RE: Not using indexes???
E.g. the non-equivalence operator is the same. MySQL will use indexes for foo0, but not foo0, which ask for the same result (presumed foo is an unsigned column). Perhaps I was a bit unclear... Using foo 0 does *NOT* use an index. Using foo 0 AND foo somevalue *DOES* use an index. As Erv did not know why this could help: It uses a different operator than IS NOT NULL, namely greather-than. One, that MySQL supports to make use of indexes. Again, merely using greater than by itself produces results identical to using IS NOT NULL. -JF - 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: Not using indexes???
At 03:11 PM 6/8/2002 +0200, Benjamin Pflugmann wrote: Hi. As far as I can see is that you use a condition in your WHERE clause which MySQL will not (yet?) use indexes for. See http://www.mysql.com/doc/M/y/MySQL_indexes.html (seems to be mainly about MyISAM tables) to see how indexes are used. You use IS NOT NULL. This page states nowhere that this will be able to make use of an index (only IS NULL does). E.g. the non-equivalence operator is the same. MySQL will use indexes for foo0, but not foo0, which ask for the same result (presumed foo is an unsigned column). Yes, but the manual also does not say that it does _not_ use an index for negated operators. It does not mention the inequality comparison operator . It does not specifically mention IS NOT NULL. ... But type=range does mean that MySQL is using an index. Just in case that this is not clear: using index in the Extra does not indicate whether an index can be used (a non-NULL value in key does), but that the index alone is sufficent and the data file does not has to be touched. If we take this observation as impetus to actually take the SELECT statements and the EXPLAIN output from Jon's original message, and piece the broken lines back together (supplying elided spaces as needed), and then do the necessary RTFM at http://www.mysql.com/doc/E/X/EXPLAIN.html it would appear that Jon is mistaken. Three of his four queries actually do use the sequence_log_id index, according to the output he presented. Only the second query does not use the index. This knowledge is hardly reassuring; it doesn't make the queries run any faster. In fact, the EXPLAIN output suggests that the only purpose for which the index has been used is to determine the order in which MySQL visits each one of the ~1M rows in the table, and not to limit its attention to just those few rows of interest. To rephrase Jon's original question, what can be done to cause MySQL to process these queries quickly? I don't think we have answered this yet. --Erv - 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: Not using indexes???
Hi. As far as I can see is that you use a condition in your WHERE clause which MySQL will not (yet?) use indexes for. See http://www.mysql.com/doc/M/y/MySQL_indexes.html (seems to be mainly about MyISAM tables) to see how indexes are used. You use IS NOT NULL. This page states nowhere that this will be able to make use of an index (only IS NULL does). E.g. the non-equivalence operator is the same. MySQL will use indexes for foo0, but not foo0, which ask for the same result (presumed foo is an unsigned column). On Fri 2002-06-07 at 19:29:44 -0700, [EMAIL PROTECTED] wrote: [...] Using sequence_log_id 0 produces the same result. Using any particular value in place of 0 produces the same effect. Using sequence_log_id foo AND sequence_log_id bar does seem to use the index... As Erv did not know why this could help: It uses a different operator than IS NOT NULL, namely greather-than. One, that MySQL supports to make use of indexes. explain SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id 10 and sequence_log_id 40; +---+---+-+-+-+--+-- ++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+-+-+-+--+-- ++ | click | range | sequence_log_id | sequence_log_id | 5 | NULL | 10 | where used | +---+---+-+-+-+--+-- ++ But type=range does mean that MySQL is using an index. Just in case that this is not clear: using index in the Extra does not indicate whether an index can be used (a non-NULL value in key does), but that the index alone is sufficent and the data file does not has to be touched. Bye, Benjamin. -- [EMAIL PROTECTED] - 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: Not using indexes???
Jon, Have you tried phrasing your WHERE clause as WHERE sequence_log_id = 0 ? If there is a determinate lower bound other than zero, then substitute it for 0 as the second comparand. Let us know how it comes out. (And however it comes out, don't ask me why. The real experts can sort that out for both of us when they get to their desks, in about 4 hours' time.) --Erv At 06:07 PM 6/7/2002 -0700, Jon Frisby wrote: Please excuse me if this is something blindingly obvious, but having now encountered this in several circumstances, but I have been unable to find a resolution in the docs (perhaps I just missed it?). In actuality we encountered the problem when doing some nasty joins, but the problem seems really to be more fundamental as we've reproduced the problem without a join... We're using MySQL 4.0.1 on Linux (from the RPM). We have an InnoDB table, 'click' with 6 or so indexes on it. Notably: -`id` is the PRIMARY KEY. -`sequence_log_id` has a non-unique index. The problem is when we do queries that involve columns *not* available directly from the index, MySQL absolutely refuses to use an index for looking up rows. See the output from EXPLAIN for each of these queries: SELECT COUNT(*) FROM click WHERE sequence_log_id IS NOT NULL; +---+---+-+-+-+--+-- --+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+-+-+-+--+-- --+-+ | click | index | sequence_log_id | sequence_log_id | 5 | NULL | 939470 | where used; Using index | +---+---+-+-+-+--+-- --+-+ SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT NULL; +---+--+-+--+-+--++- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+-+--+-+--++- ---+ | click | ALL | sequence_log_id | NULL |NULL | NULL | 939470 | where used | +---+--+-+--+-+--++- ---+ (`actual_revenue` isn't part of the index. SELECT COUNT(*), SUM(id) FROM click WHERE sequence_log_id IS NOT NULL; +---+---+-+-+-+--+-- --+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+-+-+-+--+-- --+-+ | click | index | sequence_log_id | sequence_log_id | 5 | NULL | 939470 | where used; Using index | +---+---+-+-+-+--+-- --+-+ (Since it's an InnoDB table, a secondary index uses the PK as a reference for the index.) SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT NULL GROUP BY sequence_log_id; +---+---+-+-+-+--+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+-+-+-+--+-- --++ | click | index | sequence_log_id | sequence_log_id | 5 | NULL | 939469 | where used | +---+---+-+-+-+--+-- --++ (The key isn't actually *used* here, as demonstrated by the query taking a long time to run... Of the ~970k rows in click, about 2800 have sequence_log_id IS NOT NULL.) My question is: Is this a bug? A feature? What can I do to get MySQL to use the sequence_log_id index? -JF - 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
Re: Not using indexes???
* Mysql, after reading the query, decides wether using an index would be better than just a table row scan. Hence, it's MySql's decision * You can force MySql to use indexes using the 'using index' option with the select query.(MySql 'might' still reject the force, not sure of the circumstances). * The first 2 queries pretty much won't use index because it is not a specific value that you are asking MySql to search for but just a NOT NULL, very different for '=' or even ranges. * The final query is a Group By and hence it will need to use the index to group by the Values that it is going to show you. * The third query is a bit confusing. I don't think it should be using indexes, just as the second one but it is due to some reason, can't answer that one but instead I would also suggest that you try this: explain Select sum(actual_revenue) From Click where sequence_log_id = specifysomevalue; That should use indexes since you are searching for something specific. You can also try ranges and it should still use indexes ( a rule of thumb is that if the return result is more than 30% of the total number of records, it's faster to do just a full table scan so it still might not use it if you specify a very big range) Regards, Bhavin Vyas. - Original Message - From: Jon Frisby [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 07, 2002 6:07 PM Subject: Not using indexes??? Please excuse me if this is something blindingly obvious, but having now encountered this in several circumstances, but I have been unable to find a resolution in the docs (perhaps I just missed it?). In actuality we encountered the problem when doing some nasty joins, but the problem seems really to be more fundamental as we've reproduced the problem without a join... We're using MySQL 4.0.1 on Linux (from the RPM). We have an InnoDB table, 'click' with 6 or so indexes on it. Notably: -`id` is the PRIMARY KEY. -`sequence_log_id` has a non-unique index. The problem is when we do queries that involve columns *not* available directly from the index, MySQL absolutely refuses to use an index for looking up rows. See the output from EXPLAIN for each of these queries: SELECT COUNT(*) FROM click WHERE sequence_log_id IS NOT NULL; +---+---+-+-+-+--+-- --+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+-+-+-+--+-- --+-+ | click | index | sequence_log_id | sequence_log_id | 5 | NULL | 939470 | where used; Using index | +---+---+-+-+-+--+-- --+-+ SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT NULL; +---+--+-+--+-+--++- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+-+--+-+--++- ---+ | click | ALL | sequence_log_id | NULL |NULL | NULL | 939470 | where used | +---+--+-+--+-+--++- ---+ (`actual_revenue` isn't part of the index. SELECT COUNT(*), SUM(id) FROM click WHERE sequence_log_id IS NOT NULL; +---+---+-+-+-+--+-- --+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+-+-+-+--+-- --+-+ | click | index | sequence_log_id | sequence_log_id | 5 | NULL | 939470 | where used; Using index | +---+---+-+-+-+--+-- --+-+ (Since it's an InnoDB table, a secondary index uses the PK as a reference for the index.) SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT NULL GROUP BY sequence_log_id; +---+---+-+-+-+--+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+-+-+-+--+-- --++ | click | index | sequence_log_id | sequence_log_id | 5 | NULL | 939469 | where used | +---+---+-+-+-+--+-- --++ (The key isn't actually *used* here, as demonstrated by the query taking a long time to run... Of the ~970k rows in click, about 2800 have sequence_log_id IS NOT NULL.) My question is: Is this a bug? A feature? What can I do to get MySQL to use the sequence_log_id index? -JF
RE: Not using indexes???
* Mysql, after reading the query, decides wether using an index would be better than just a table row scan. Hence, it's MySql's decision MySQL is making the wrong decision. As stated below, it's doing a table scan when it only needs to look at some 2,800 rows out of 970,000 rows. In addition, the real query I've been trying to make work (a join) gets nonsense output from EXPLAIN -- some 50k rows from the left table are involved in the join, yet MySQL estimates that 1 row will be! (The query produces correct results, it just takes a very very long time to do so .) * You can force MySql to use indexes using the 'using index' option with the select query.(MySql 'might' still reject the force, not sure of the circumstances). I have never managed to get MySQL to obey the using index option. It produces no effect here. * The first 2 queries pretty much won't use index because it is not a specific value that you are asking MySql to search for but just a NOT NULL, very different for '=' or even ranges. Using sequence_log_id 0 produces the same result. Using any particular value in place of 0 produces the same effect. Using sequence_log_id foo AND sequence_log_id bar does seem to use the index... explain SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id 10 and sequence_log_id 40; +---+---+-+-+-+--+-- ++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+-+-+-+--+-- ++ | click | range | sequence_log_id | sequence_log_id | 5 | NULL | 10 | where used | +---+---+-+-+-+--+-- ++ * The final query is a Group By and hence it will need to use the index to group by the Values that it is going to show you. However it seems to be doing a table scan across the actual *data*. It does not seem to be using the index to eliminate rows. That should use indexes since you are searching for something specific. You can also try ranges and it should still use indexes ( a rule of thumb is that if the return result is more than 30% of the total number of records, it's faster to do just a full table scan so it still might not use it if you specify a very big range) The return result is about 0.3% -- that is a far cry from 30%. Using ranges may work (I'll just use 0 and maxint), but this is definitely not desirable behavior from the MySQL optimizer if I have to resort to such a trick. Using *a* specific value is somewhat inapplicable here because my ultimate goal is to use this table in a *join*. It's not a good thing when the left table *must* do a table scan across 50k rows, and MySQL acts brain dead when scanning the right table and looks at all 970k rows even though it should be getting 1..10 rows out of click for each row from the left table... -JF - 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
