0

I have a SQL-query like

SELECT somefields
FROM T1  
INNER JOIN T2 ON T1.x=T2.x
LEFT JOIN (SELECT somefields FROM T3 WHERE y=123 GROUP BY z)X ON T1.a=X.a
WHERE t1.m=4 AND t1.n>0 AND t1.date1 >CURRENT_TIMESTAMP 
ORDER BY t1.date1 DESC 
LIMIT 25;

The query takes about 1.5 seconds sometimes, eg when t1.m=4 has many rows, and the result is empty. So far so good. But if I just eliminate the LIMIT 25 the execution time of the same query is about 0.05 seconds.

Can someone explain to me why this is? I would think that LIMIT 25 would at least be as fast as the query without LIMIT 25.

Improve this question
5
  • 4
    Use explain analyze and post the results with and without the limit, please. Also please add the relevant parts of your table definitions and indexes. Commented Mar 13, 2024 at 17:59
  • 4
    limit can cause it to choose a different index that it thinks will be faster (because it could stop reading partway through) and have it not actually be. Commented Mar 13, 2024 at 18:36
  • 1
    I had exactly the same (stackoverflow.com/questions/61972759/…) and I can vow that it is probably using another index and the speed difference can probably be solved with an extra index, even if this doesn't make sense.. The two remarks above combined should make you able to analyse which index you could add/change. Commented Mar 13, 2024 at 19:16
  • I now know, the reason if someone is interested here: if I use LIMIT the system uses the index of the Date-Field t1.date1. If not it uses the better index y which is an integer. Thanks for your help Commented Mar 14, 2024 at 15:32
  • That subquery seems to be too "simplified". Please provide the full query, plus EXPLAIN SELECT ... with and without LIMIT. Also SHOW CREATE TABLE for each table. Commented Mar 14, 2024 at 20:43
Related questions
3006
Why is printing "B" dramatically slower than printing "#"?
3215
Should I use the datetime or timestamp data type in MySQL?
1656
Replacing a 32-bit loop counter with 64-bit introduces crazy performance deviations with _mm_popcnt_u64 on Intel CPUs
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.