2

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?

Improve this question
2
  • 4
    You start by posting table and index definitions and the query explain plan (and formatting the query such that it is readable).
    – mustaccio
    Dec 13, 2017 at 13:22
  • 3
    If you have any indexes, please add their definitions. If there are no indexes, please state that explicitly in your question, so that a potential answerer can know what needs addressing. Also, the query's current explain plan can be helpful in such cases too.
    – Andriy M
    Dec 13, 2017 at 14:07

1 Answer 1

Reset to default
3

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.

Improve this answer
8
  • 1
    Note that, with LEFT JOINs, some filtering in the ON clause may make sense. The filtering is being used to determine whether a specific row from the right (nullable) table should be matched up or not. In the WHERE clause, you have to allow for the values from the table on the right to be NULL (or else you'll effectively change the LEFT JOIN into an INNER JOIN). Of course, as you note, if some of those can be converted to INNER JOINs, then filtering should definitely move to the WHERE clause.
    – RDFozz
    Dec 13, 2017 at 17:45
  • 1
    @RDFozz - The Optimizer can start with either table in an INNER JOIN, but it needs to start with the "left" table in a join that is truly LEFT JOIN. The added flexibility may provide significant optimization opportunities.
    – Rick James
    Dec 13, 2017 at 17:56
  • 1
    @sasuke - I added a necessary index. Even without the UNION, it would probably help a little.
    – Rick James
    Dec 13, 2017 at 18:00
  • I understand the pluses of making the joins INNER; just wanted to caution the reader about changing the ON clause if the LEFT JOINs turn out to be mandatory for their purposes.
    – RDFozz
    Dec 13, 2017 at 18:51
  • @RickJames I'm using post_date for pagination, so post_date depend on last page post date, then how should I index this?
    – baldraider
    Dec 14, 2017 at 6:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.