Geek Answers Handbook

Any SQL database: when is it better to retrieve the whole table instead of querying for specific rows?

I have a table that contains, possibly, from 10 to 100 thousand rows, and I need different sets of up to 1 or 2 thousand rows, but often they are much smaller. I want these queries to be as fast as possible, and I would like to know which approach is usually smarter:

  • Always query exactly the rows I need with the WHERE clause, which is different all the time.
  • Download the entire table into the in-memory cache inside my application and search there, regularly synchronizing the cache
  • Always query the entire table (without the WHERE clause), allow the SQL server to process the cache (it is always the same query so that it can cache the result) and filter the output as necessary

I would like to be an agnostic of a certain DB mechanism at the moment.

+3
source share
9 answers

I strongly believe that option 1 should be preferred in the initial situation. When you run into performance issues, you can see how you could optimize it with caching. (Pre-optimization is the root of all evil, Dijkstra once said).

Also, remember that if you select option 3, you will also send the full contents of the table over the network. It also affects performance.

+3
source

10K 100K , 1 . < 1K, , , , . 1.

, , , , , DB .

, , .

+7

, , , , , . , , , - , , " " ". , " " , , .

+4

, , , , . , , - , .

+2

, # 2. - , , , .

# 3 : " ", , . # 2, , # 2, # 2.

. . .

+2

-, 1, - , WHERE (.. WHERE , WHERE id = 3 or id = 4 or id = 32 or ...).

+1

- ? , SQL- , . , "- " . , , , .

0

, SQL- , , ( ).

, " " "". , (), , .

... , , ?

0

:

SELECT * FROM users;

mysql : , , - , .

SELECT id, email, password FROM users;

mysql , .

about limits: it is always best to request the number of rows you need, no more, no less. more data means more time for it to work.

-1
source

More articles:


All Articles