CODEWITHSUNDEEP
mysqlsql
fguillen
fguillen

Reputation: 38888

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`);

Upvotes: 0

Views: 88

Answers (4)

ralf.w.
ralf.w.

Reputation: 1696

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"

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

With where field_3 != 2 and field_2 != 2 it is very, very unlikely that one of your indexes will be used. field_3 != 2 should be true for an awful lot of records. Same for field_2 != 2. The DBMS won't scan the indexes to get, say, 80 percent of the records with one criteria and another 70% percent with the other and then intersect the sets. It will rather simply read the table once from beginning to end, i.e. perform a full table scan.

As it will read the whole table for this count, you shouldn't get the other counts in separate queries, but get the counts on the fly instead. So the option with conditional sums is the way to go. In standard SQL:

select
  count(case when field_1 = 2 then 1 end) as field_1_is_2,
  count(case when field_2 = 4 then 1 end) as field_2_is_4,
  count(case when field_3 = 5 then 1 end) as field_3_is_5,
  count(case when field_3 != 2 and field_2 != 2 then 1 end) as fields_combined
from my_table;

In MySQL:

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

Upvotes: 1

michelek
michelek

Reputation: 2536

I can't imagine you can do it more effectively than querying every index separately. In scenarios popping into my mind I would use something along these lines:

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

returning me every value on separate row:

criteria    count(*)
field_1_is_2    1
field_2_is_4    1
field_3_is_5    1
fields_combined 3

Upvotes: 1

Ankur Rastogi
Ankur Rastogi

Reputation: 99

You can use MYSQL Union All command . I have create a simillar table and inserted test data.

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

Now the sql query that I am using is

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

This is giving result like

+--------------+
| field_1_is_2 |
+--------------+
|            1 |
|            4 |
|            1 |
+--------------+

You should be able to get the result as first row, second row and third row. This will harness the power of indexes as well.

Upvotes: -1

Related Questions