0

I have this entry in slow query log:

# User@Host: user[host] @  [ip]
# Thread_id: 1514428  Schema: db  Last_errno: 0  Killed: 0
# Query_time: 2.795454  Lock_time: 0.000116  Rows_sent: 15  Rows_examined: 65207  Rows_affected: 0  Rows_read: 65207
# Bytsent: 26618
SET timestamp=1407511874;
select off.*,translated_title,translated_description
from products off  USE INDEX(id_viewed)
INNER JOIN members mem ON off.uid = mem.id
Left Join product_language_new pol ON off.id = pol.offer_id and pol.language='en'
where off.approved=1
order by off.viewed
LIMIT 15;

When I explain this query, it's absolutely fine.

mysql> explain select off.*,translated_title,translated_description
from products off  USE INDEX(id_viewed)
INNER JOIN members mem ON off.uid = mem.id
Left Join product_language_new pol ON off.id = pol.offer_id and pol.language='en'
where off.approved=1
order by off.viewed
LIMIT 15;

+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
| id | select_type | table | type   | possible_keys           | key         | key_len | ref                       | rows | Extra       |
+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
|  1 | SIMPLE      | off   | index  | NULL                    | id_viewed   | 4       | NULL                      |    3 | Using where |
|  1 | SIMPLE      | mem   | eq_ref | PRIMARY                 | PRIMARY     | 4       | db.off.uid                |    1 | Using index |
|  1 | SIMPLE      | pol   | ref    | offer_id,id_language    | offer_id    | 5       | db.off.id                 |    4 |             |
+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
3 rows in set (0.17 sec)

How do I optimize this query? Why does explain show 3 rows and slow query log says it examined 65207 rows.

And yes, I have tried FORCE INDEX, without forcing index and what not, it only gets worse.

Iłya Bursov
  • 23,342
  • 4
  • 33
  • 57
codefreak
  • 6,950
  • 3
  • 42
  • 51

1 Answers1

0

Looks like the query couldn't use id_viewed as it is not in the possible_keys column and the extra column just says Using where.

I'm also guessing rows refers to the returned rows which you have LIMITed, not the number of rows that it had to scan.. you could remove the LIMIT for the EXPLAIN.

I'd try a composite index on (id, approved) and maybe a single one on (viewed) for the ordering.

Arth
  • 12,789
  • 5
  • 37
  • 69