CODEWITHSUNDEEP
mysqlindexing
J. Wilson
J. Wilson

Reputation: 35

MySQL indexes best performance

I'm optimizing a mysql table for my high traffic website and I've got a question about mysql indexes. First some info:

The table has 273382 rows.

The query is:

SELECT table1.field1, table1.field2, table2.field2
FROM table1
LEFT JOIN table2 ON table1.idfield = table2.idfield
WHERE table1.field = 1 AND table1.field2 = 'yes'
ORDER BY table1.id DESC
LIMIT 0,25

I've got the following indexes:

index for table1.id - primary unique index
index for table1.idfield - index
index for table2.idfield - index
index for (table1.field,table1.field2) - index

With the above indexes query takes 1.5 to 2.5s to complete

EXPLAIN EXTENDED says it is using the primary key (table1.id), I've tried unique/index (table1.field,table1.field2,table1.id) with similar results (even though it is using this index).

During the index altering process (which takes around 1 min) the query becomes much much faster 0.009. Once the altering process is complete the query goes back to being slow at 1.5-2.5s (mostly under 2).

My question is: which index would work the best for this query and why the query is so much faster while altering the index.

Upvotes: 1

Views: 50

Answers (1)

Rick James
Rick James

Reputation: 142208

If more than about 20% of the table has table1.field = 1 AND table1.field2 = 'yes', then the Optimizer decides that it is cheaper to scan the table rather than bounce back and forth between even the optimal index (field, field2, id) and the data.

Please provide SHOW CREATE TABLE, SHOW TABLE STATUS, EXPLAIN SELECT ..., the size of RAM, and the value of innodb_buffer_pool_size if you want to discuss this further.

It smells like caching and I/O issues; the above info will help drill into that.

Upvotes: 1

Related Questions