John Davidson

Message:

This may be so easy for an experienced user, but I'm no able to fully understand what indexes are needed for this simple case.

I have a front-end table that can be sorted by any column besides some filters that can be applied for searching purposes:

id | username | email | name | phone | email_verified_at

The rows shown is a result of joining 2 tables (with a one-to-one relationship):

TABLE USERS



id | autoincrement

name | string

username | string | unique

email | string | unique

email_verified_at | timestamp | nullable



INDEXES

primary(id)

unique(name,id)

unique(username)

unique(email)

unique(email_verified_at,email)

TABLE PROFILES



id | autoincrement

user_id | FK(users)

phone | string



INDEXES

primary(id)

index(user_id)

unique(phone,user_id)

The most basic query, ordered by user id looks like

select

  `users`.`id` as `id`,

  `users`.`name` as `name`,

  `users`.`username` as `username`,

  `users`.`email` as `email`,

  `users`.`email_verified_at` as `email_verified_at`,

  `profiles`.`phone` as `phone`

from

  `users`

  left join `profiles` on `users`.`id` = `profiles`.`user_id`

order by

  `id` asc

limit

  11

Ordering by id, name, username, email, or email_verified_at performs a FULL INDEX SCAN in users table which I assume is correct.

But the trouble (or not) comes when I try to order by phone:

select

  `users`.`id` as `id`,

  `users`.`name` as `name`,

  `users`.`username` as `username`,

  `users`.`email` as `email`,

  `users`.`email_verified_at` as `email_verified_at`,

  `profiles`.`phone` as `phone`

from

  `users`

  left join `profiles` on `users`.`id` = `profiles`.`user_id`

order by

  `phone` asc

limit

  11

The first thing I see is that it's doing a FULL TABLE SCAN in users table.
In order to avoid that I need to create a UNIQUE index for users table containing:

id, username, email, name

Therefore the query performs a FULL INDEX SCAN over users table.
Despite that, I'm seeing that query cost is too high compared to sorting by users table fields.

Is that OK? Am I missing something here? I'm not sure if this is an expected result or it can be improved.

Thanks in advance

---

My Answer:
In this case, creating a unique index on the users table with the columns id, username, email, and name should help improve the performance of the query when ordering by the phone column. This index will allow MySQL to efficiently retrieve the necessary data without having to perform a full table scan.

However, it's important to note that the query cost may still be higher compared to sorting by columns that already have indexes in place. This is because ordering by a column that is not indexed may require additional processing and sorting operations.

If you are still experiencing high query costs, you may want to consider optimizing the query further or reviewing the overall database schema and indexing strategy to ensure optimal performance. Additionally, you can use tools like EXPLAIN to analyze the query execution plan and identify any potential bottlenecks.