# log-queries-not-using-indexes logs results using index **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [January 3, 2010, 4:16am UTC](https://forums.percona.com/t/log-queries-not-using-indexes-logs-results-using-index/1321 "2010-01-03T04:16:33Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![ep90](https://avatars.discourse-cdn.com/v4/letter/e/e274bd/32.png) [@ep90](https://forums.percona.com/u/ep90)\ **Post date:** [January 3, 2010, 4:16am UTC](https://forums.percona.com/t/log-queries-not-using-indexes-logs-results-using-index/1321/1 "2010-01-03T04:16:33Z")
I’m having a weird problem where one of my queries that’s using an index gets logged when the “log-queries-not-using-indexes” option is used in my.ini. The query: mysql\> explain select data.img\_url from data,popular where data.model\_no= popular.model\_no order by popular.popularity desc,popular.y\_popularity desc limit 80;±—±------------±--------±------±--------------±----------±--------±-------------------------------±------±------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±--------±------±--------------±----------±--------±-------------------------------±------±------------+| 1 | SIMPLE | popular | index | PRIMARY,icode | iboth | 10 | NULL | 12139 | Using index || 1 | SIMPLE | data | ref | imodel\_no | imodel\_no | 130 | odata.popular.model\_no | 1 | |±—±------------±--------±------±--------------±----------±--------±-------------------------------±------±------------+2 rows in set In the log: # Query\_time: 0 Lock\_time: 0 Rows\_sent: 80 Rows\_examined: 160select data.img\_url from data,popular where data.model\_no= popular.model\_no order by popular.popularity desc,popular.y\_popularity desc limit 80; Also, what’s weird is under EXPLAIN is states PRIMARY,icode as possible keys, but uses iboth (iboth is a normal index on popularity,y\_popularity) I am using MYSQL 5.0.45 Any ideas what’s going on here? ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [January 3, 2010, 10:48am UTC](https://forums.percona.com/t/log-queries-not-using-indexes-logs-results-using-index/1321/2 "2010-01-03T10:48:01Z")
| [B]Quote:[/B] | | (iboth is a normal index on popularity,y\_popularity) | that's impossible, it must contain popular.model\_no also for this explain output to appear. And that's where the order is important: popularity,y\_popularity should be a prefix for this query. ---
**Author:** ![ep90](https://avatars.discourse-cdn.com/v4/letter/e/e274bd/32.png) [@ep90](https://forums.percona.com/u/ep90)\ **Post date:** [January 3, 2010, 12:03pm UTC](https://forums.percona.com/t/log-queries-not-using-indexes-logs-results-using-index/1321/3 "2010-01-03T12:03:59Z")
These are the indexes on popular: mysql\> show indexes from popular;±--------±-----------±---------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------+| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |±--------±-----------±---------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------+| popular | 0 | PRIMARY | 1 | model\_no | A | 14583 | NULL | NULL | | BTREE | || popular | 1 | icode | 1 | model\_no | A | 14583 | NULL | NULL | | BTREE | || popular | 1 | ipop | 1 | popularity | A | 1 | NULL | NULL | YES | BTREE | || popular | 1 | iypop | 1 | y\_popularity | A | 441 | NULL | NULL | YES | BTREE | || popular | 1 | iboth | 1 | popularity | A | 123 | NULL | NULL | YES | BTREE | || popular | 1 | iboth | 2 | y\_popularity | A | 1041 | NULL | NULL | YES | BTREE | |±--------±-----------±---------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------+6 rows in set ---
**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\ **Post date:** [January 3, 2010, 12:27pm UTC](https://forums.percona.com/t/log-queries-not-using-indexes-logs-results-using-index/1321/4 "2010-01-03T12:27:01Z")
When you enable log-queries-not-using-indexes, MySQL logs queries that do not use an index to restrict the result set, which is the case here. The “Using Index” in your explain results just means that the table in question (popularity) is not being read at all, and instead everything that is needed from it (model\_no, popularity, y\_popularity) is in in the index and may be retrieved with an index scan. For this explain to make sense given your indexes, you must be using InnoDB, since all secondary indexes must also contain the primary key. That’s why the iboth index can be used to satisfy the popularity table’s role in this query. ---
**Author:** ![ep90](https://avatars.discourse-cdn.com/v4/letter/e/e274bd/32.png) [@ep90](https://forums.percona.com/u/ep90)\ **Post date:** [January 3, 2010, 4:10pm UTC](https://forums.percona.com/t/log-queries-not-using-indexes-logs-results-using-index/1321/5 "2010-01-03T16:10:42Z")
Thanks for your replies vgatto - I am guessing that this is the fastest solution as popular is not read at all? (even though an index is not being used to refine the results?) gmouse - thanks for highlighting the redundant indexes, I’ve removed these now ---
**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\ **Post date:** [January 3, 2010, 6:06pm UTC](https://forums.percona.com/t/log-queries-not-using-indexes-logs-results-using-index/1321/6 "2010-01-03T18:06:23Z")
I have these additional comments: This option actually logs queries that don’t use any index, and queries that don’t use a “good” index. Also, it’s quite possible for MySQL to use a different query plan at the time of execution than the one you see later with EXPLAIN. Just some things to keep in mind. ---
**Author:** ![ep90](https://avatars.discourse-cdn.com/v4/letter/e/e274bd/32.png) [@ep90](https://forums.percona.com/u/ep90)\ **Post date:** [January 4, 2010, 3:05am UTC](https://forums.percona.com/t/log-queries-not-using-indexes-logs-results-using-index/1321/7 "2010-01-04T03:05:02Z")
Are you saying the query is inefficient, that’s why it’s coming up in the log? ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [January 4, 2010, 6:42am UTC](https://forums.percona.com/t/log-queries-not-using-indexes-logs-results-using-index/1321/8 "2010-01-04T06:42:05Z")
What he is saying, is that the EXPLAIN output might not show the actual execution plan. The only way you can be sure, is by looking at the “Rows\_sent: 80 Rows\_examined: 160” part.