Limit 1 line in queries with primary key queries

Question

One of the best practices on optimization of Mysql queries, dictated in an internet source, says that it is important to limit as 1 the queries that already display only one line.

For example, if I’m going to change a user’s registration, my current query looks like this:

select campo1,campo2 from tabela where id=x

In this case I would pass to:

select campo1,campo2 from tabela where id=x limit 1

In this case, considering that id is already the primary key, one should make this change to optimize query or in this case the limitation is unnecessary?

Answer 1

whereas the countryside id is defined as primary key, no need to put limit 1, because there will never be more than 1 record.

Answer 2

The clause is unnecessary limit.

Mysql provides a limit clause which is used to specify the number of records to be returned.

The LIMIT clause makes it easier to encode multiple page results or pagination with SQL, and is very useful in large tables. Getting back a large number of records can impact performance.

Suppose we want to select all 1-30 records (inclusive) from a table called "Requests". The SQL query then looks like this:

$sql = "SELECT * FROM Orders LIMIT 30";

When the above SQL query runs, it will return the first 30 records.

And if we want to select records 16-25 (inclusive)?

Mysql also provides a way to handle this: Using offset.

The SQL query below says "return only 10 records, start at record 16 (offset 15)":

$sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15";

You can also use a shorter syntax to achieve the same result:

$sql = "SELECT * FROM Orders LIMIT 15, 10";

Note that the numbers are reversed when you use a comma. If you only need a specified number of rows from a result set, use the LIMIT clause in the query.

But for a case with key use primary the use of this clause is totally unnecessary since the key already has search index.