All geek questions in one place

How to speed up this SQL index query?

Given the following SQL table:

Employee ( ssn , name, department, manager, salary)

You will find that the next query is much slower than expected. There is an index on salary , and you have confirmed that the query plan uses it.

 SELECT * FROM Employee WHERE salary = 48000

Please provide a possible reason why this request is slower than expected and provide a customization solution that addresses this reason.

I have two ideas why this query is slower than expected. First, we try SELECT * instead of SELECT Employee.salary , which slows down the query, since we must search all the columns, not one at a time. Another idea is that the salary index is salary clustered, and we want to use a clustered index, because the company can be very large, and it would be advisable to organize the table in the salary field.

Will either of these two solutions speed up this query? That is, either change SELECT * to SELECT Employee.salary , or explicitly set the index to salary for clustering?

+5
source share
2 answers

What indexes do you have?

Is it really "slow"? What evidence do you have?

Comments on "SELECT * instead of SELECT Employee.salary" -

  • * is a bad form because tomorrow you can add a column, thereby breaking any code that expects a certain number of columns in a certain order.
  • Work with * in comparison with salary does not occur until a number of lines are found.
  • Finding a string (s) is expensive.
  • On the other hand, if you have INDEX(salary) and look only at salary , then the index will "cover". This means that “data” (other columns) does not need to be retrieved. Therefore, faster. But that is probably not what your teacher has told you.

Comments on "the salary index is nonclustered, and we want to use the clustered index" -

  • In MySQL (not necessarily in other RDBMSs), InnoDB has exactly one PRIMARY KEY , and it is always UNIQUE and "clustered".
  • That is, “grouped” means “unique”, which seems inappropriate for “paycheck”.
  • In InnoDB, the "secondary key" implicitly includes PK columns (columns) ( ssn ?) With which it can go into the data.

"Confirmed Query Plan" - Did you hear about EXPLAIN SELECT ... ?

Additional tips on creating the optimal index for a given SELECT .

+4
source

I will try to be as simple as I can be

You cannot just make a salary a clustered index , unless you make it unique or primary, which is kind of stupid and pointless because two people can have the same salary.

A MYSQL table can have only one clustered index for each table. By default, the database selects the primary key for the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL finds the first UNIQUE index, where all key columns are NOT NULL and InnoDB uses it as a clustered index.

To speed up your query, I have a few suggestions , go for secondary indexes,

If you want to find the salary by the direct value , then hash indexes are the best option if MYSQL already supports this.

If you want to find a value using more than, less than, or some range, it is better to use B-tree indexes.

The first option is faster than the second, but limited only by the equality operator.

Hope this helps.

0
source

Source: https://habr.com/ru/post/1267056/

More articles:


All Articles