Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does MySQL use index for sorting?

Tags:

sql

sorting

mysql

Does a sort use a MySQL index if there is an index on the sorting column? Also what other things is the index used for?

What difference does it make in a combined and separate indexes of the columns?

like image 334
Anush Avatar asked Jul 28 '11 12:07

Anush


People also ask

Do you need index for sorting?

Yes, index will help you, when using ORDER BY. Because INDEX is a sorted data structure, so the request will be executed faster.

How does MySQL sort?

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

What is the use of index in MySQL?

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs.

Is indexing the same as sorting?

Both indexing and sorting arrange rows in a specified order. However, indexing changes only the logical order and leaves the natural order intact, while sorting changes the natural order of the rows in the new table. Processing operations.


1 Answers

Yes, MySQL uses your index to sort the information when the order is by the sorted column.

Also, if you have indexes in all columns that you have added to the SELECT clause, MySQL will not load the data from the table itself, but from the index (which is faster).

The difference between combined and separate indexes is that MySQL can not use more than one index per query, so, if your query filters by many columns and you would like to have it correctly indexed you will need to create a combined index of all columns.

But before adding lots of indexes to your tables, remember that each index makes insert/update/delete operations go slower.

I would also highly recommend the High Performance MySQL book by O'Reilly that will cover in depth all of these issues and a lot of other hints you need to know to really be able to use MySQL to the limit.

like image 194
Maurício Linhares Avatar answered Oct 17 '22 08:10

Maurício Linhares