Performance when querying the database

Question

Performance when querying the database

Navigation

#1 by (2 votes)

0

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

SELECT FROM * dicionario

Let's say hypothetically I've already done a query and in this query I got the 1000 results ID and turned it into an array.

Considering performance at query time, it would be worth redoing SELECT using the same condition in WHERE , or it would be worth doing SELECT using WHERE the id that composes or array, something like:

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

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

php mysql desempenho

asked by anonymous 31.01.2017 / 04:17

1 answer

TextView is not displaying decimal numbers of a variable of type long! How to get, by reference, string values using PHP

score 2 · Accepted Answer

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

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

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