CODEWITHSUNDEEP
mysqlindexingexplain
user1532606
user1532606

Reputation:

MySQL has indexed tables and EXPLAIN looks good, but still not using index

I am trying to optimize a query, and all looks well when I got to "EXPLAIN" it, but it's still coming up in the "log_queries_not_using_index".

Here is the query:

SELECT t1.id_id,t1.change_id,t1.like_id,t1.dislike_id,t1.user_id,t1.from_id,t1.date_id,t1.type_id,t1.photo_id,t1.mobile_id,t1.mobiletype_id,t1.linked_id 
FROM recent AS t1 
LEFT JOIN users AS t2 ON t1.user_id = t2.id_id 
WHERE t2.active_id=1 AND t1.postedacommenton_id='0'  AND t1.type_id!='Friends' 
ORDER BY t1.id_id DESC LIMIT 35;

So it grabs like a 'wallpost' data, and then I joined in the USERS table to make sure the user is still an active user (the number 1), and two small other "ANDs".

When I run this with the EXPLAIN in phpmyadmin it shows

id | select_type | table | type   | possible_keys     | key     | key_len | ref                       | rows | Extra
1  | SIMPLE      | t1    | index  | user_id           | PRIMARY | 4       | NULL                      | 35   | Using where
1  | SIMPLE      | t2    | eq_ref | PRIMARY,active_id | PRIMARY | 4       | hnet_user_info.t1.user_id | 1    | Using where

It shows the t1 query found 35 rows using "WHERE", and the t2 query found 1 row (the user), using "WHERE"

So I can't figure out why it's showing up in the log_queries_not_using_index report.

Any tips? I can post more info if you need it.

Upvotes: 1

Views: 763

Answers (1)

user166390
user166390

Reputation:

tldr; ignore the "not using index warning". A query execution time of 1.3 milliseconds is not a problem; there is nothing to optimize here - look at the entire performance profile to find bottlenecks.

Trust the database engine. The database query planner will use indices when it determines that doing so is beneficial. In this case, due to the low cardinality estimates (35x1), the query planner decided that there was no reason to use indices for the actual execution plan. If indices were used in a trivial case like this it could actually increase the query execution time.

As always, use the 97/3 rule.

Upvotes: 2

Related Questions