Improve long mysql query

Question

I have a php mysql query like this

$query = <<<EOS
    SELECT * FROM articles 
      FORCE INDEX (articleindex) 
      WHERE category='$thiscat' AND did>'$thisdid' 
        AND mid!='$thismid' AND status='1' 
        AND group='$thisgroup' AND pid>'$thispid' 
      LIMIT 10
EOS;

As optimization, I've indexed all the parameters in articleindex and I use force index to force mysql to use the index, supposedly for faster processing.

But it seems that this query is still quite slow and it's causing a jam and maxing out the max mysql connection limit.

How we can improve on such long running query?

Answer 1

Forcing the index on articleindex is most likely hurting you because you are not actually referencing it in your where clause. Indexes exist to speed up searches by letting you quickly get to rows with certain values. I can't give a more precise answer without more details on your tables and the data contained there, but you should definitely start by dropping the force index. If that is still slow, perhaps you could post the result of EXPLAIN (your query).