fguillen fguillen - 1 month ago 8
MySQL Question

MySQL, combine several queries in one

I have a table like this

field_1 field_2 field_3
------- ------- -------
3 1 5
2 1 1
1 2 1
1 4 1


And I have these 3 queries:

select count(*) as field_1_is_2 from my_table where field_1 = 2;
select count(*) as field_2_is_4 from my_table where field_2 = 4;
select count(*) as field_3_is_5 from my_table where field_3 = 5;
select count(*) as fields_combined from my_table where field_3 != 2 and field_2 != 2;


How can I combine the above queries in one query?

One option could be:

select
sum(if(field_1 = 2, 1, 0)) as field_1_is_2,
sum(if(field_2 = 4, 1, 0)) as field_2_is_5,
sum(if(field_3 = 5, 1, 0)) as field_3_is_5,
sum(if(field_3 != 2 and field_2 != 2, 1, 0)) as fields_combined
from my_table;


But the table is very big and I want to use a proper use of indexes and this approach is not making use of them.

This is the table descripton:

CREATE TABLE `my_table` (
`field_1` int(11) DEFAULT NULL,
`field_2` int(11) DEFAULT NULL,
`field_3` int(11) DEFAULT NULL,
KEY `index_field_1` (`field_1`),
KEY `index_field_2` (`field_2`),
KEY `index_field_3` (`field_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


So, the question is, How can I combine (if possible) the above queries in one query and making proper use of the indexes?

Update



As @ralf.w is mentioning there is a index missing in the table:

ALTER TABLE `my_table` ADD KEY `index_field_2_and_3` (`field_2`,`field_3`);

Answer

My version would look like this:

select
(select count(*) as field_1_is_2 from my_table where field_1 = 2) as field_1_is_2,
(select count(*) as field_2_is_4 from my_table where field_2 = 4) as field_2_is_4,
(select count(*) as field_3_is_5 from my_table where field_3 = 5) as field_3_is_5,
(select count(*) as fields_combined from my_table 
where field_3 != 2 and field_2 != 2) as fields_combined;

and let the rest be optimized and combined by the optimizer.

Executed prepended by EXPLAIN we see this:

1, PRIMARY, , , , , , , , No tables used
5, SUBQUERY, my_table, ALL, index_field_2,index_field_3, , , , 4, Using where
4, SUBQUERY, my_table, ref, index_field_3, index_field_3, 5, const, 1, Using index
3, SUBQUERY, my_table, ref, index_field_2, index_field_2, 5, const, 1, Using index
2, SUBQUERY, my_table, ref, index_field_1, index_field_1, 5, const, 1, Using index

so really missing here is the index for field_3 and field_2:

ALTER TABLE `my_table` ADD KEY `index_field_2_and_3` (`field_2`,`field_3`);

After adding the double-field index "Using Where" changes to "Using where; Using index"

Comments