Optimizing a PHP page: MySQL bottleneck

Question

I have a page that is taking 37 seconds to load. While it is loading it pegs MySQL's CPU usage through the roof. I did not write the code for this page and it is rather convoluted so the reason for the bottleneck is not readily apparent to me.

I profiled it (using kcachegrind) and find that the bulk of the time on the page is spent doing MySQL queries (90% of the time is spent in 25 different mysql_query calls).

The queries take the form of the following with the tag_id changing on each of the 25 different calls:

SELECT * FROM tbl_news WHERE news_id
 IN (select news_id from
 tbl_tag_relations WHERE tag_id = 20)

Each query is taking around 0.8 seconds to complete with a few longer delays thrown in for good measure... thus the 37 seconds to completely load the page.

My question is, is it the way the query is formatted with that nested select that is causing the problem? Or could it be any one of a million other things? Any advice on how to approach tackling this slowness is appreciated.

Running EXPLAIN on the query gives me this (but I'm not clear on the impact of these results... the NULL on primary key looks like it would be bad, yes? The number of results returned seems high to me as well as only a handful of results are returned in the end):

1    PRIMARY     tbl_news   ALL NULL    NULL    NULL    NULL    1318    Using where
2   DEPENDENT SUBQUERY  tbl_tag_relations   ref FK_tbl_tag_tags_1   FK_tbl_tag_tags_1   4   const   179 Using where

Answer 1

I'e addressed this point in Database Development Mistakes Made by AppDevelopers. Basically, favour joins to aggregation. IN isn't aggregation as such but the same principle applies. A good optimize will make these two queries equivalent in performance:

SELECT * FROM tbl_news WHERE news_id
 IN (select news_id from
 tbl_tag_relations WHERE tag_id = 20)

and

SELECT tn.*
FROM tbl_news tn
JOIN tbl_tag_relations ttr ON ttr.news_id = tn.news_id
WHERE ttr.tag_id = 20

as I believe Oracle and SQL Server both do but MySQL doesn't. The second version is basically instantaneous. With hundreds of thousands of rows I did a test on my machine and got the first version to sub-second performance by adding appropriate indexes. The join version with indexes is basically instantaneous but even without indexes performs OK.

By the way, the above syntax I use is the one you should prefer for doing joins. It's clearer than putting them in the WHERE clause (as others have suggested) and the above can do certain things in an ANSI SQL way with left outer joins that WHERE conditions can't.

So I would add indexes on the following:

• tbl_news (news_id)
• tbl_tag_relations (news_id)
• tbl_tag_relations (tag_id)

and the query will execute almost instantaneously.

Lastly, don't use * to select all the columns you want. Name them explicitly. You'll get into less trouble as you add columns later.