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.

Answers


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:

  1. Use ANY operator;
  2. moving p.appphonne != '' into the WHERE clause;
  3. 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.


Need Your Help

Clear fields on CreateUserWizard, Login control

asp.net login createuserwizard

I have a createuserwizard and a login control on a page. both of them are customized (standard textboxes are replaced by RadTextBoxes)