Improving my query to get posts on the homepage - Takes 0.5171 sec

Question

This query is running very slow; it used to be 3 separate queries but I've consolidated it into 1. I assume it's slow because of the subselects it's doing, but I assume there might be a better way to query for this info.

* Background:*

users is just user accounts.
posts is all the post content.
posts_int is just 2 fields, postid and intid. This stores interest ID's for posts.
int_usr is also just 2 fields, userid and intid. This stores interest ID's for users.

All of the "intid" field names are based on another table that simply has ids and names for each interest. This query returns all posts for this user that are relevant to their selected interests, stored in integers in int_usr.

The query:

SELECT DISTINCT 
users.first_name,
users.last_name,
posts.id,
posts.userid,
posts.comments,
posts.date,
posts.comment 
FROM posts 
LEFT JOIN users ON 
posts.userid = users.id 
LEFT JOIN posts_int ON 
posts.id = posts_int.postid 
WHERE 
posts_int.intid IN (
SELECT DISTINCT 
  intid 
FROM int_usr 
WHERE 
  intid IN (
      SELECT DISTINCT 
      intid 
       FROM int_usr 
       WHERE 
      userid = '1' 
  ) 
ORDER BY intid ASC 
)
AND posts.deleted = '0' 
AND users.deleted = '0' 
ORDER BY 
posts.id DESC 
LIMIT 0,12

Any help is appreciated!

Ryan

EDIT: Here is the explain you requested:

* EXPLAIN SQL: *

+----+--------------------+-----------+--------+----------------+---------+---------+---------------------------+------+----------------------------------------------+
| id | select_type        | table     | type   | possible_keys  | key     | key_len | ref                       | rows | Extra                                        |
+----+--------------------+-----------+--------+----------------+---------+---------+---------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | posts_int | ALL    | postid         | NULL    | NULL    | NULL                      |   87 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | posts     | eq_ref | PRIMARY,userid | PRIMARY | 4       | bunchify.posts_int.postid |    1 | Using where                                  |
|  1 | PRIMARY            | users     | eq_ref | PRIMARY        | PRIMARY | 4       | bunchify.posts.userid     |    1 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | int_usr   | ALL    | NULL           | NULL    | NULL    | NULL                      |  288 | Using where; Using temporary                 |
|  3 | DEPENDENT SUBQUERY | int_usr   | ref    | userid         | userid  | 4       | const                     |   19 | Using where; Using temporary                 |
+----+--------------------+-----------+--------+----------------+---------+---------+---------------------------+------+----------------------------------------------+
5 rows in set (0.00 sec)

Can you post the `SHOW CREATE TABLE` for each table, and the `EXPLAIN SELECT ` output? — Konerak, Sep 14 '11 at 22:13
Those `IN` queries will probably be showing up as dependant sub queries and be getting re-evaluated [for each row.](http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql/3417190#3417190) — Martin Smith, Sep 14 '11 at 22:17
@Ryan: (1) Please, provide the `EXPLAIN` of your query. (2) Your `SELECT DISTINCT intid ... WHERE intid IN ( SELECT DISTINCT intid ...` does not make any sense -- it should be collapsed. (3) All `IN` subqueries can be replaced by JOIN -- this can impact the performance. — dma_k, Sep 14 '11 at 22:20
@dma_k Actually having an extra level of nesting can help with `IN` clauses - See [How to force the inner query to execute first](http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/) although not quite as the OP has it. — Martin Smith, Sep 14 '11 at 22:27
I added the EXPLAIN for the query, and the SHOW CREATE TABLE output for the relevant tables. — Ryan, Sep 14 '11 at 22:31

Jimmy · Accepted Answer · 2011-09-14T22:24:22.123

3

You can simplify things a bit by getting rid of the extra level of nesting in

posts_int.intid IN (
SELECT DISTINCT 
  intid 
FROM int_usr 
WHERE 
  intid IN (
      SELECT DISTINCT 
      intid 
       FROM int_usr 
       WHERE 
      userid = '1' 
  ) 
ORDER BY intid ASC 
)

and then changing the left joins to inner joins because you're checking the right tables in your where clause anyways.

Something like

SELECT DISTINCT <blah>
  FROM posts 
  JOIN users 
       ON posts.userid = users.id 
  JOIN posts_int 
       ON posts.id = posts_int.postid 
  JOIN int_usr 
       on posts_int.intid = int_usr.intid and int_usr.userid = '1'
 where posts.deleted = '0' 
   AND users.deleted = '0' 
ORDER BY posts.id DESC 
LIMIT 0,12

edited Sep 14 '11 at 22:24

answered Sep 14 '11 at 22:16

Jimmy

89,068
17
119
137

WOW, this works and the page loads way faster now! Query took 0.0033 sec – Ryan Sep 14 '11 at 22:39
Thanks for this. I'm confused, though. When I have done joins before in a situation like this, they seem to join tons of data that I don't need, I dont get why this works. Or why you used JOIN instead of LEFT JOIN; I'm still learning I suppose heh – Ryan Sep 14 '11 at 22:42

score 1 · Answer 2 · answered Sep 14 '11 at 22:22

This should work faster

SELECT DISTINCT  users.first_name, users.last_name, users.fb_userid, users.fb_username, posts.id, posts.userid, posts.likes, posts.dislikes, posts.comments, posts.date, posts.youtube_id, posts.media_image, posts.link_title, posts.link_description, posts.link_url, posts.comment  
FROM posts  
LEFT JOIN users ON  posts.userid = users.id  
LEFT JOIN posts_int ON  posts.id = posts_int.postid  
WHERE  posts_int.intid IN (SELECT DISTINCT intid  
                           FROM int_usr  
                           WHERE userid = '1' 
                           ORDER BY intid ASC) AND posts.deleted = '0'  AND users.deleted = '0'  
ORDER BY  posts.id DESC  LIMIT 0,12

If this would do what I hope it should do it will be the fastest

SELECT DISTINCT  users.first_name, users.last_name, users.fb_userid, users.fb_username, posts.id, posts.userid, posts.likes, posts.dislikes, posts.comments, posts.date, posts.youtube_id, posts.media_image, posts.link_title, posts.link_description, posts.link_url, posts.comment  
FROM posts  
LEFT JOIN users ON  posts.userid = users.id  
LEFT JOIN posts_int ON  posts.id = posts_int.postid 
left join int_user ON  int_user.userid = posts.userid and int_user.intid = posts_int.intid and int_user.userid = '1' 
WHERE  posts_int.intid IN AND posts.deleted = '0'  AND users.deleted = '0'  
ORDER BY  posts.id DESC  LIMIT 0,12

Second one is missing the ending of "posts_int.intid IN ..." You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND posts.deleted = '0' AND users.deleted = '0' ORDER BY posts.id DESC LIMI' at line 6 — Ryan, Sep 14 '11 at 22:35

Improving my query to get posts on the homepage - Takes 0.5171 sec

2 Answers2