title: How to improve order by performance with joins in mysql
body: <p>I am working on a social network tracking application. Even joins works fine with proper indexing. But when I add the order by clause the total query takes 100 times longer time to execute. The following query I used to get the twitter_users without order by clause.</p>
  
  <pre><code>SELECT DISTINCT  `tracked_twitter`.id
  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` 
  AND  `tracker_twitter_content`.`tracker_id` =  '88'
  LIMIT 20
  </code></pre>
  
  <p>Showing rows 0 - 19 (20 total, Query took 0.0714 sec)</p>
  
  <p>But when I add order by clause ( on indexed column )</p>
  
  <pre><code>SELECT DISTINCT  `tracked_twitter`.id
  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` 
  AND  `tracker_twitter_content`.`tracker_id` =  '88'
  ORDER BY tracked_twitter.followers_count DESC 
  LIMIT 20
  </code></pre>
  
  <p>Showing rows 0 - 19 (20 total, Query took 13.4636 sec)</p>
  
  <p>EXPLAIN
  <a href="https://i.stack.imgur.com/bvjRb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bvjRb.png" alt="enter image description here"></a></p>
  
  <p>When I implement the order by clause in its table alone it doesn't take much time</p>
  
  <pre><code>SELECT * FROM `tracked_twitter` WHERE 1 order by `followers_count` desc limit 20
  </code></pre>
  
  <p>Showing rows 0 - 19 (20 total, Query took 0.0711 sec) [followers_count: 68236387 - 10525612]</p>
  
  <p>The table creation query as follows</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `tracked_twitter` (
      `id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `handle` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `location` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `description` text COLLATE utf8_unicode_ci,
      `profile_image` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `followers_count` int(11) NOT NULL,
      `is_influencer` tinyint(1) NOT NULL DEFAULT '0',
      `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `gender` enum('Male','Female','Other') COLLATE utf8_unicode_ci 
       DEFAULT NULL,
       PRIMARY KEY (`id`),
       KEY `followers_count` (`followers_count`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  </code></pre>
  
  <p>So join didn't slow the query and order by working well when I execute it on its table. So how can I improve performance?</p>
  
  <p><strong>UPDATE 1</strong></p>
  
  <p>@GordonLinoff method solves if i only need the result set from parent table. What f I want to know the number tweets per person (count of twitter_content which match the tracked_twitter table). How can I modify it? And if I want to have math functions on tweet content how do I do it ??</p>
  
  <pre><code>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  `is_influencer` !=  '1'
  AND  `tracker_twitter_content`.`tracker_id` =  '88'
  AND  `tracked_twitter_id` !=  '0'
  GROUP BY  `tracked_twitter`.`id` 
  ORDER BY twitterContentCount DESC 
  LIMIT 20 
  OFFSET 0
  </code></pre>