please help me with optimizing query with these MySQL tables

Question

Hi all!

I need some help optimizing queries on 2 tables. One will contain about a million and the other will contain about 10 million of rows.

here is the table structure --

Table Structure

Sample Browse and Search Queries

These queries takes a very long time, about 15-20 secs, in some cases over a minute

So, please have a look and suggest me how can i make them much faster for practical use

Thanks, Anjan

P.S. here are the query plans for those 2 sample search and browse queries ...

Explain: search query

Explain: Browse query

Answer 1

You might want to put indexes on the fields you're filtering on, like category_id, site_enabled, and video_collection date.

In general, with tables of that size, the goal is to cut down as much as possible on the number of rows that need to be joined.

For your browse query, I can only imagine it being that slow if it's trying to join all of the rows and then filter the results down to 20. You might try re-writing it to use a nested select on the top 20 vids with the most likes. Something like:

select 
v.video_id,
v.video_title,
v.video_link,
v.video_total_view,
v.video_likes_count,
v.video_collection_date as date,
v.video_time,
s.site_name

from 
(select 
video_id,
v.video_title,
v.video_link,
v.video_total_view,
v.video_likes_count,
v.video_collection_date as date,
v.video_time
from icumm_videos 
where category_id='1'
and 1277612659 - v.video_collection_date < 86400 * 7
order by video_likes_count desc limit 0,20) v
inner join icumm_sites s on v.site_id = s.site_id and site_enabled>0
left outer join icumm_featured_videos fv on v.video_id = fv.video_id

I couldn't see where your query was using anything from the featured videos table, so that join may be superfluous.