A
A
Arris2016-01-07 12:41:23
PHP
Arris, 2016-01-07 12:41:23

SELECT LIMIT OFFSET vs SELECT foo WHERE bar in () - which is faster?

Given a MySQL table with the following structure:

id int,
data1 varchar(300), data2 varchar(300), data3 varchar(300)... dataN varchar(300),
factor1 int,
factor2 int,
factor3 varchar(30),
factor4 tinyint(4),
factor5 datatime
...
factorN sometype

Index by id and factor* . MyISAM
table type . mysql-server version is 5.6.19-67.0-log . The table has tens of millions of rows. To it periodically (quite often) a request of the form is made:
SELECT * FROM table WHERE (совокупность факторов) LIMIT 100 OFFSET N

Of course, not all fields are selected with an asterisk, the necessary ones are listed, but this is not the mission.
As long as OFFSET is small, everything is fine.
As soon as a type request is made, the
LIMIT 100 OFFSET 750000
situation deteriorates.
Actually, this is a pagination backend with selection according to several criteria.
For a long time I figured out how to cache it and speed it up and came up with the following idea:
1. First, we select all possible string IDs that meet the required criteria:
SELECT id FROM table WHERE (совокупность критериев)

2. Then we divide the sequence of IDs into chains of 100 numbers.
3. Chains (strings) are cached (taking into account time stamps to check for obsolescence and a combination of request factors).
4. Then we select the chain of IDs we need
5. And we pull the necessary 100 lines from the database with the following request:
SELECT * FROM table WHERE id in (set)
It is clear that steps 1..3 are performed if the sequence we need is not in the cache at step 4 (or it is outdated).
What is the question:
how much faster/slower is SELECT FROM TABLE WHERE id in set query SELECT FROM TABLE LIMIT OFFSET ?
Unfortunately, I don’t have access to a real database and I have to guess on the coffee grounds.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
A
Andrey Pavlenko, 2016-01-07
@Arris

Yes, the second method can help, but only if the first request is cached.
This will really be faster, because the selection goes by the primary key without additional conditions that require iteration, and then sorting and partial selection using LIMIT / OFFSET

E
Eugene, 2014-06-25
@jackroll

typedef unsigned int un;
Works. And if you tried to become a preprocessor yourself and substitute your macros, you would see what happens in the end unsigned int; a = 1, b = 2;
. Just remove the ; at the end of the macro and you will be happy.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question