CODEWITHSUNDEEP
mysqlsql
Syed Daniyal Asif
Syed Daniyal Asif

Reputation: 736

MySql: How to reduce execution time of this Query?

How to customize this query, It takes around 30 Second to take out results, total records in 'videos' table are about 0.5 million, 3 million members are present in 'Members' Table, is there any alternate Query or should i break this query in 2 select queries ?

user_id is Indexed vid_id is Indexed

select a.ref_url , a.source , a.video_name , a.viewers , b.username ,           
c.points from 
members_videos a 
inner join Members b on a.user_id = b.user_id
inner join rankings      c on c.user_id = b.user_id

where a.cat_ids in (123,234,52,234,423,122) not in (110,99)
order by a.vid_id Desc limit 10

Upvotes: 0

Views: 138

Answers (1)

BlueSky
BlueSky

Reputation: 79

There are multiple factors:

  1. Make sure members_videos, Members and rankings have index on user_id column.
  2. Break the query; eliminate Order By clause. Get the data in temporary table.
  3. Select the data from temporary table and put Order By Clause over there.

Alternate, try sub-query.

SELECT *
FROM
(
select a.ref_url , a.source , a.video_name , a.viewers , b.username ,           
c.points from 
members_videos a 
inner join Members b on a.user_id = b.user_id
inner join rankings      c on c.user_id = b.user_id
where a.cat_ids in (123,234,52,234,423,122) not in (110,99)
) as T
order by T.vid_id Desc limit 10

Upvotes: 1

Related Questions