CODEWITHSUNDEEP
mysql
Raj
Raj

Reputation: 39

what is purpose of order in mysql index

I have table person in that there is field contact number i want to add indexing on contact number. In mysql what is the purpose on ordering while indexing some field.

INDEX contact_number (contact_number ASC))

Upvotes: 0

Views: 139

Answers (2)

Tarsis
Tarsis

Reputation: 740

Right now (mysql 5.7) the ASC/DESC is ignored by MySQL.

http://dev.mysql.com/doc/refman/5.7/en/create-index.html

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

If it is implemented some day it might be useful for composite indices: http://explainextended.com/2009/04/27/descending-indexes/

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

So far ASC and DESC in an index are simply ignored:

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

Source: http://dev.mysql.com/doc/refman/5.7/en/create-index.html

I guess the idea is that if you use a column very often in ORDER BY and always use the same direction, you can write the index accordingly, so as to speed up the typical queries:

create index idx_logdate on logtable (logdate desc);

Typical query:

select *
from logtable
where logdate > date_sub(now(), interval 1 month)
order by logdate desc;

As mentioned, DESC in the index has no effect now, but may have effect in a future release.

Upvotes: 2

Related Questions