2

I got quite a big sql query, which needs to select random rows, but because table is large, order by rand() is taking really long.

            $getdata = $this->db->query("
            SELECT DISTINCT property.id,property.unid,property.imported,property.userid,
            CONCAT(user.firstname) as username,property.url,
            IFNULL(user.thumbpic,'temp/misc/noimage.png') as profilepic,
            property.bedrooms,property.beds,type.meta_val as type,property.accommodates,property.price,
            IFNULL((select thumbimg from tblpropertyimages where pid=property.id limit 1),'temp/misc/noimage.png') as image,
            property.name as propertyname,(select sum(rating) from tblreviews where pid=property.id) as totalrating,
            (select count(id) from tblreviews where pid=property.id) as countratings,
            location.name as cityname from tblproperty as property join tbluser as user on property.userid=user.id 
            join tblcommon as type on property.type=type.id 
            left join tblpropertyamenities as p_amenities on property.id=p_amenities.pid 
            join tbllocation as location on location.id=property.city 
            WHERE property.status='Active' and user.status='Active' 
            $home $q limit $limit offset $start");

What is the best solution for selecting random rows, for this specific query?

user3186034
  • 45
  • 1
  • 10
  • Could you first generate a list of random numbers, and then select only those rows contained in your list (e.g. maybe by joining it to a #rownum or some such). That way you're not actually sorting anything. (Generating a list of random numbers is addressed here: http://stackoverflow.com/questions/1045138/how-do-i-generate-random-number-for-each-row-in-a-tsql-select) – johnjps111 Feb 19 '17 at 18:49

1 Answers1

1

Depending on your detailed requirements, there are several faster approaches in here None is 'perfect', but each is probably 'good enough'.

Rick James
  • 135,179
  • 13
  • 127
  • 222