Optimize sql query to use indexes or rewrite sql

Question

I have sql query

SELECT 
  bd2.*, 
  bdmax.last_video, 
  bdmax.number_videos 
FROM 
  video bd2 
  JOIN 
    ( SELECT 
      bd.video_cat_id, 
      MAX(bd.last_poster_time) AS last_video, 
      COUNT(bd.video_id) as number_videos 
    FROM 
      video bd 
    GROUP BY 
      bd.video_cat_id 
    ) AS bdmax 
    ON bd2.video_cat_id = bdmax.video_cat_id 
      AND bd2.last_poster_time = bdmax.last_video;

Table keys are

  PRIMARY KEY (`video_id`),
  KEY `video_cat_id` (`video_cat_id`),
  KEY `video_user_id` (`video_user_id`),
  KEY `video_time` (`video_time`),
  KEY `video_urn` (`video_urn`),
  KEY `last_poster_time` (`last_poster_time`),
  KEY `video_username` (`video_username`),
  KEY `video_approval` (`video_approval`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=45619 ;

How can optimize this query to use indexes?

It already is using your indexes? You query the whole table so a scan isn't unperformant. — Caleth, Nov 06 '17 at 12:33
It's fine like it is. Because you are querying the whole table, the whole table has to be read. The query planner is giving you a good plan — Caleth, Nov 06 '17 at 12:49
**Why** do you think your query needs to change? I don't see any problem with it — Caleth, Nov 06 '17 at 13:24
Yes it does. It does an index lookup and an index scan. You may find that a *different* result will be faster, but the `bdmax.number_videos` implies that you need to *count all the rows* — Caleth, Nov 06 '17 at 13:41
Thanks for explains. So, no need doing anything to optimize? This is very huge query for mysql. — Begemont, Nov 06 '17 at 13:52
I don't know how you are using the result. can you omit the rowcount in some circumstances? — Caleth, Nov 06 '17 at 13:54
I need only cat ids, cat names, last 3 videos with post times in each cat id — Begemont, Nov 06 '17 at 14:06
Have another code for this $categoryId = (int) $categoryId; $sql = ' SELECT v.* FROM video v WHERE video_cat_id = ' . $categoryId . ' AND video_approval = 1 ORDER BY video_time DESC LIMIT 0, 3'; $result = $db->sql_query($sql); — Begemont, Nov 06 '17 at 14:10
Have a look at https://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category/1442867#1442867 — Caleth, Nov 06 '17 at 14:15

score 0 · Answer 1 · answered Nov 07 '17 at 23:24

0

Don't clutter things by putting the table name at the beginning of column names. Then this 'composite' index

INDEX(cat_id, last_poster_time, id)

will help the subquery (by being "covering") and the JOIN.

Part of this query is "groupwise max". But the COUNT means that optimizations for the max are not useful.

answered Nov 07 '17 at 23:24

Rick James

135,179
13
127
222

Thank You for answer! Now table have keys PRIMARY KEY (`video_id`), KEY `video_cat_id` (`video_cat_id`), KEY `video_user_id` (`video_user_id`), KEY `video_time` (`video_time`), KEY `video_urn` (`video_urn`), KEY `last_poster_time` (`last_poster_time`), KEY `video_username` (`video_username`), KEY `video_approval` (`video_approval`), KEY `video_cat_id_2` (`video_cat_id`,`last_poster_time`,`video_id`) It's ok? https://image.ibb.co/fDo4Aw/query.jpg – Begemont Nov 08 '17 at 07:33
Good. But now you can get rid of `KEY video_cat_id (video_cat_id)` as being redundant (same as the left most column(s) of the new index). Does it run faster? That is the real deciding point. – Rick James Nov 08 '17 at 14:09
I can delete that index, and create only that You adwise, do it? – Begemont Nov 08 '17 at 14:22
Looks like you have already added the better index. This would remove the unnecessary one: `ALTER TABLE video DROP INDEX video_cat_id;` – Rick James Nov 08 '17 at 15:02
Now table have keys PRIMARY KEY (`video_id`), KEY `video_user_id` (`video_user_id`), KEY `video_time` (`video_time`), KEY `video_urn` (`video_urn`), KEY `last_poster_time` (`last_poster_time`), KEY `video_username` (`video_username`), KEY `video_approval` (`video_approval`), KEY `video_cat_id` (`video_cat_id`,`last_poster_time`,`video_id`) https://image.ibb.co/coRHfw/query.jpg Many thanks for all!!!! – Begemont Nov 08 '17 at 15:20

Optimize sql query to use indexes or rewrite sql

1 Answers1