MySQL - ORDER BY en LIMIT performance
Query #1
SELECT
id,
company_name,
FROM
members
WHERE
boolean_deleted = 0
ORDER BY
company_name ASC
LIMIT
3045700, 25
Query #2
SELECT
m.id,
m.company_name
FROM
members AS m
INNER JOIN (SELECT id FROM members WHERE boolean_deleted = 0 ORDER BY company_name ASC LIMIT 3045700, 25) AS m_alias ON m_alias.id = m.id
Index
INDEX 'mijn_index' ('boolean_deleted', 'company_name', 'id')
Zonder Index | Met Index | |
---|---|---|
Query #1 | 60 seconden | 10 seconden |
Query #2 | 5 seconden | 3 seconden |