mysql speed problems
Any advice on how i can get this query to run faster?
SELECT *, p.* FROM grouped g INNER JOIN form p on p.id = g.id LEFT JOIN prospect ps ON (p.appphone = phone_bus OR p.appphone = phone_res OR p.appphone = phone_fax) AND p.appphone != '' WHERE p.agname like '%test%' ORDER BY p.agname DESC limit 0, 100
If i change the ON to not use OR it runs quickly. Like this it takes 30-40 seconds.
p.appphone, phone_bus, phone_res, phone_fax, and p.agname are all indexed in table.
You are doing an inner join, that should go fast if indexed properly, the left join on the match '%test%' is probably where it is taking the time.
it is going through your max 60k rows, (you are selecting all columns), and when it gets to the field agname, it has to traverse the string to match the word test somewhere in the string 60,000 times. I would say optimize the '%test%', if there's noway around this... you could
appphone - how many different scenarios are there --
ON (**p.appphone = phone_bus OR p.appphone = phone_res OR p.appphone = phone_fax**) AND p.appphone != ''
you could join on the key, and then use your nested OR statement in the WHERE clause, no?
Maybe you could say where p.appphone <> phone_print or something that will include this nested OR statement, but optimizes it so it just looks for what it's not instead of three different instances of what it could be....also does p.appphone really = '' or is it null? Not sure exactly how much faster it will make it but you could say where p.appphone is not null on the left join as mentioned earlier.
I would try to:
- Use ANY operator;
- moving p.appphonne != '' into the WHERE clause;
- precise SELECT list, 'cos * already includes the p.* fields.
Please, try this query:
SELECT *, p.* FROM grouped g JOIN form p ON p.id = g.id LEFT JOIN prospect ps ON p.appphone = ANY(ps.phone_bus, ps.phone_res, ps.phone_fax) WHERE p.agname LIKE '%test%' AND p.appphone != '' ORDER BY p.agname DESC LIMIT 0, 100;
Please, include EXPLAIN output of the query in your first message.