mysql random records give a duplicate data

Question

I am developing a website where records are not sequential, records are displayed randomly. I use simple mysql random method to display random records by using rand() with limit where limit is parameterized to get next record one by one.

I am using asp.net mvc framework and mysql database.

Here is mysql query.

select distinct
lw.Lawyer_id,
lw.name,
StateName,
ct.city,
Date_of_registration,
lawyer_views,
fl.practice
from
    registration lw
        left join
    states st ON st.Id = lw.State_Id
        left join
    city ct ON ct.id = lw.City_Id
        left join
    total_views lwv ON lwv.l_id = lw.L_id
        left join
    rsuper rsub ON rsub.l_id = lw.L_Id
        left join
    lfilter fl ON fl.L_Id = lw.L_Id
where
    lw.City_Id = '577'
        and rsub.special_id = 1
        and lw.status = 'Active'
        and lw.L_id != 1
        and lw.service = 'Free'
order by rand()
limit start , pageSize

in this query pageSize is total number of record which is 18 and start variable is change according to parameter but default value is 0.

It gives 18 records randomly on ajax request but problem is that it gives some duplicate records.

please tell me how to prevent this or other better solution for it and i also try other alternatives of rand() function but it not helps me. I used this tutorial for better improvements in random records. https://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/

You probably have duplicates in your underlying data that aren't obvious because you don't select all the fields — Kell, Jun 15 '16 at 09:44
its duplicates because of random records when i remove rand() then records are not duplicate. — NorCode, Jun 15 '16 at 09:47
`order by rand()` this is weird, there is no specific relationship with the table data itself — Anonymous Duck, Jun 15 '16 at 10:32
Maybe it is duped because you don't use your aliases properly throughout if column names exist in different tables as the same column name. You could utilize a derived table as an inner-most block. Fold that into your outer wrapper select/join — Drew, Jun 15 '16 at 15:50

score 0 · Answer 1 · edited May 23 '17 at 10:29

0

Look here: http://jan.kneschke.de/projects/mysql/order-by-rand/ There uncovered research about how do rand select. May be one of the solutions from there can solve your problem.

P.S. I can't make comments so write post but it should be in comments...

UPD: looked at MySQL select 10 random rows from 600K rows fast ...

edited May 23 '17 at 10:29

Community

1
1

answered Jun 15 '16 at 10:11

dadymax

1
2

mysql random records give a duplicate data

1 Answers1