Getting Random Record from Query

[dragonflyuk]

I've got quite a slow query, that I need to get a random record from, however this is proving painfully slow, can anyobdy suggest ways to speed up the following query.

SELECT * FROM people WHERE names like "%bert%" or names like "%john smith%".......... or names like "%eric%" ORDER BY RAND() LIMIT 0,1

bear in mind their could be 50+ names in the list hence the ............

The query does infact work, but is that slow that the PHP code that runs it times out waiting for the answer.

[indianrock9]

Hi there,
One common mistake a newbie makes is using *. From your query, I can see that you are using '*' to get all the fields. Do you really need all the fields in your query? To speed it up, you can remove * & in fact, specify which field(s) you want to retrieve. It will boost your query speed a lot.

So instead of using *, try something like this:

SELECT field1, field2,....field10 FROM people WHERE names like "%bert%" or names like "%john smith%".......... or names like "%eric%" ORDER BY RAND() LIMIT 0,1

That's just an example. You may modify it to suit your need. Let us know if this helps.

[dragonflyuk]

I actually only get 2 fields, but I'd paraphrased my query to shorten it.

[dragonflyuk]

I have found it is considerably quicker to do the query without the rand, then get the record count in php and pick a random record. Don't know if that helps anybody infuture.

[verruckt24]

Can you detail a bit on what you exactly need to do rather than showing us what to do it through, so we can show you a completely different way, if there exists, to do the same thing efficiently. Also use EXPLAIN in front of your query for MySQL to explain how your query is performing then analyze the output so you might get clues what actually is slowing your query down.