Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 75 ยท Querying with MySQL

SELECT: MySQL Specifics

The parts of SELECT that are special in MySQL: backtick quoting, LIMIT and OFFSET, SQL_CALC_FOUND_ROWS history, STRAIGHT_JOIN, index hints and string comparison.

If you know standard SQL, most of SELECT in MySQL will feel familiar. This lesson is about the corners where MySQL has its own habits: how identifiers are quoted, how LIMIT works, a counting trick that has been deprecated, hints that steer the optimizer, how DISTINCT compares to GROUP BY, and the default rule that string comparison ignores case. Knowing these saves you from queries that run on one database and quietly return something different on another.

The examples that rely only on portable behavior run live below in SQLite. Features that exist only on a MySQL server, such as STRAIGHT_JOIN and index hints, are shown as read-only code. The lesson says which is which.

MySQL's SELECT habits

Backticks Quote names that clash with keywords.
LIMIT Page through rows with LIMIT and OFFSET.
Hints Nudge the optimizer when it chooses badly.
Case rules Text comparison ignores case by default.

Each one is small, and each one has bitten real projects.

1 / 2

Quoting identifiers with backticks

Identifiers (names of databases, tables and columns) normally need no quotes. If a name is a reserved word, contains a space or starts with a digit, you quote it with backticks, not double quotes:

SQL
SELECT `order`, `group`, `first name`
FROM `my table`;

In standard SQL the quote for identifiers is the double quote ("order"). MySQL uses double quotes for strings by default, so "hello" is a text value, not a column. The ANSI_QUOTES SQL mode switches MySQL to the standard rule (SQL Modes and Strictness). A backtick inside a name is doubled: `weirdname` ``.

Quoting everything works but makes queries noisy. Many teams simply avoid reserved words as names (order_no instead of order, grp instead of group) and keep backticks for the rare case, or for generated SQL where you cannot check every name.

SQLite also understands backticks, which makes this one runnable:

SQL Backticks around reserved words

Press Run (or Ctrl + Enter) to see the result here.

LIMIT and OFFSET

LIMIT returns at most a given number of rows, and OFFSET skips rows first. MySQL accepts two spellings:

SQL
SELECT id, title FROM books ORDER BY id LIMIT 3;            -- first 3 rows
SELECT id, title FROM books ORDER BY id LIMIT 3 OFFSET 6;   -- rows 7, 8, 9
SELECT id, title FROM books ORDER BY id LIMIT 6, 3;         -- same thing: offset, then count

The comma form puts the offset first, which surprises almost everyone. Prefer LIMIT count OFFSET offset, which reads like English and works on PostgreSQL and SQLite too.

Two rules matter more than the syntax:

  1. Always add ORDER BY when you use LIMIT. Without it, MySQL may return any rows, and which ones can change between runs or after an index is added.
  2. Make the order unique. If several rows tie on the sort column, add the primary key as a tie-breaker (ORDER BY created_at DESC, id DESC). Otherwise the same row can show up on two pages or on none.

LIMIT takes literal numbers or prepared-statement placeholders, not expressions. Large offsets are slow because MySQL still reads and throws away all skipped rows. Query Optimization Techniques shows the keyset alternative.

SQL Paging with a unique ordering ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The second and third queries return exactly the same rows. Because id breaks every tie in genre, the pages never overlap.

Counting rows for pagination: SQL_CALC_FOUND_ROWS

A pager needs two numbers: the rows for the current page and the total for "page 2 of 17". For years MySQL had a shortcut:

SQL
SELECT SQL_CALC_FOUND_ROWS id, title FROM books WHERE genre = 'Fiction' LIMIT 10;
SELECT FOUND_ROWS();

The first statement counted how many rows would have matched without the LIMIT, and FOUND_ROWS() returned it. It was convenient, but it forces MySQL to scan every matching row to count them, and the optimizer cannot stop at ten. Often it was slower than running the query and a separate count. Both SQL_CALC_FOUND_ROWS and FOUND_ROWS() have been deprecated since MySQL 8.0.17 and may disappear, so write the two queries instead:

SQL
SELECT id, title FROM books WHERE genre = 'Fiction' ORDER BY id LIMIT 10;
SELECT COUNT(*) FROM books WHERE genre = 'Fiction';

You will still meet the old form in legacy code, and it is worth recognizing. If the exact total is not needed, many applications show "next" and "previous" only, or cache an approximate count.

STRAIGHT_JOIN and index hints

Normally the optimizer chooses the order in which tables are joined and which index each one uses. When its statistics mislead it, MySQL offers ways to override the decision. These are read-only here, since SQLite has no equivalent.

STRAIGHT_JOIN forces the join order: the left table is always read first.

SQL
SELECT o.id, c.name
FROM customers AS c
STRAIGHT_JOIN orders AS o ON o.customer_id = c.id
WHERE c.city = 'Lisbon';

You can also write SELECT STRAIGHT_JOIN ... to apply the rule to all joins in the query. Index hints go after a table name and tell the optimizer what to do with indexes:

SQL
SELECT * FROM orders USE INDEX (idx_customer) WHERE customer_id = 7;     -- consider only this index
SELECT * FROM orders FORCE INDEX (idx_customer) WHERE customer_id = 7;   -- a table scan is "very expensive"
SELECT * FROM orders IGNORE INDEX (idx_ordered_on) WHERE customer_id = 7; -- never use this one

Hints are a last resort. They freeze today's guess into your SQL, and when data volumes change the forced plan can become the slow one. First try ANALYZE TABLE to refresh statistics, add a better index, or rewrite the query. If you do use a hint, leave a comment saying why and revisit it after upgrades. Newer MySQL versions also offer optimizer hints written as comments (/*+ JOIN_ORDER(c, o) */), covered in Query Optimization Techniques.

DISTINCT versus GROUP BY

SELECT DISTINCT removes duplicate rows. GROUP BY groups rows so you can aggregate, and used alone it also yields one row per group. For plain de-duplication the two return the same rows:

SQL DISTINCT and GROUP BY remove the same duplicates ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Pick DISTINCT when you only want unique values, because it says so plainly. Pick GROUP BY when you aggregate. Before MySQL 8.0, GROUP BY also sorted its output as a side effect, and old code sometimes relied on that. Since 8.0 the implicit sort is gone: add an explicit ORDER BY whenever the order matters. DISTINCT combined with ORDER BY on a column that is not in the select list is rejected, because the result would be ambiguous.

String comparison quirks

The biggest MySQL surprise for newcomers is that text comparison is case-insensitive by default. The default collation, utf8mb4_0900_ai_ci, is case-insensitive (ci) and accent-insensitive (ai), so all of these are true on a stock MySQL 8.x server:

SQL
SELECT 'Ada' = 'ada';        -- 1
SELECT 'resume' = 'rรฉsumรฉ';  -- 1 (accent-insensitive)
SELECT 'abc' LIKE 'ABC';     -- 1

That is friendly for searching, and it also means a unique index on email treats [email protected] and [email protected] as duplicates. To get a case-sensitive comparison you pick a _bin or _cs collation for the column, or override it for one comparison with COLLATE or the BINARY operator. Character Sets and Collations goes deeper.

SQLite compares text exactly (BINARY) unless you ask otherwise. That lets you see both sides of the rule live:

SQL Case-sensitive versus case-insensitive comparison

Press Run (or Ctrl + Enter) to see the result here.

The first query returns 0 and 1, and the two counts are 1 and 3. A default MySQL server would return 3 for both counts. Remember this when you test queries on SQLite and deploy on MySQL.

Other string quirks worth knowing:

  • Trailing spaces may or may not count, depending on the collation's pad attribute (String Types).
  • LIKE follows the column's collation, so LIKE 'a%' also matches A... on a _ci column.
  • Comparing a string to a number converts the string to a number, so '12abc' = 12 is true with a warning. NULL Handling and Comparison Rules covers this trap.
  • ORDER BY sorts text by the collation too, so with an accent-insensitive collation e and รฉ sort together.

What ran live here

Backticks, LIMIT/OFFSET, DISTINCT versus GROUP BY, and the case comparison ran in SQLite. SQL_CALC_FOUND_ROWS, STRAIGHT_JOIN, index hints and the default MySQL collation behavior are server features and are shown as read-only code and described results.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In MySQL's default mode, which character quotes an identifier such as a column named group?

    MySQL quotes identifiers with backticks. Double quotes make a string literal unless the ANSI_QUOTES SQL mode is on.
  2. 2. What does SELECT id FROM books LIMIT 5, 10 return?

    In the comma form the offset comes first, then the count. LIMIT 10 OFFSET 5 says the same thing more clearly.
  3. 3. Why should you replace SQL_CALC_FOUND_ROWS in new code?

    Since 8.0.17 it is deprecated. It forces a scan of all matching rows, so a normal query plus a COUNT(*) is usually as fast or faster.
  4. 4. On a default MySQL 8 server, what is 'Ada' = 'ada'?

    The default collation utf8mb4_0900_ai_ci ignores case and accents, so the two strings are equal.
  5. 5. When do you add an index hint such as FORCE INDEX?

    Hints freeze one plan into your SQL and can become the slow choice as data grows, so try statistics, indexes and rewrites first.

Summary

  • Quote odd identifiers with backticks. Double quotes are strings unless ANSI_QUOTES is set.
  • Use LIMIT count OFFSET offset, always with a unique ORDER BY. The comma form puts the offset first.
  • SQL_CALC_FOUND_ROWS and FOUND_ROWS() are deprecated since 8.0.17; run a separate COUNT(*).
  • STRAIGHT_JOIN and USE/FORCE/IGNORE INDEX override the optimizer. Use them last and comment them.
  • DISTINCT and GROUP BY both de-duplicate. Since 8.0, GROUP BY no longer sorts for you.
  • Default text comparison in MySQL is case- and accent-insensitive, unlike SQLite.

Next: GROUP BY, HAVING and Aggregates in MySQL.