Mysql index explained

Question

FROM: https://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

Does the order of how the columns are used matter?

Example: id, first name, last name, middle name, address

index (first name, last name, middle name)

Indexes:

first name
first name, last name
first name, last name, middle name

When doing the query does the order of the WHERE matter?

EXAMPLE:

WHERE first_name = 'Chris' and last_name = 'Smith'

VS

WHERE last_name='Smith' and first_name = 'Chris'

Are these basically the same?

matino · Accepted Answer · 2015-04-04 15:02:17Z

Are these basically the same?

Short answer - yes, they are the same.

The order on the where clause doesn't matter, what matters is whether the columns used match with index columns in the left to right order. To give an example:
Index:

(first name, last name, middle name)

Where clause (order doesn't matter):

first name, last name - covered by the index
last name, middle name - not covered, because doesn't contain first name column. This is because the index is stored as a B-Tree and if you don't provide the first column you won't hit it.

http://use-the-index-luke.com/ has a lot of information about indexes.

There is no silver bullet, every qeury needs to be analysed separatly.

Collectives™ on Stack Overflow

Mysql index explained

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related