There is a table users . It has 500,000 users. It is necessary to optimize the query selection of users with regard to sorting by first_name , last_name , etc.

My query looks like this:

 SELECT `user_id`, `first_name`, `last_name`, `city_name`, `password`, `post_index`, `birthdate`, `salary` FROM `users` ORDER BY last_name ASC LIMIT 0, 300

But the problem is that it runs for 1 minute and 8 seconds on my computer.

Tell me what you can do.

  • 3
    indexes to these fields added? - mountpoint
  • for this query, an index of last_name is sufficient. - Yura Ivanov
  • Added an index like this CREATE INDEX index_first_name ON users (first_name (26)); But it did not help. - xdoctordog
  • 2
    @xdoctordog. Do you sort on last_name? Why do you need an index on first_name? it will not be used. for this query, an index of last_name is sufficient. - Yura Ivanov

3 answers 3

You can do:

  1. Add indexes for first_name, last_name

  2. To make a composite index ( user_id, first_name, last_name, city_name, password, post_index, birthdate, salary ) will be covering. Funny for such a number of fields

  3. Add a composite index uid_lname(user_id, last_name) and split the query into 2 queries:

    • SELECT user_id FROM users USE INDEX(uid_lname) ORDER BY last_name ASC LIMIT 0, 300 - in this case the index will be covering and will be used for sorting
    • S ELECT user_id, first_name, last_name, city_name, password, post_index, birthdate, salary FROM users WHERE user_id IN (result of the 1st query)
  • Did not help. Added an index like this: CREATE INDEX index_first_name ON users (first_name (26)); And I execute such SELECT query user_id, first_name, last_name, city_name, password , post_index, birthdate, salary FROM users USE INDEX (index_first_name) ORDER BY first_name LIMIT 0, 300 And it runs at about the same time as without indices. - xdoctordog
  • Try this combination CREATE INDEX index_uid_first_name ON users (user_id, first_name (10)); SELECT user_id, first_name, last_name, city_name, password , post_index, birthdate, salary FROM users USE INDEX (index_uid_first_name) ORDER BY first_name LIMIT 0, 300 The result is the same. - xdoctordog
  • one
    What is the point of creating an index for a column part? What does EXPLAIN say? - renegator
  • ... and also not in that field. - Yura Ivanov
  • first_name (26) - 26 is the maximum record length in the field? In 1 case, you can without use index. It is strange that the index on first_name did not speed up the query. Make explain. In case 2, your query simply will not use the index. - evz

If the data in the table does not change very often, then you can pre-sort the data by the desired field.

 ALTER TABLE users ORDER BY last_name ASC;

In this case, ORDER BY is no longer needed. But for the relevance of sorting, this query should be performed regularly, for example, by krone once a day.

    In order not to roll all the data at once, first select id and lastname . Sort by lastname , and the remaining data - attach via JOIN by id . It should be much faster.