CODEWITHSUNDEEP
mysqlsqlperformancesql-order-byinner-join
Tamizharasan
Tamizharasan

Reputation: 293

How to improve query performance with order by, group by and joins

I Had a problem with order by when joins multiple tables which have millions of data. But I got solution as instead of join with distinct use of EXISTS will improve performance from the following question

How to improve order by performance with joins in mysql

SELECT
   `tracked_twitter` . *,
   COUNT( * ) AS twitterContentCount,
   retweet_count + favourite_count + reply_count AS engagement 
FROM
   `tracked_twitter` 
   INNER JOIN
      `twitter_content` 
      ON `tracked_twitter`.`id` = `twitter_content`.`tracked_twitter_id` 
   INNER JOIN
      `tracker_twitter_content` 
      ON `twitter_content`.`id` = `tracker_twitter_content`.`twitter_content_id` 
WHERE
   `tracker_twitter_content`.`tracker_id` = '88'
GROUP BY
   `tracked_twitter`.`id` 
ORDER BY
   twitterContentCount DESC LIMIT 20 OFFSET 0

But that method solves if I only need the result set from the parent table. What if, I want to execute grouped count and other math functions in other than parent table. I wrote a query that solves my criteria, but it takes 20 sec to execute. How can I optimize it ??.

Thanks in advance

Upvotes: 1

Views: 907

Answers (1)

War
War

Reputation: 8628

Given the query is already fairly simple the options I'd look in to are ...

  • Execution plan (to find any missing indexes you could add)
  • caching (to ensure SQL already has all the data in ram)
  • de-normalisation (to turn the query in to flat select)
  • cache the data in the application (so you could use something like PLINQ on it)
  • Use a ram based store (redis, elastic)
  • File group adjustments (physically move the db to faster discs)
  • Partition your tables (to spread the raw data over multiple physical discs)

The further you go down this list the more involved the solutions become. I guess it depends how fast you need the query to be and how much you need your solution to scale.

Upvotes: 2

Related Questions