MySQL GROUP BY Clause – Using Unique Index Instead of Column

group byindexMySQL

I have a table shift_wise_traffic with following columns.

toll        date            shift      car      truck      bus
 1          2016-09-01        1        10        20        30
 1          2016-09-01        2        15        25        35
 1          2016-09-01        3        20        30        40

 2          2016-09-01        1        5         10        15
 2          2016-09-01        2        20        25        30
 2          2016-09-01        3        35        40        50

 1          2016-09-02        1        10        20        30
 1          2016-09-02        2        10        20        30
 1          2016-09-02        3        10        20        30

 2          2016-09-02        1        10        20        30
 2          2016-09-02        2        10        20        30
 2          2016-09-02        3        10        20        30

Now I want to produce the output from shift_wise_traffic table group by
toll, date. And I have created a unique-index named shift_unique_index having two columns toll and date.

so instead of writing following query,

SELECT toll, date, SUM(car), SUM(truck), SUM(bus) FROM shift_wise_traffic 
GROUP BY toll, date;

Can I use this query using shift_unique_index,

SELECT toll, date, SUM(car), SUM(truck), SUM(bus) FROM shift_wise_traffic 
GROUP BY shift_unique_index;

Best Answer

SELECT toll, date, SUM(car), SUM(truck), SUM(bus)
    FROM shift_wise_traffic 
    GROUP BY toll, date;

The GROUP BY must have a list of columns, not an index name.
An INDEX is an optimization, not a requirement.
An INDEX may be useful for queries like this. (No index is likely to be useful for this particular query.)
Probably this query will simply scan the entire table, sort by toll+date, sum up the values, then deliver the output. (Or it may build an in-memory hash based on toll+date for summing.)
UNIQUE versus INDEX -- makes no difference in this context. UNIQUE = INDEX + a constraint. For INSERT, UNIQUE prevents duplicates.

It feels like your PRIMARY KEY is (date, shift, toll) in some order. Is it? If so, then changing the order to PRIMARY KEY(toll, date, shift) might make your GROUP BY run a little faster. (But it might slow down other operations.)

Related Solutions

MySQL Ticket Counting – Counting Number of Open Tickets Per Day in MySQL

You can use conditional sum for this calculation something as

select 
date_format(created,'%d-%m') as created_date , 
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select 
    -> date_format(created,'%d-%m') as created_date , 
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select 
 date_format(created,'%d-%m') as created_date , 
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select 
 date_format(t.created,'%d-%m') as created_date , 
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created) 
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+

Mysql – Unique Index/Constraint with multiple columns, one column is nullable

Since the alias column is nullable, if you add a unique constraint on the composite (last_name, first_name, birth_date, alias), there will still be duplicates allowed, with the same values in the first 3 columns and NULL in the alias. The constraint is skipped / accepted when at least one value is null. MySQL documentation on CREATE TABLE is not very clear but you can test the behaviour:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

What you could do is to define the alias as not null and add a default value, (say 'NONE' or 'DEFAULT' or the empty string ''). You (or the user) will not have to provide that value, it will be automatically saved in all rows. Once someone tries to add a new row with same last name, first name and birth date as an existing row, the unique constraint will forbid it. I guess you could add some procedure at that point, that asks for a different value for the alias and adds the new row with it.

Best Answer

Related Solutions

MySQL Ticket Counting – Counting Number of Open Tickets Per Day in MySQL

Mysql – Unique Index/Constraint with multiple columns, one column is nullable

Related Question