CODEWITHSUNDEEP
phpmysql
Arshad Zackeriya
Arshad Zackeriya

Reputation: 163

MySQL SELECT distinct very slow

For my mobile App i am using a web service to get the images and all records from the MySQL database, its like Instagram.

But SELECT DISTINCT taking more than 7 - 8 seconds.is there any thing that i can do for reduce the time?

SELECT distinct count(*) as totalrecord ,p.description,p.photo_id ,p.user_id,p.imagepath1 ,p.friends, p.is_report_photo,
    p.imagepath2,p.imagepath3,p.imagepath4,p.imagepath5,p.count_comment,p.count_like,p.created as photo_created ,

    (select username from users where id = p.user_id) as username , 
    (select country from users where id = p.user_id) as country, 
    (select email from users where id = p.user_id) as email , 
    (select image_path from users where id = p.user_id) as image_path ,
    (select gender from users where id = p.user_id) as gender, 
    (select privacy from users where id = p.user_id) as privacy,
    (select audio_privacy from users where id = p.user_id) as audio_privacy,
    (select video_privacy from users where id = p.user_id) as video_privacy,
    (select photo_privacy from users where id = p.user_id) as photo_privacy,

    If(pl.user_id && pl.photo_id !='',like_status,0) as islike ,
    If(pr.user_id && pr.photo_id !='',1,0) as isreport,
    p.user_visibility

    from friends as fr
    inner join users as u on u.id = (select distinct if (fr.user_id != $user_id,fr.user_id,(fr.to_user_id)) as rd
    from friends) && fr.read_status = '1'
    inner join photos as p on (p.user_visibility = 'p')
    LEFT JOIN photolike as pl ON pl.photo_id = p.photo_id && pl.user_id = $user_id
    LEFT JOIN photo_report as pr on pl. photo_id = pr.photo_id && pr.user_id = $user_id     
    ORDER BY p.photo_id DESC;

Upvotes: 1

Views: 573

Answers (2)

Sanka Bulathgama
Sanka Bulathgama

Reputation: 92

Use mysql indexing that will reduce the time

Upvotes: 1

rohitr
rohitr

Reputation: 371

Try using group-by clause instead of distinct.

Upvotes: 1

Related Questions