mysql - My SQL query is slow -

today have problem in sql query, request take 30 seconds, error in php.

my sql query :

select v.id      , v.title       , v.thumb     video v   join join_video_date d     on d.id_video = v.id    join join_video_category c      on c.id_video = v.id  c.id_category = $id  order      d.date desc   limit $page,24 

this query take 24 video of category ordered date, query slow.

i have indexes tables, example of table "join_video_date"

join_video_date (id, id_video, date) 

index :

primary -> id id_video -> id_video date -> date 

this query:

select v.id, v.title, v.thumb   video v join      join_video_date d      on d.id_video = v.id join      join_video_category c       on c.id_video = v.id  c.id_category = $id  order d.date desc   limit $page,24; 

the first thing try indexes: join_video_category(id_category, id_video), join_video_date(id_video, date). assume video(id) has index.

your query suggests video have multiple categories , multiple dates. if so, query not doing want anyway. 1 way prevent move condition on categories where clause:

select v.id, v.title, v.thumb   video v join      join_video_date d      on d.id_video = v.id  v.id in (select c.id_video                 join_video_category c                 c.id_category = $id                )  order d.date desc   limit $page,24;