Re: Index only sometimes used
At 16:31 + 3/20/02, Steve Gardner wrote: Hi All Could someone explain to me why an index in a select I am doing is only sometimes used. The select I do is a complex one with multiple tables, and when it uses the index it takes 1.5 seconds, but when it dosnt it takes 1 min and 25-50 seconds. I have simplified it down and founed the place the problem issee explains bellow: For all the cases where the index is not being used, the rows value is pretty high. The optimizer has probably decided that the number of rows relative to the total number of rows in the table is such that it'll be just as fast to do a table scan as it would be to use the index. mysql explain select * from mailstat where domain_id in(1,3,4,7,50,20,11); +--+---+---+--+-+--++--- -+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+--+-+--++--- -+ | mailstat | range | ind4 | ind4 | 4 | NULL | 239862 | where used | +--+---+---+--+-+--++--- -+ 1 row in set (0.00 sec) *notice ind4 is used mysql explain select * from mailstat where domain_id in(1,2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955648 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used mysql explain select * from mailstat where domain_id in(1); +--+---+---+--+-+--++--- -+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+--+-+--++--- -+ | mailstat | range | ind4 | ind4 | 4 | NULL | 174922 | where used | +--+---+---+--+-+--++--- -+ 1 row in set (0.00 sec) *notice ind4 is used mysql explain select * from mailstat where domain_id in(2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955666 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used Basicaly, when I use '2' in the in() statement (and one or two other values from hundreds that do work), the index is not used. Initialy I thought that maybe the index was corupted, so I built a copy of the table, and inserted a handfull of rows, but the I got the same results, except for my new table 1 and 3 didnt work, but 2 did!. Out of interest I tried other indexes and found results like: mysql explain select * from mailstat where id558693; +--+---+---+-+-+--++ + | table| type | possible_keys | key | key_len | ref | rows | Extra| +--+---+---+-+-+--++ + | mailstat | range | PRIMARY | PRIMARY | 4 | NULL | 511375 | where used | +--+---+---+-+-+--++ + 1 row in set (0.00 sec) *primary key is used mysql explain select * from mailstat where id558694; +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | PRIMARY | NULL |NULL | NULL | 2956363 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *primary key is NOT used If anyone could shed some light on this I would b most greatful :) Cheers, Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Index only sometimes used
On Wed, 20 Mar 2002, Steve Gardner wrote: Hi All Could someone explain to me why an index in a select I am doing is only sometimes used. mysql explain select * from mailstat where domain_id in(2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955666 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used snip Basicaly, when I use '2' in the in() statement (and one or two other values from hundreds that do work), the index is not used. snip If anyone could shed some light on this I would b most greatful :) Cheers, Steve Check the manual section on the query optimiser,.I seem to remember that if MySQL has to check more that some percentage of a table (30% ?) the optimiser guesses it can search the entire table just as quickly. Someone please correct my if I'm wrong. Try 'select domain_id, count(*) from mailstat group by domain_id' to check the distribution of your data. Hope this helps, Nigel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Index only sometimes used
If more than a third of the rows would be selected, it is faster not to use the index. Over 2 million records match the '2'. How many are there total? Steve Gardner wrote: Hi All Could someone explain to me why an index in a select I am doing is only sometimes used. The select I do is a complex one with multiple tables, and when it uses the index it takes 1.5 seconds, but when it dosnt it takes 1 min and 25-50 seconds. I have simplified it down and founed the place the problem issee explains bellow: mysql explain select * from mailstat where domain_id in(1,3,4,7,50,20,11); +--+---+---+--+-+--++--- -+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+--+-+--++--- -+ | mailstat | range | ind4 | ind4 | 4 | NULL | 239862 | where used | +--+---+---+--+-+--++--- -+ 1 row in set (0.00 sec) *notice ind4 is used mysql explain select * from mailstat where domain_id in(1,2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955648 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used mysql explain select * from mailstat where domain_id in(1); +--+---+---+--+-+--++--- -+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+--+-+--++--- -+ | mailstat | range | ind4 | ind4 | 4 | NULL | 174922 | where used | +--+---+---+--+-+--++--- -+ 1 row in set (0.00 sec) *notice ind4 is used mysql explain select * from mailstat where domain_id in(2); +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | ind4 | NULL |NULL | NULL | 2955666 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *notice ind4 is NOT used Basicaly, when I use '2' in the in() statement (and one or two other values from hundreds that do work), the index is not used. Initialy I thought that maybe the index was corupted, so I built a copy of the table, and inserted a handfull of rows, but the I got the same results, except for my new table 1 and 3 didnt work, but 2 did!. Out of interest I tried other indexes and found results like: mysql explain select * from mailstat where id558693; +--+---+---+-+-+--++ + | table| type | possible_keys | key | key_len | ref | rows | Extra| +--+---+---+-+-+--++ + | mailstat | range | PRIMARY | PRIMARY | 4 | NULL | 511375 | where used | +--+---+---+-+-+--++ + 1 row in set (0.00 sec) *primary key is used mysql explain select * from mailstat where id558694; +--+--+---+--+-+--+-+--- -+ | table| type | possible_keys | key | key_len | ref | rows| Extra | +--+--+---+--+-+--+-+--- -+ | mailstat | ALL | PRIMARY | NULL |NULL | NULL | 2956363 | where used | +--+--+---+--+-+--+-+--- -+ 1 row in set (0.00 sec) *primary key is NOT used If anyone could shed some light on this I would b most greatful :) Cheers, Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
