CODEWITHSUNDEEP
phpmysql
Jared
Jared

Reputation: 301

Making an SQL query more efficient

I have a query that works, but it's taking at least 3 seconds to run so I think it can probably be faster. It's used to populate a list of new threads and show how many unread posts there are in each thread. I generate the query string before throwing it into $db->query_read(). In order to only grab results from valid forums, $ids is string with up to 50 values separated by commas.

The userthreadviews table has existed for 1 week and there are roughly 9,500 rows in it. I'm not sure if I need to set up a cron job to regularly clear out thread views more than a week old, or if I will be fine letting it grow.

Here's the query as it currently stands:

SELECT 
    `thread`.`title` AS 'r_title', 
    `thread`.`threadid` AS 'r_threadid', 
    `thread`.`forumid` AS 'r_forumid', 
    `thread`.`lastposter` AS 'r_lastposter', 
    `thread`.`lastposterid` AS 'r_lastposterid', 
    `forum`.`title` AS 'f_title', 
    `thread`.`replycount` AS 'r_replycount', 
    `thread`.`lastpost` AS 'r_lastpost', 
    `userthreadviews`.`replycount` AS 'u_replycount', 
    `userthreadviews`.`id` AS 'u_id',
    `thread`.`postusername` AS 'r_postusername', 
    `thread`.`postuserid` AS 'r_postuserid' 
FROM 
    `thread` 
INNER JOIN 
    `forum` 
    ON (`thread`.`forumid` = `forum`.`forumid`) 
LEFT JOIN 
    (`userthreadviews`) 
    ON (`thread`.`threadid` = `userthreadviews`.`threadid` 
    AND `userthreadviews`.`userid`=$userid) 
WHERE 
    `thread`.`forumid` IN($ids) 
    AND `thread`.`visible`=1 
    AND `thread`.`lastpost`> time() - 604800
ORDER BY `thread`.`lastpost` DESC LIMIT 0, 30

An alternate query that joins the post table (to only show threads where user has posted) is actually twice as fast, so I think there's got to be something in here that could be changed to speed it up. Could someone provide some advice?

Edit: Sorry, I had put the EXPLAIN in front of the alternate query. Here is the correct output: As Requested, here is the output generated by EXPLAIN SELECT: EXPLAINS Query

Upvotes: 0

Views: 147

Answers (4)

J. Bruni
J. Bruni

Reputation: 20492

Suggestions:

  • move the conditions from the WHERE clause to the JOIN clause
  • put the JOIN with the conditions before the other JOIN
  • make sure you have proper indexes and that they are being used in the query (create the ones you'll need... too much indexes can be as bad as too few)

Here is my suggestion for the query:

SELECT 
    `thread`.`title` AS 'r_title',
    `thread`.`threadid` AS 'r_threadid',
    `thread`.`forumid` AS 'r_forumid',
    `thread`.`lastposter` AS 'r_lastposter',
    `thread`.`lastposterid` AS 'r_lastposterid',
    `forum`.`title` AS 'f_title',
    `thread`.`replycount` AS 'r_replycount',
    `thread`.`lastpost` AS 'r_lastpost',
    `userthreadviews`.`replycount` AS 'u_replycount',
    `userthreadviews`.`id` AS 'u_id',
    `thread`.`postusername` AS 'r_postusername',
    `thread`.`postuserid` AS 'r_postuserid' 
FROM
    `thread` 
    INNER JOIN (`forum`)
        ON ((`thread`.`visible` = 1)
        AND (`thread`.`lastpost` > $time)
        AND (`thread`.`forumid` IN ($ids))
        AND (`thread`.`forumid` = `forum`.`forumid`))
    LEFT JOIN (`userthreadviews`)
        ON ((`thread`.`threadid` = `userthreadviews`.`threadid`)
        AND (`userthreadviews`.`userid` = $userid))
ORDER BY
    `thread`.`lastpost` DESC
LIMIT
    0, 30

These are good candidates to be indexed:

- `forum`.`forumid`
- `userthreadviews`.`threadid`
- `userthreadviews`.`userid`
- `thread`.`forumid`
- `thread`.`threadid`
- `thread`.`visible`
- `thread`.`lastpost`

It seems you already have lots of indexes... so, make sure you keep the ones you really need and remove the useless ones.

Upvotes: 0

Alain
Alain

Reputation: 36944

To have no indexes on joins / where (used key = NULL on explain), this is the reason why your queries are slow. You should index them in such a way :

CREATE INDEX thread_forumid_index ON thread(forumid);
CREATE INDEX userthreadviews_forumid_index ON userthreadviews(forumid);

Documentation here

Upvotes: 1

JvdBerg
JvdBerg

Reputation: 21856

Have a look at the mysql explain statement. It gives you a execution plan of your query.

Once you know the plan, you can check if you have got a index on the fields involved in the plan. If not, create them.

Perhaps the plan reveals details about how the query can be written in another way, such that the query will be more optimized.

Upvotes: 2

petergt
petergt

Reputation: 79

Try to index the table forumid if it is not indexed

Upvotes: 1

Related Questions