Select rows randomly

Question

Select rows randomly

Navigation

#1 by (8 votes)

6

I'm developing a very complex system, where in some cases I need to select only a few fields, a friend who has more experience in the area gave me some tips, but he did not explain why.

I have the following query that searches all users in random order, but I can never select the same user, here is my query :

SELECT * FROM tabela ORDER BY RAND() LIMIT 1

Then my friend criticizes saying:

"You use RAND (), this scans the entire table to return a user only"

What did he mean by that?

The same also says:

"and never use a SELECT *"

Why?

Soon after he also says that:

"and you probably do not use indexes",

"You probably do not optimize my.cnf"

What did he mean? I asked him and he just ignored it, I searched the internet but I did not find anything about it.

mysql random query

asked by anonymous 07.12.2017 / 03:56

1 answer

What is using namespace? How to redirect from non-www to www?

score 8 · Accepted Answer

Complexity without explanation is subjective. Without context information has no value.

Experience time does not mean quality.

"Do this" without explanation is useless.

"You use RAND (), this scans the entire table to return a user only"

It is correct, it means that the performance will suffer, it will make a reading in the whole table taking all the existing lines in her and there one of them will be selected to deliver to you. People think they will only read one line, but this is a mistake.

Probably the solution in the application is more appropriate. But there is a specific problem that is not the focus of the question.

"and never use a SELECT *"

Myth .

"and you probably do not use indexes",

Indices are critical to performance. If created correctly, of course.

It is the same as a book index, if you need to find an important word in the book you have two options: search for all words in the entire book or look at the alphabetical index and because of this you can get there fast where you want, there the index tells you which page is the information you want. Much faster, huh?

Created wrong can bring more harm than good.

"You probably do not optimize my.cnf"

This file has a number of parameters that can make the database server work better or worse according to what you need. For a few volumes it will make little difference. If you do not have much experience tinkering at it will probably give bad results. Trying to explain something useful will be absurdly long.