Fast mysql query to randomly select N usernames

Question

In my jsp application I have a search box that lets user to search for user names in the database. I send an ajax call on each keystroke and fetch 5 random names starting with the entered string. I am using the below query: select userid,name,pic from tbl_mst_users where name like 'queryStr%' order by rand() limit 5 But this is very slow as I have more than 2000 records in my table.

Is there any better approach which takes less time and let me achieve the same..? I need random values.

Your title should be Fast mysql query to randomly select different usernames instead of n because n is 5 here — , Jun 17 '12 at 05:05

score 1 · Answer 1 · answered Jun 17 '12 at 05:13

does table has indexing on name? if not apply it

2.MediaWiki uses an interesting trick (for Wikipedia's Special:Random feature): the table with the articles has an extra column with a random number (generated when the article is created). To get a random article, generate a random number and get the article with the next larger or smaller (don't recall which) value in the random number column. With an index, this can be very fast. (And MediaWiki is written in PHP and developed for MySQL.)

This approach can cause a problem if the resulting numbers are badly distributed; IIRC, this has been fixed on MediaWiki, so if you decide to do it this way you should take a look at the code to see how it's currently done (probably they periodically regenerate the random number column).

3.http://jan.kneschke.de/projects/mysql/order-by-rand/

score 1 · Accepted Answer · answered Jun 17 '12 at 06:45

How slow is "very slow", in seconds?

The reason why your query could be slow is most likely that you didn't place an index on name. 2000 rows should be a piece of cake for MySQL to handle.

The other possible reason is that you have many columns in the SELECT clause. I assume in this case the MySQL engine first copies all this data to a temp table before sorting this large result set.

I advise the following, so that you work only with indexes, for as long as possible:

SELECT userid, name, pic
FROM tbl_mst_users
JOIN (
    -- here, MySQL works on indexes only
    SELECT userid
    FROM tbl_mst_users
    WHERE name LIKE 'queryStr%'
    ORDER BY RAND() LIMIT 5
) AS sub USING(userid); -- join other columns only after picking the rows in the sub-query.

This method is a bit better, but still does not scale well. However, it should be sufficient for small tables (2000 rows is, indeed, small).

The link provided by @user1461434 is quite interesting. It describes a solution with almost constant performance. Only drawback is that it returns only one random row at a time.

I haven't index on my column as you pointed. I ll index it and will check the performance once again. Thanks — Muthukrishnan, Jun 17 '12 at 08:49
@Muthukrishnan Have you found a solution to your problem? If so, please mark one answer as correct. — RandomSeed, Jul 03 '12 at 00:52

Fast mysql query to randomly select N usernames

2 Answers2