Performance at query time to database

Asked

Viewed 38 times

0

Suppose I have a table with 100,000 records, which in this example I will call dictionary, and will have a LIMIT of 1000 results.

SELECT FROM * dicionario

Let’s say that hypothetically I have already made a query and in this query I took the ID of 1000 results and turned into an array.

Taking into account the performance at the time of consultation, it would be more worth redoing the SELECT using the same condition in WHERE, or it would be more worthwhile to do the SELECT using in the WHERE the id that composes the array, something like:

$arrayId = 1, 20, 101, 345, 800, .... 7001.
SELECT FROM * dicionario WHERE id IN (". $arrayId .")

My doubt arose based on my current situation, where in fact I already have an array with the ID, because in this way I believe that it would spare me some SELECT, but I was worried about the performance and use of resources, such as memory.

php mysql performance

  • I agree with the reply of @msb but then I ask you: Is there really a need to redo the query? Suddenly the solution is to change the query a little and do it only once.

1 answer

2


It depends on many factors, one of them being memory, but for only 1000 records, if you don’t have many columns, the ideal is actually to just do the original query already searching the other fields that you want. 1000 records of few columns will hardly utilize an absurd memory.

If you really need to redo the query, the main factor about which is more efficient is knowing what your indexes are. A query for 1000 values in an indexed column (as is usually the case with the ID) is probably faster than any other alternative, but if the other fields used in your WHERE also be indexed, there is a small chance of being faster.

But fundamentally, unless this query is in a loop of many iterations, or this code and server is running on a slow/old/limited machine, the bigger chance is that your performance gain with one solution or another is negligible. ;-)

Browser other questions tagged php mysql performance

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