0

As titled, if there is only one record in table, are the two querys identical:

select * from table limit 10;

select * from table limit 100000;

thanks.

Sheldon Wei
  • 1,198
  • 16
  • 31
  • I have never seen the source code for `LIMIT`, but yes, the two queries should behave identically, because in the final limiting step, there is only one record in both cases. And, from the point of view of bandwidth of data, the result sets are also the same size in both cases. – Tim Biegeleisen May 17 '19 at 05:27
  • If the query result estimator estimates the result size to one (which he definitely will in this case) than the limit will be practically ignored during the query optimization. He will consider limit during the query processing, however, since the result is one row, then it again makes no difference. – Radim Bača May 17 '19 at 06:14
  • Note that if there were more than 100000 records then we couldn't guarantee that query 1 was a subset of query 2. Without an ORDER BY clause, LIMIT is of limited appeal. – Strawberry May 17 '19 at 06:49

5 Answers5

0

I dont know the implementation of LIMIT in mysql but based on below links, i think it will not difference if the query returns less than LIMIT.

Does adding 'LIMIT 1' to MySQL queries make them faster when you know there will only be 1 result?

Santosh
  • 874
  • 11
  • 21
0

putting limit in this case will not effect your end result. But you should be aware that putting limit in a query does impact the performance of a query.

tatasisi
  • 39
  • 8
0

I have not researched about mysql, but in context with Postgresql, there is a difference, but not much to be considered. You can see the difference by prefixing your query with EXPLAIN ANALYZE, it will output QUERY PLAN.

=# EXPLAIN ANALYZE select * from table limit 10;
=# EXPLAIN ANALYZE select * from table limit 100000;

This will give you parameters like

  • Actual time
  • Planning time
  • Execution time
  • and few more...

There might be something available for mysql as well. I hope this will help.

Jyoti Sharma
  • 21
  • 3
-1

It won't affect your end result but if you know you only have one record then it'd be better not to use limit because if you set limit to 100 it'll reserve extra 99 pointers in memory.

nazifa rashid
  • 1,469
  • 1
  • 9
  • 20
-4

Do not put limit because it limited the records appear on your screen so try to avoid if in future you want to get more records it will make problem for you. Also if you want query faster and more efficient use limit number you want to show in your page as per your requirement

Hamza Mazhar
  • 21
  • 1
  • 7
  • On the contrary you should make it a habit of using LIMIT, once you get enough records the receiving end will not be able to handle the amount of data being sent. For the first 100 records you have LIMIT 0, 100 and to get the next "page" you simply offset LIMIT 100,100 adjust the page size to match your needs – Iesus Sonesson May 17 '19 at 05:34