Randomly select lines

Asked

Viewed 392 times

4

I am 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 which searches all users in random order, but can never select the same user, here’s my query:

SELECT * FROM tabela ORDER BY RAND() LIMIT 1

Then my friend criticizes saying:

"vc uses RAND(), this scans the entire table to return a single user to you"

What did he mean by that?

The same goes for:

"and never use a SELECT *"

Why?

Then he says that:

"and you probably don’t use indexes",

"you probably don’t optimize my.cnf"

What did he mean? I asked and he just ignored, I searched the internet but found nothing about.

mysql query random
  • 2

    This guy doesn’t seem to be your friend...

  • Worse than it is but he said I have to learn, so I’m pretty sure he gave me information "almost incorrect" just to turn one more morning researching.

1 answer

6


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

Time of experience does not mean quality.

"Do this" without explanation serves for nothing.

"vc uses RAND(), this scans the entire table to return a single user to you"

It is correct, it means that the performance will suffer, will make a reading in all table taking all the existing lines in it and then one of them will be selected to deliver you. People think you’ll read only one line, but that’s 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 don’t use indexes",

Indexes are fundamental to give performance. If created correctly, of course.

It is equal to a remissive index of a book, if you need to find an important word in the book you have two options: search for all words in the whole book or look at the index that is in alphabetical order and because of this you can get quickly to where you want, then the index tells you which page the information you want is on. A lot faster, right?

Wrong bred can bring more harm than good.

"you probably don’t 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 don’t have much experience handling it it will probably bring bad results. Trying to explain something useful will be absurdly long.

  • As to the * I had seen the question and the answers, as for the rest of your answer, I’m researching about indexes, I’ll try to use it, but if I use it I won’t need to use rand() correct? because I need one user at a time. at each request

  • 1

    If you want something random, you need to, if you want assurance that it doesn’t happen again, there are other ways to do it, depending on the volume of lines and how many you need to pick up, it doesn’t pay off. Only n]ao is ideal to take this way. Index will not solve anything there, unless index uses the RAND(), but it still won’t be useful because the order will always be the same if we don’t recreate the whole index, which wouldn’t make sense since the idea of the index is just not have to go through the entire table in the query, which is necessary in the construction.

  • Yes I realized it now, I’m using Indice but it wasn’t random anyway, I have 1000+ records in the table but each request can never select the same.

  • @Cool, is returning faster now with the indexes however, I’m using limit 1, I need every F5 given it return me another different record, never going by the same id or username, have idea how to proceed? $sql = "SELECT * FROM viperusers USE INDEX (index_username) WHERE vip < 1 LIMIT 1";

Browser other questions tagged mysql query random

You are not signed in. Login or sign up in order to post.