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.
Rate this post
5 of 5 based on 1023 votesComments