Fco Fco - 1 day ago 4
MySQL Question

Is necessary index a column of a table if is the last field on the query - MySQL?

Suppose that i've a table (t1) with 3 columns (c1,c2,c3), and i added an index like this:

ALTER TABLE `t1` ADD INDEX `IDX_c1_c2` USING BTREE (`c1`, `c2`);


I know that if i make this query use the index and works perfect:

SELECT * FROM `t1` where c1 = "foo" AND c2 = "bar"


But, what about this query?:

SELECT * FROM `t1` where c1 = "foo" AND c2 = "bar" AND c3 = "foobar"


The MySQL engine will use the index in order to improve the query or will ignore the index? i mean, the whole query must match with an index in order to use the index or may be i can take advantage of the index for the first two columns of the query.

[UPDATE] - The last query, in fact, look like this (the date is just an example):

SELECT * FROM `t1` where c1 = "foo" AND c2 = "bar" AND c3 > "2016-11-26 07:37:47"

Answer

Just to make things simple your exisiting index will still be used , however third column will be resolved independently. The Query Processor will generate some plans and the best plan will be accepted by the engine to proceed . Still making third column an index as well will definitely boast the performance .

Suggestion If you are using equality conditions in querying go for hash based indexing rather than Btree

Comments