Limit 1 row in queries with primary key queries

Question

Limit 1 row in queries with primary key queries

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

One of the best practices for optimizing MySQL queries, dictated by an internet source, says that it is important to limit queries that already display only one row to one.

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

select campo1,campo2 from tabela where id=x

In this case you would go to:

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

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

mysql sql query

asked by anonymous 26.08.2016 / 00:17

2 answers

1

Since the id field is set as the primary key, there is no need to set limit 1 , since there will never be more than 1 record.

26.08.2016 / 01:11

Commit files disappeared Click on a button and make another

score 1 · Accepted Answer

The limit clause is unnecessary.

MySQL provides a limit clause that is used to specify the number of records to be returned.

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

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

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

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

What 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 using primary key the use of this clause is totally unnecessary since the key already has a search index.