Query optimization using these MySQL tables

Question

I need help optimizing queries on 2 tables. One will contain about a million, and the other will contain about 10 million lines.

here is the table structure -

Table structure

Search Examples and Search Queries

These requests take a very long time, about 15-20 seconds, in some cases within a minute

So, please take a look and offer me how I can make them much faster for practical use.

PS here are the query plans for these two search and query patterns ...

Explain: search query

Explain: view request

Answer 1

You might want to put indexes in the fields you filter on, for example category_id, site_enabled, and video collection date.

In general, with tables of this size, the goal is to reduce the maximum possible number of rows that need to be combined.

For your view request, I can only imagine that it is slow if it tries to combine all the rows and then filter the results to 20. You can try rewriting it to use the nested selection in the top 20 vids with the cutest ones. Sort of:

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 could not see where your request used anything from the favorites table of videos, so combining might be redundant.