Bug #29285 | --log-slow-queries + --log-queries-not-using-indexes works incorrect | ||
---|---|---|---|
Submitted: | 21 Jun 2007 20:32 | Modified: | 25 Aug 2008 10:01 |
Reporter: | Valeriy Kravchuk | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.0.36, 5.0.37, 5.0.60 | OS: | Any ((Linux, Windows)) |
Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
[21 Jun 2007 20:32]
Valeriy Kravchuk
[7 Apr 2008 7:01]
Tatiana Azundris Nuernberg
I see this behaviour as consistent across all servers (5.0.3x, 5.0-bk, 5.1-bk). It is --log-queries-not-using-indexes that produces these log-entries; while the query produces no result set and is not, in fact, slow, SERVER_QUERY_NO_INDEX_USED is set in sql_select.cc:make_join_readinfo() (around 6221) as we have JT_ALL, which is pretty bad. By that token, the query should show up as optimizable (not in general as "slow" because the execution wasn't, but as "generally optimizable," (we need to traverse all rows, in index or in data ...) which was requested with --l/q/n/u/i). I checked with consulting and got "If that weren't how we're already doing it, it's how we should be doing it. We need that in the field."
[22 Aug 2008 0:37]
Trent Lloyd
Here is another example query that appears to trigger this: SELECT * FROM table ORDER BY indexed_field LIMIT 1; I believe this should be relatively well optimized - however it shows up also. Any further consensus on this issue? Certainly it goes against the specific definition, as EXPLAIN show it USING INDEX much like Valeriy's case.
[22 Aug 2008 3:50]
Valeriy Kravchuk
Looks like this is NOT fixed (and there is NO QA test, as I demanded). Newer versions are affected.
[25 Aug 2008 10:01]
Sergei Golubchik
As Tatjana has explained, --log-queries-not-using-indexes logs all queries that don't use an index to limit the number of rows, in other words it logs all queries that are expected to retrieve all rows from the table. "Using index" in the EXPLAIN does not count, it does not necessarily mean that index is used to limit the number of rows, it could also mean full index scan.