mysql query optimization

Question

Hello,

This is the table structure:

for_id
from_id
for_folder
from_folder
deleted

The number 2 represents my ID and this is the query:

SELECT *
FROM poruke_konverzacija
WHERE (
(
for_id =2
AND for_folder = "inbox"
)
OR (
from_id =2
AND from_folder = "inbox"
)
)
AND deleted !=2

I have near 500k records on that table and when i run explain, this is what i get:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  poruke_konverzacija  ALL  obrisano,za_id,od_id  NULL  NULL  NULL  456884  Using where; Using filesort

I have indexed for_id from_id for_folder from_folder and the query is really slow. Is there any other way to do so and get the same results ?

Answer 1

Index deleted also. As well...A union may be better, running two concurrent queries rather than one long one..

Additionally indexing/selecting on string fields is always slower. If you can make a look up table for the folders

ie

id ----- folder
1        inbox
2        sent
3        trash

in your poruke_konverzacija table convert the from_folder and for_folder to the equivalent ID

SELECT *
FROM poruke_konverzacija
WHERE
for_id =2
AND for_folder = "inbox"
AND deleted !=2
UNION
SELECT *
FROM poruke_konverzacija
WHERE
from_id =2
AND from_folder = "inbox"
AND deleted!=2

Answer 2

From the EXPLAIN output it looks like mysql is not using any of the keys. This is because you are using OR with two different fields. mysql will use index for OR if the conditions use same field eg. field1=v1 OR field1=v2. It will not use index if you use different fields in the condition See: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html.

Try it with UNION:

SELECT *
FROM poruke_konverzacija
WHERE for_id =2
  AND for_folder = "inbox"
  AND deleted !=2
UNION
SELECT *
FROM poruke_konverzacija
WHERE from_id =2
  AND from_folder = "inbox"
  AND deleted !=2