How to optimize a query with multiple tables and left joins?

Question

I'm trying to run a query on MySQL database, but the query takes too much time, even though the database is not too large, around 100,000 of rows in each table.I have not defined or created any indexes till now.

These are my tables:

• post_feeds:

  +-------------------+--------------+------+-----+---------+----------------+
  | Field             | Type         | Null | Key | Default | Extra          |
  +-------------------+--------------+------+-----+---------+----------------+
  | id                | int(11)      | NO   | PRI | NULL    | auto_increment |
  | post_id           | int(11)      | YES  |     | NULL    |                |
  | post_title        | varchar(200) | YES  |     | NULL    |                |
  | post_date         | int(11)      | YES  |     | NULL    |                |
  | post_featured_img | varchar(200) | YES  |     | NULL    |                |
  | post_scope        | int(11)      | YES  |     | 0       |                |
  +-------------------+--------------+------+-----+---------+----------------+
  

• post_likes:

  +------------------+------------+------+-----+---------+-------+
  | Field            | Type       | Null | Key | Default | Extra |
  +------------------+------------+------+-----+---------+-------+
  | post_id          | int(11)    | NO   | PRI | NULL    |       |
  | post_likes_count | bigint(20) | NO   |     | 0       |       |
  +------------------+------------+------+-----+---------+-------+
  

• post_descriptions:

  | Field            | Type         | Null | Key | Default | Extra |
  +------------------+--------------+------+-----+---------+-------+
  | post_id          | int(11)      | NO   | PRI | NULL    |       |
  | post_description | longtext     | NO   |     | NULL    |       |
  | post_link        | varchar(200) | NO   |     | NULL    |       |
  +------------------+--------------+------+-----+---------+-------+
  

• user_post_bookmarks:

  +---------+---------+------+-----+---------+----------------+
  | Field   | Type    | Null | Key | Default | Extra          |
  +---------+---------+------+-----+---------+----------------+
  | id      | int(11) | NO   | PRI | NULL    | auto_increment |
  | user_id | int(11) | NO   |     | NULL    |                |
  | post_id | int(11) | NO   |     | NULL    |                |
  +---------+---------+------+-----+---------+----------------+
  

• user_post_likes:

  +---------+-------------+------+-----+---------+----------------+
  | Field   | Type        | Null | Key | Default | Extra          |
  +---------+-------------+------+-----+---------+----------------+
  | id      | int(11)     | NO   | PRI | NULL    | auto_increment |
  | user_id | varchar(45) | NO   |     | NULL    |                |
  | post_id | varchar(45) | NO   |     | NULL    |                |
  +---------+-------------+------+-----+---------+----------------+
  

This is my query:

SELECT post_feeds.post_id
      ,post_feeds.post_title
      ,post_feeds.post_date
      ,post_feeds.post_featured_img     AS post_featured_image
      ,ifnull(post_likes.post_likes_count ,0) AS post_likes_count
      ,(user_post_bookmarks.post_id IS NOT NULL) AS post_bookmarked,
      ,(user_post_likes.post_id IS NOT NULL) AS post_liked
      ,post_descriptions.post_description
      ,post_descriptions.post_link
FROM   cloneposts_base.post_feeds
       LEFT JOIN cloneusers_base.user_post_bookmarks
            ON  user_post_bookmarks.post_id = post_feeds.post_id
                AND user_post_bookmarks.user_id = 1
       LEFT JOIN cloneposts_base.post_likes
            ON  post_likes.post_id = post_feeds.post_id
       LEFT JOIN cloneusers_base.user_post_likes
            ON  user_post_likes.post_id = post_feeds.post_id
                AND user_post_likes.user_id = 1
       LEFT JOIN cloneposts_base.post_descriptions
            ON  post_descriptions.post_id = post_feeds.post_id
WHERE  post_feeds.post_date<unix_timestamp()
       AND (post_scope=0 OR post_scope=1)
ORDER BY
       post_feeds.post_date DESC limit     10

The query takes around 4-5 minutes to query all the data. How can I optimize my query or database tables to minimize the query time as low possible, like in milliseconds or seconds?

Answer 1

post_feeds -- If post_id uniquely determines the row, it should be the PRIMARY KEY. Then get rid of id. This change may help certain JOINs.

bookmarks looks like a many:many mapping table. If so, follow the advice here. Ditto for user_post_likes. This particular case is a serious performance bottleneck the way you have it.

The ON clause should say only how the tables are related, it should not be used for "filtering". Such should be moved to the WHERE clause. In some situations, it modifies the semantics of the query. This may be part of the problem you are experiencing.

post_likes is actually a good idea. This keeps the frequent updating of that one column away from other "post-related" activity.

Don't use LEFT JOIN when JOIN is sufficient. It inhibits certain optimizations.

You seem to have 3 separate databases; why? (This does not impact speed; rather, it impacts my understanding of the schema.)

OR is usually a performance killer. One workaround, that should work for your query, is to turn it into a UNION:

( SELECT ...
    WHERE  post_feeds.post_date<unix_timestamp()
      AND  post_feeds.post_scope=0
    ORDER BY post_feeds.post_date DESC limit 10 )
UNION ALL   -- ALL is faster than DISTINCT; is there a chance of dups?
( SELECT ...
    WHERE  post_feeds.post_date<unix_timestamp()
      AND  post_feeds.post_scope=1
    ORDER BY post_feeds.post_date DESC limit 10 )
ORDER BY post_feeds.post_date DESC limit 10

On the other hand, is post_feeds.post_date<unix_timestamp() really needed? Or do you have things being posted in the future?

If you do the UNION, this is very important:

INDEX(post_scope, post_date)

Take care of those things, then come back for more advice/abuse.