Re: A "key" question
MySQL can use the index on one of the columns in a multi-column index, with caveats. If this is your index, UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) and you plan to use "price_data_date" in all your queries, but never "price_data_ticker", then simply reverse the order of the columns in your index definition: UNIQUE KEY `tidadx` (`price_data_date`, `price_data_ticker`) If you have a composite index on columns a, b and c: create index a_b_c_idx ON table_name (a, b, c); and you query with "a" in the where clause, the composite index will be used. If you query with "a" and "b" in the where clause, the composite index will be used; ditto for "a", "b" and "c". But if you query with "b" (and only "b") in the where clause, the index won't be used. If you use "b" and "c" in the where clause, the index won't be used. Look here for other examples: http://dev.mysql.com/doc/refman/4.1/en/multiple-column-indexes.html Don't add indexes you don't need - it slows down inserts (and updates where the indexed columns are being updated), uses up space in your database, and requires extra administration, etc. David Mikhail Berman wrote: Michael, Thank you for your comments. This give me a new ideas how to work with this issues. And, no at this point we are not planning to work with price_data_ticker field itself. Regards, Mikhail Berman -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 12:11 PM To: Mikhail Berman Cc: Jeremy Cole; Jasper Bryant-Greene; mysql@lists.mysql.com Subject: Re: A "key" question Mikhail Berman wrote: Dear Jeremy, Thank you for your help. I do have an exact situation you have assume I have. Here is the output of SHOW CREATE TABLE CREATE TABLE `TICKER_HISTORY_PRICE_DATA` ( `price_data_ticker` char(8) NOT NULL default '', `price_data_date` date NOT NULL default '-00-00', `price_data_open` float default NULL, `price_data_high` float default NULL, `price_data_low` float default NULL, `price_data_close` float default NULL, `price_data_volume` float default NULL, UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | > As you can see, Unique KEY is on two first fields, but most of the work, joins & searches, will be done on the second field "price_data_date". As others have pointed out, your UNIQUE KEY on (price_data_ticker,price_data_date) will serve as an index to speed queries which search for a specific value of price_data_ticker and queries which search for a specific combination of values of price_data_ticker and price_data_date, but it won't help queries which only search by price_data_date. Yet, "most of the work, joins & searches, will be done on the second field, price_data_date." In that case, you definitely need an index on price_data_date. Based on your description, I'd suggest you have your index backwards. What you need is an index on (price_data_date, price_data_ticker). This will satisfy searches on price_data_date and on combinations of the two. Hence, ALTER TABLE TICKER_HISTORY_PRICE_DATA DROP INDEX tidadx, ADD PRIMARY KEY (price_data_date, price_data_ticker); That will satisfy most of your queries. Then, the question becomes, do you need a separate, single-column index on price_data_ticker? That will depend on whether you run queries which select based on price_data_ticker without specifying price_data_date. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A "key" question
Michael, Thank you for your comments. This give me a new ideas how to work with this issues. And, no at this point we are not planning to work with price_data_ticker field itself. Regards, Mikhail Berman -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 12:11 PM To: Mikhail Berman Cc: Jeremy Cole; Jasper Bryant-Greene; mysql@lists.mysql.com Subject: Re: A "key" question Mikhail Berman wrote: > Dear Jeremy, > > Thank you for your help. > > I do have an exact situation you have assume I have. Here is the > output of SHOW CREATE TABLE > CREATE TABLE `TICKER_HISTORY_PRICE_DATA` ( > `price_data_ticker` char(8) NOT NULL default '', > `price_data_date` date NOT NULL default '-00-00', > `price_data_open` float default NULL, > `price_data_high` float default NULL, > `price_data_low` float default NULL, > `price_data_close` float default NULL, > `price_data_volume` float default NULL, > UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | > > As you can see, Unique KEY is on two first fields, but most of the > work, joins & searches, will be done on the second field "price_data_date". As others have pointed out, your UNIQUE KEY on (price_data_ticker,price_data_date) will serve as an index to speed queries which search for a specific value of price_data_ticker and queries which search for a specific combination of values of price_data_ticker and price_data_date, but it won't help queries which only search by price_data_date. Yet, "most of the work, joins & searches, will be done on the second field, price_data_date." In that case, you definitely need an index on price_data_date. Based on your description, I'd suggest you have your index backwards. What you need is an index on (price_data_date, price_data_ticker). This will satisfy searches on price_data_date and on combinations of the two. Hence, ALTER TABLE TICKER_HISTORY_PRICE_DATA DROP INDEX tidadx, ADD PRIMARY KEY (price_data_date, price_data_ticker); That will satisfy most of your queries. Then, the question becomes, do you need a separate, single-column index on price_data_ticker? That will depend on whether you run queries which select based on price_data_ticker without specifying price_data_date. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A "key" question
Mikhail Berman wrote: Hi Jeremy, This is still "work in progress" but here are some samples of queries we will be running, that involved this table and this date field: == #this fails -- join on is horrible What do you mean by "fails"? Takes too long? Wrong results? Crashes client/server? In general, using ON for your JOIN conditions is better than putting them in the WHERE clause. It's never worse for equivalent queries. update COMPANY_NUMBERS left join TICKER_HISTORY_PRICE_DATA on ticker = price_data_ticker and date_sub(date_qtr, interval 0 day) = price_data_date Huh? Why not "date_qtr = price_data_date"? DATE_SUB is doing nothing useful here, but is taking time to execute on every row. Also, if there's an index on date_qtr, you just prevented its use by running date_qtr through a function. and !isnull(price_data_ticker) and isnull(price_date) set price_date = price_data_date, price_open = price_data_open, price_close = price_data_close, price_high = price_data_high, price_low = price_data_low, price_date_volume = price_data_volume; #this succeeds -- putting the on clause in the where is fine -- using join on is horrible But this is a different query! This is a JOIN, not a LEFT JOIN, so you cannot directly compare them. The difference between them is the type of JOIN, not the location of the join conditions. update COMPANY_NUMBERS, TICKER_HISTORY_PRICE_DATA set price_date = price_data_date, price_open = price_data_open, price_close = price_data_close, price_high = price_data_high, price_low = price_data_low, price_date_volume = price_data_volume where isnull(price_date) and ticker = price_data_ticker and date_sub(date_qtr, interval 0 day) = price_data_date and !isnull(price_data_ticker); In the first query, you give *all* your restrictions as join conditions, but in the second, they are all in the WHERE clause, so mysql will choose which to use as join conditions. These are not equivalent queries, so I expect they give different results. If you would describe exactly what you want this to do, I'm sure someone could help you get the right query. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A "key" question
Mikhail Berman wrote: Dear Jeremy, Thank you for your help. I do have an exact situation you have assume I have. Here is the output of SHOW CREATE TABLE CREATE TABLE `TICKER_HISTORY_PRICE_DATA` ( `price_data_ticker` char(8) NOT NULL default '', `price_data_date` date NOT NULL default '-00-00', `price_data_open` float default NULL, `price_data_high` float default NULL, `price_data_low` float default NULL, `price_data_close` float default NULL, `price_data_volume` float default NULL, UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | > As you can see, Unique KEY is on two first fields, but most of the work, joins & searches, will be done on the second field "price_data_date". As others have pointed out, your UNIQUE KEY on (price_data_ticker,price_data_date) will serve as an index to speed queries which search for a specific value of price_data_ticker and queries which search for a specific combination of values of price_data_ticker and price_data_date, but it won't help queries which only search by price_data_date. Yet, "most of the work, joins & searches, will be done on the second field, price_data_date." In that case, you definitely need an index on price_data_date. Based on your description, I'd suggest you have your index backwards. What you need is an index on (price_data_date, price_data_ticker). This will satisfy searches on price_data_date and on combinations of the two. Hence, ALTER TABLE TICKER_HISTORY_PRICE_DATA DROP INDEX tidadx, ADD PRIMARY KEY (price_data_date, price_data_ticker); That will satisfy most of your queries. Then, the question becomes, do you need a separate, single-column index on price_data_ticker? That will depend on whether you run queries which select based on price_data_ticker without specifying price_data_date. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A "key" question
| | NULL | | | cshflst_net_cash_from_operating_activities_qtr| bigint(20) | YES | | NULL | | | cshflst_net_cash_from_operating_activities_ttm| bigint(20) | YES | | NULL | | | cshflst_net_cash_from_investing_activities_qtr| bigint(20) | YES | | NULL | | | cshflst_net_cash_from_investing_activities_ttm| bigint(20) | YES | | NULL | | | cshflst_net_cash_from_financing_activities_qtr| bigint(20) | YES | | NULL | | | cshflst_net_cash_from_financing_activities_ttm| bigint(20) | YES | | NULL | | | cshflst_net_cash_flow_change_in_cash_and_cash_equivalents_qtr | bigint(20) | YES | | NULL | | | cshflst_net_cash_flow_change_in_cash_and_cash_equivalents_ttm | bigint(20) | YES | | NULL | | +---+--- -+--+-++---+ 39 rows in set (0.00 sec) mysql> select count(*) from COMPANY_NUMBERS; +--+ | count(*) | +--+ | 175102 | +--+ 1 row in set (0.01 sec) mysql> describe TICKER_HISTORY_PRICE_DATA; +---+-+--+-++---+ | Field | Type| Null | Key | Default| Extra | +---+-+--+-++---+ | price_data_ticker | char(8) | | PRI || | | price_data_date | date| | PRI | -00-00 | | | price_data_open | float | YES | | NULL | | | price_data_high | float | YES | | NULL | | | price_data_low| float | YES | | NULL | | | price_data_close | float | YES | | NULL | | | price_data_volume | float | YES | | NULL | | +---+-+--+-++---+ 7 rows in set (0.00 sec) mysql> And thank you again, Mikhail Berman -Original Message- From: Jeremy Cole [mailto:[EMAIL PROTECTED] Sent: Friday, November 18, 2005 11:01 AM To: Mikhail Berman Cc: Jasper Bryant-Greene; mysql@lists.mysql.com Subject: Re: A "key" question Hi Mikhail, > Thank you for your help. > > I do have an exact situation you have assume I have. Here is the > output of SHOW CREATE TABLE > > `price_data_ticker` char(8) NOT NULL default '', > `price_data_date` date NOT NULL default '-00-00', ... > UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) ... > As you can see, Unique KEY is on two first fields, but most of the > work, joins & searches, will be done on the second field "price_data_date". Could you provide some example queries? Likely the solution is to create another index on price_data_date, that could be used for searches by date that do not include ticker. As I mentioned before, an index on (a, b) can be used for (a) but not for (b) alone. However, it usually doesn't make sense to create an index on (b, a) as well, since if you have both columns in your query, usually the index on (a, b) would be fine. So I would suggest adding an index: ALTER TABLE `TICKER_HISTORY_PRICE_DATA` ADD INDEX (price_data_date); Keep in mind that will lock the table to add the index, and may take a few minutes (although I would expect less than two minutes for 32M rows) so it might not be a good idea to run while the market is open. :) If you could provide the exact query you were running, I could confirm that it would or would not help. :) Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A "key" question
Hi Mikhail, Thank you for your help. I do have an exact situation you have assume I have. Here is the output of SHOW CREATE TABLE `price_data_ticker` char(8) NOT NULL default '', `price_data_date` date NOT NULL default '-00-00', ... UNIQUE KEY `tidadx` (`price_data_ticker`,`price_data_date`) ... As you can see, Unique KEY is on two first fields, but most of the work, joins & searches, will be done on the second field "price_data_date". Could you provide some example queries? Likely the solution is to create another index on price_data_date, that could be used for searches by date that do not include ticker. As I mentioned before, an index on (a, b) can be used for (a) but not for (b) alone. However, it usually doesn't make sense to create an index on (b, a) as well, since if you have both columns in your query, usually the index on (a, b) would be fine. So I would suggest adding an index: ALTER TABLE `TICKER_HISTORY_PRICE_DATA` ADD INDEX (price_data_date); Keep in mind that will lock the table to add the index, and may take a few minutes (although I would expect less than two minutes for 32M rows) so it might not be a good idea to run while the market is open. :) If you could provide the exact query you were running, I could confirm that it would or would not help. :) Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A "key" question
| 0 | | sync_replication_timeout| 0 | | sync_frm| ON | | system_time_zone| EST | | table_cache | 512 | | table_type | MyISAM | | thread_cache_size | 8 | | thread_concurrency | 8 | | thread_stack| 196608 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | tmp_table_size | 33554432 | | tmpdir | | | transaction_alloc_block_size| 8192 | | transaction_prealloc_size | 4096 | | tx_isolation| REPEATABLE-READ | | version | 4.1.10a-standard-log | | version_comment | MySQL Community Edition - Standard (GPL) | | version_compile_machine | sparc | | version_compile_os | sun-solaris2.9 | | wait_timeout| 28800 | +-+- -+ 180 rows in set (0.00 sec) Mikhail Berman -Original Message- From: Jeremy Cole [mailto:[EMAIL PROTECTED] Sent: Thursday, November 17, 2005 5:23 PM To: Mikhail Berman Cc: Jasper Bryant-Greene; mysql@lists.mysql.com Subject: Re: A "key" question Hi Mikhail, > I may not have been precise in my question, but the Unique Index in > question is a two fields index, and I was looking to find out wisdom > from the List if there is sense and/or experience in keying > second(left) field on in the Unique Index to speed up a search. If you have a UNIQUE(a, b), then MySQL can use it as an index for (a), or (a, b), but NOT for (b). In this context, it won't help generally to create an index on (a), but it may help to create one on (b) depending on your queries. > I am dealing with 32M rows table, where second field in the Unique > Index is a date field. Unfortunately for my simple "SELECT MAX(Date) > as Latest_Date from THE_TABLE" took 4 minutes and some seconds, so > before I will go and buy bigger server I needed to re-assure myself > that there is no other way. Four minutes to find a MAX(date) is too long for any kind of hardware. It should be much faster. Can you post the output of: * SHOW CREATE TABLE tbl * SHOW VARIABLES FYI: mysql> select max(dep_time) from ontime_all; +-+ | max(dep_time) | +-+ | 2005-05-31 23:59:00 | +-+ 1 row in set (49.76 sec) mysql> select count(*) from ontime_all; +--+ | count(*) | +--+ | 33395077 | +--+ 1 row in set (0.00 sec) Could be a lot faster, even, but these are MERGE tables so it's really 65 tables that are being checked... Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A "key" question
Hi Mikhail, I may not have been precise in my question, but the Unique Index in question is a two fields index, and I was looking to find out wisdom from the List if there is sense and/or experience in keying second(left) field on in the Unique Index to speed up a search. If you have a UNIQUE(a, b), then MySQL can use it as an index for (a), or (a, b), but NOT for (b). In this context, it won't help generally to create an index on (a), but it may help to create one on (b) depending on your queries. I am dealing with 32M rows table, where second field in the Unique Index is a date field. Unfortunately for my simple "SELECT MAX(Date) as Latest_Date from THE_TABLE" took 4 minutes and some seconds, so before I will go and buy bigger server I needed to re-assure myself that there is no other way. Four minutes to find a MAX(date) is too long for any kind of hardware. It should be much faster. Can you post the output of: * SHOW CREATE TABLE tbl * SHOW VARIABLES FYI: mysql> select max(dep_time) from ontime_all; +-+ | max(dep_time) | +-+ | 2005-05-31 23:59:00 | +-+ 1 row in set (49.76 sec) mysql> select count(*) from ontime_all; +--+ | count(*) | +--+ | 33395077 | +--+ 1 row in set (0.00 sec) Could be a lot faster, even, but these are MERGE tables so it's really 65 tables that are being checked... Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A "key" question
Mikhail, >Is it possible or makes sense to key a field that is a part of Unique >Index already? MySQL won't stop you, but it's a waste of space & cpu cycles unless there's a high-priority query performance need for it. PB - Mikhail Berman wrote: Hello everyone, Is it possible or makes sense to key a field that is a part of Unique Index already? Regards and thank you Mikhail Berman No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/2005 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A "key" question
Dear Jasper, I may not have been precise in my question, but the Unique Index in question is a two fields index, and I was looking to find out wisdom from the List if there is sense and/or experience in keying second(left) field on in the Unique Index to speed up a search. I am dealing with 32M rows table, where second field in the Unique Index is a date field. Unfortunately for my simple "SELECT MAX(Date) as Latest_Date from THE_TABLE" took 4 minutes and some seconds, so before I will go and buy bigger server I needed to re-assure myself that there is no other way. I would not bother the List without a good reason and doing what you said before Regards, Mikhail Berman -Original Message- From: Jasper Bryant-Greene [mailto:[EMAIL PROTECTED] Sent: Thursday, November 17, 2005 4:19 PM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: A "key" question Mikhail Berman wrote: > Is it possible or makes sense to key a field that is a part of Unique > Index already? It's possible, but it doesn't make sense. A unique index is a normal index with an added unique constraint. Adding another index on the same field would make no sense (unless the field is a rightmost part of a unique index). Your question would have been answered by checking the archives less than a week back (this question comes up a lot) or reading the manual. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A "key" question
Mikhail Berman wrote: Is it possible or makes sense to key a field that is a part of Unique Index already? It's possible, but it doesn't make sense. A unique index is a normal index with an added unique constraint. Adding another index on the same field would make no sense (unless the field is a rightmost part of a unique index). Your question would have been answered by checking the archives less than a week back (this question comes up a lot) or reading the manual. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
