CODEWITHSUNDEEP
mysqlindexing
cheng ye
cheng ye

Reputation: 1

why this query in MySQL do not use index?

As I am learning how to make index perform better in MySQL,I saw an example :

if you create join index like this : (col1,col2) ;and your query like this : select col3 from table where col1 = 'col1' order by col2. the engine will not use join index to sort your data.you should sort like : select col3 from table where col1 = 'col1' order by col1,col2;

But I am confuse about the above example: if I create (col1,col2) ,it mean that the B + tree will first order index by col1,and when col1 is same it consider col2.

so in this query we have col1 = 'col1',so I think the engine just get data by join index order then the data will order by col2 naturally.

Am I wrong or this example wrong? also I hope someone could recomment an article about how to use index better,rather than teach what is index and how to add an index,

Upvotes: 0

Views: 52

Answers (1)

Rick James
Rick James

Reputation: 142538

This works for me:

select col3 from table
    where col1 = 'col1'
    order by col2

with INDEX(col1, col2) (which is called a "composite index")

I agree with you, not the quote.

To figure out what went wrong, please provide

SHOW CREATE TABLE `table` -- so we can see exact datatypes and index
SHOW TABLE STATUS LIKE 'table'; -- to see sizes
EXPLAIN SELECT ...;  -- to see what the Optimizer decide was best
EXPLAIN FORMAT=JSON select ...; -- more details

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';  -- to get still more details.

and a link to the quote you provided.

Upvotes: 1

Related Questions