CODEWITHSUNDEEP
mysqlsqlperformance
arberb
arberb

Reputation: 960

MySQL Query Extremely Slow any Suggestions?

I have this MySQL query which seems to be very very slow. It takes 3 seconds to run. This is trying to get all the posts from either who they're following or any interests they have. Also its trying to make sure it doesn'tshow any duplicate shares that match any post_id. What do you guys think I should do? 

SELECT p.*,
       IFNULL(post_data_share, UUID()) AS unq_share,
       UNIX_TIMESTAMP(p.post_time) AS a
FROM posts p
LEFT JOIN users_interests i ON (i.user_id=1
                                AND p.post_interest = i.interest)
LEFT JOIN following f ON (f.user_id=1
                          AND p.post_user_id = f.follower_id)
WHERE (post_user_id=1
       OR f.follower_id IS NOT NULL
       OR i.interest IS NOT NULL)
  AND (POST_DATA_SHARE NOT IN
         (SELECT POST_ID
          FROM posts p
          LEFT JOIN following f ON f.user_id=1
          AND p.post_user_id = f.follower_id
          LEFT JOIN users_interests i ON (i.user_id=1
                                          AND p.post_interest = i.interest)
          WHERE (post_user_id=1
                 OR f.follower_id IS NOT NULL
                 OR i.interest IS NOT NULL))
       OR POST_DATA_SHARE IS NULL)
GROUP BY unq_share
ORDER BY `post_id` DESC LIMIT 10;

Upvotes: 0

Views: 52

Answers (2)

Venkat.R
Venkat.R

Reputation: 7736

Below are the Performance tips will definitely make difference.

  • Try Explain Statement.
  • Alter the Table by Normalize your tables by adding Primary Key and Foreign Key
  • Add Index for Repeated Values.
  • Avoid select * from table. Mention the specify column name.
  • Convert IS NULL to (='')
  • Convert IS NOT NULL to (!='')
  • Avoid More OR Condition.
  • MySQL Configurations to explore
    • key_buffer_size
    • innodb_buffer_pool_size
    • query_cache_size
    • thread_cache

Much more refer this SO Answer Best my.cnf configuration for a 8GB MySQL server with MyISAM use only

Upvotes: 2

user3728292
user3728292

Reputation: 134

I would start by looking at the execution plan for the query. Here is a link to MySQL documentation on the EXPLAIN keyword to show you how the optimizer is structuring your query: http://dev.mysql.com/doc/refman/5.5/en/using-explain.html

If CPU usage is low, likely the bottleneck is disk access for large table scans.

The way the query is executed is often different from how it was written. Once you see how the execution plan is structured, you are probably going to create indexes on the largest joins. Every table should have one clustered index (often it is created by default), but other fields can often benefit from unclustered indexes.

If the problem is extremely bad and this is vital to your application, you may want to consider reorganizing the database.

Upvotes: 1

Related Questions