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 --
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
P.S. here are the query plans for those 2 sample search and browse queries ...
Explain: search query
Explain: Browse query
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.