How to improve query performance by organizing, grouping, and combining

Question

I had a problem with ordering when combining multiple tables containing millions of data. But I got a solution, instead of joining with the excellent use of EXISTS, will improve performance on the following issue.

How to improve performance order 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 this method solves if I only need a result set from the parent table. What if I want to perform grouped calculations and other math functions other than the parent table. I wrote a query that solves my criteria, but it takes 20 seconds to complete. How can I optimize it?

Thank you in advance

Answer 1

Given that the request is already quite simple, the parameters that I would like to see are ...

• Execution Plan (to find missing indexes that you might add)
• caching (to ensure that SQL already has all the data in ram)
• de-normalization (to include query in flat selection)
• cache data in the application (so you can use something like PLINQ on it)
• Use bar based storage (redis, elastic)
• File group settings (physically moving db to faster drives)
• Divide your tables (to distribute raw data across multiple physical disks)

The further you enter this list, the more decisions are involved. I suppose it depends on how fast you need the request and how much you need your solution to scale.