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?

What's the definition of index `articleindex`? In particular, what are the columns (and in what order)? You probably shouldn't use `FORCE INDEX`. MySQL will use an index if possible and if it will likely be faster than a table scan. — outis, Apr 16 '10 at 04:55
Also, if you're executing this query multiple times within the same script, use a prepared statement. They have the added advantage that prepared statement parameters are invulnerable to SQL injection. — outis, Apr 16 '10 at 04:57
Lastly, you should only select all columns if you're writing a DB client for a table browsing function (http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select). Select only the columns you need. — outis, Apr 16 '10 at 05:07
I removed force index and select columns and there is a drastic improvement! So fast that I don't even see it in mysql queue anymore! Thanks. — John Adawan, Apr 16 '10 at 05:49

score 1 · Answer 1 · edited Apr 16 '10 at 05:10

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).

score 0 · Answer 2 · answered Nov 28 '10 at 01:35

Hopefully you've heard this before, but what do you do when someone sends a request with

category="';drop table articles;"

...? Please please please use prepared statements with placeholders that will automatically sanitize your values to prevent people from hosing your database.

Improve long mysql query

2 Answers2