MySQL Top Queries

Top MySQL queries: pagination with LIMIT and OFFSET, Nth highest value, find and delete duplicate rows, and count the tables in a database.

MySQL – Count the number of tables in the database

This query counts how many tables a database has. It reads the system table INFORMATION_SCHEMA.TABLES, which keeps the details of every table in the server.

SELECT COUNT(*) AS total_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydatabase';

Change mydatabase to the name of your database. If the database has 5 tables, the result is:

Tutorials Class - Output Window
total_tables
5

List the table names too

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydatabase'
ORDER BY TABLE_NAME;

Or, if you have already selected the database with USE, the short way is:

SHOW TABLES;
Note: INFORMATION_SCHEMA.TABLES also lists views. To count only real tables, add AND TABLE_TYPE = 'BASE TABLE' to the WHERE condition.

Related: MySQL SELECT and Aggregate functions.

MySQL Find Duplicate Records

Duplicate rows are a common problem, for example the same email saved two times. This query finds them. We use a users table where the email should be unique, but it is not.

Tutorials Class - Output Window
id name email
1 Ravi ravi@mail.com
2 Sita sita@mail.com
3 Amit ravi@mail.com
4 Neha neha@mail.com
5 Sita K sita@mail.com

Find the duplicate emails

SELECT email, COUNT(*) AS total
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
Tutorials Class - Output Window
email total
ravi@mail.com 2
sita@mail.com 2

Show the full duplicate rows

SELECT *
FROM users
WHERE email IN (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
)
ORDER BY email;

This shows rows 1, 3, 2 and 5, so you can see which records are repeated.

Note: Use the same query with more columns in GROUP BY (like name, email) when a duplicate means that all of them match.

Related: GROUP BY and HAVING and Subqueries.

MySQL Delete Duplicate Rows

This code removes duplicate rows and keeps the first one (the lowest id). We use the same users table from the find duplicates code.

Tutorials Class - Output Window
id name email
1 Ravi ravi@mail.com
2 Sita sita@mail.com
3 Amit ravi@mail.com
4 Neha neha@mail.com
5 Sita K sita@mail.com

Step 1: check what will be deleted

SELECT u1.*
FROM users u1
JOIN users u2
  ON u1.email = u2.email AND u1.id > u2.id;

This shows rows 3 and 5. These are the extra copies.

Step 2: delete them

DELETE u1
FROM users u1
JOIN users u2
  ON u1.email = u2.email AND u1.id > u2.id;

Now the table has only one row for each email:

Tutorials Class - Output Window
id name email
1 Ravi ravi@mail.com
2 Sita sita@mail.com
4 Neha neha@mail.com

Note: Always run the SELECT first and take a backup of the table. A DELETE cannot be undone after it is committed. To stop duplicates in future, add a UNIQUE constraint on the email column.

Related: MySQL DELETE, MySQL JOINS and Constraints.

MySQL Nth Highest Value

A common interview question: find the 2nd, 3rd or Nth highest marks. We use the students table with the marks 85, 92, 78, 88, 65 and 45.

Third highest marks

SELECT DISTINCT marks
FROM students
ORDER BY marks DESC
LIMIT 1 OFFSET 2;
Tutorials Class - Output Window
marks
85

For the Nth highest value, use OFFSET N-1. So the 3rd highest uses OFFSET 2, the 2nd highest uses OFFSET 1. DISTINCT makes sure that same marks are counted one time.

Get the student with that marks

SELECT name, marks
FROM students
WHERE marks = (
  SELECT DISTINCT marks
  FROM students
  ORDER BY marks DESC
  LIMIT 1 OFFSET 2
);
Tutorials Class - Output Window
name marks
Robin 85

Note: If there are fewer than N different values, the subquery returns nothing and the result is empty (NULL). Also, more than one student can have the same marks, and all of them are shown.

Related: MySQL LIMIT, DISTINCT and Subqueries.

MySQL Pagination Query with LIMIT and OFFSET

Pagination means showing the records in small pages, like 10 results on each page. In MySQL we do it with LIMIT and OFFSET.

Formula

OFFSET = (page_number - 1) * records_per_page

Example: page 2, 2 students on each page

SELECT name, city, marks
FROM students
ORDER BY marks DESC
LIMIT 2 OFFSET 2;

The students ordered by marks are Maria (92), Asha (88), Robin (85), John (78), Vikram (65) and Neha (45). Page 1 has the first two, so page 2 skips two rows and shows the next two:

Tutorials Class - Output Window
name city marks
Robin Delhi 85
John Pune 78

Short form

SELECT name, city, marks
FROM students
ORDER BY marks DESC
LIMIT 2, 2;   -- LIMIT offset, row_count
Note: Always use ORDER BY with pagination. Without it, the order of rows is not fixed and the same row can appear on two pages. In PHP, send the page number from the URL and calculate the offset with the formula, and use a prepared statement.

Related: MySQL LIMIT and ORDER BY.