CODEWITHSUNDEEP
mysqlperformanceindexingcomposite-key
Jarno
Jarno

Reputation: 153

MySQL not using all key parts of composite index

I have an InnoDB table containing ~1.7m rows in MySQL 5.7.19. I want to optimize the following query:

select * from `table` where `col1` = 'x' and `col2` = 123 and `col3` = 'z'

where the columns are defined as (all using utf8mb4 encoding):

col1 varchar(255) null
col2 varchar(255) not null
col3 varchar(255) not null

and an index over all columns:

key (
  col1, -- Cardinality: 40
  col2, -- Cardinality: 472810
  col3  -- Cardinality: 403767
)

I'd expect the query to run fast, because MySQL should be able to fully utilise the index. Now, the performance is not so good and it starts to make sense when I run the query with explain format=json:

"used_key_parts": [
   "col1"
],
"key_length": "1022"

Only the first column of the composite index is utilised. The constraints on col2 and col3 are evaluated using a table scan.

Could anyone explain to me what is going on here and give advice on how to improve on this?

I currently solved it by merging columns into one column, by introducing and indexing a stored generated column that concatenates col1 and col2. However, I can not use this for queries that want to use the IN() operator on those columns.

Thank you in advance!

Jarno

Upvotes: 1

Views: 1069

Answers (3)

Rick James
Rick James

Reputation: 142443

`col2` = 123

is your undoing. When comparing a VARCHAR to an integer constant, the varchar is converted to numeric. This requires converting all relevant rows on the fly.

After all, col2 could contain "0123" or "123.0" or "1.23e2". As strings those are quite different; indexes on varchars are sorted according to string attributes (COLLATION).

The likely solution is to change to add quotes:

`col2` = "123"

The order of the ANDs in the WHERE does not matter.

The order of the columns in the INDEX does matter. Having an INDEX with col1 and col3 first, in either order, would be better.

Cardinality does matter when comparing INDEX(col1) versus INDEX(col3).

Cardinality does not matter for the parts of the index actually used, as when comparing INDEX(col1, col3) versus INDEX(col3, col1).

In contrast, WHERE int_col = "123" will convert "123" to 123 be able to use an index.

Upvotes: 3

Jarno
Jarno

Reputation: 153

Thank you all for your reply. After playing around with the column order in the index (related to cardinality) I noticed that the application was comparing with an integer value on col2 (which is a varchar column). Casting the value to a string solved the performance problem.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

try build you composite using high cardinality columns

eg: 

col2,col3,col1

and do the fact you are using AND operator in where don't need the () aroudn the condition 

  select * from `table` where `col1` = 'x' and `col2` = 'y' and `col3` = 'z'

and last you could impose the index with FORCE

Upvotes: 1

Related Questions