Is it redundant to use LIMIT in a QUERY whose ID is the primary key?

Asked

Viewed 144 times

9

My question is if using LIMIT there will be some performance gain in the QUERY.

Example: 

SELECT descricao FROM produto WHERE id = 9999 LIMIT 1

Has better performance than?

SELECT descricao FROM produto WHERE id = 9999

As perhaps it would be generic not specify any tool, I would like replies refente to the best known SQlite, SQL Server, PostgreSQL, MySQL, Oracle .

It doesn’t necessarily have to be all of them, but I prefer SQlite since it runs on mobile devices where performance/optimization is more complex.

mysql sql sql-server postgresql sqlite

  • Yeah, because it can only return one given line that is the primary key.

  • I understood your doubt, I believe that in any engine the difference is minimal (if there is difference) gain and each engine probably works in a way. I believe that there is no way to answer something specifically for SQL syntax, but only by engine, for example in mysql the engines myIsam and innoDB work in different ways.

  • I will edit to specify the most used.

  • Note that Mysql has different engine types.

2 answers

5


If the query uses the primary key as a criterion, yes, it is redundant.

If the query uses as a criterion an index that is not the primary key and whose value is not unique, it may be, but not always. For example, if the value is at the end of the index (worst case), have LIMIT or it’s almost the same thing in terms of performance.

Now, if the query uses a non-indexed column, it may not be, essentially if the query does not return results. It is worth seeing how the Sqlite optimizer works. There is reference to LIMIT when it comes to sub-consultations. In any case, if there are no results, there will be the TABLE SCAN anyway.

SQL Server and Oracle do not have LIMIT:

  • SQL Server uses TOP:

    SELECT TOP 1 descricao FROM produto WHERE id = 9999

  • Oracle uses ROWNUM:

    SELECT descricao FROM produto WHERE id = 9999 AND ROWNUM <= 1;

1

I never stopped to think about it but I think the best way to find out if it’s faster or not is to use a time measurement feature when running a query.

See an example below, in the case using sql server:

USE AdventureWorks2012;
GO       
SET STATISTICS TIME ON;
GO
SELECT ProductID, StartDate, EndDate, StandardCost 
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
GO
SET STATISTICS TIME OFF;
GO

For more information see: https://msdn.microsoft.com/pt-br/library/ms190287.aspx

Browser other questions tagged mysql sql sql-server postgresql sqlite

You are not signed in. Login or sign up in order to post.