Mysql 8 picking wrong index for group by date function [closed]

Question

I have a table as below.

CREATE TABLE `alarm` (
  `alarmTypeID` tinyint(4) NOT NULL,
  `vehicleID` mediumint(9) NOT NULL,
  `alarmDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `insertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `alarmLatitude` float(10,8) NOT NULL,
  `alarmLongitude` float(11,8) NOT NULL,
  `fenceID` mediumint(6) DEFAULT NULL,
  `alarmValue` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and below is the indexes for the table.

ALTER TABLE `alarm`
  ADD PRIMARY KEY (`vehicleID`,`alarmDateTime`,`alarmTypeID`) USING BTREE,
  ADD KEY `fenceID` (`fenceID`);

I ran this query now.

select date(alarm.alarmDateTime)
FROM alarm 
Where alarm.alarmDateTime>'2019-08-01' group by date(alarm.alarmDateTime)

Below is the explain output but it shows the key as fenceID and possible key also as fenceID but in the query above I never used the fenceID field at all?

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra

1
SIMPLE
alarm
NULL
index
PRIMARY,fenceID
fenceID
4
NULL
10197
33.33
Using where; Using index; Using temporary

Answer 1

date(alarm.alarmDateTime)

MySQL cannot use indexes for any column 'hidden inside a function call'.

Anyway, in

Where alarm.gDateTime>'2019-08-01'
group by ...

The most you can get is the use of INDEX(gDateTime) to help with the filtering. Even so, if too large a percentage of the table is needed, that index will be ignored.

OTOH, a "covering index" would be best:

INDEX(gDateTime, alarmDateTime)  -- in this order

because it contains all the columns needed by the SELECT.

Grrr... The query has changed. Now it does not make sense. Perhaps you want

SELECT  DATE(alarmDateTime),
        COUNT(*)                      -- adding this
    FROM alarm
    WHERE alarmDateTime >= '2019-08-01'  -- was just ">"
    GROUP BY 1                      -- shorthand for `DATE(...)`

This query would need an index starting with alarmDateTime, such as simply

INDEX(alarmDateTime)

ALTER TABLE .. ADD INDEX ..;

As for DATETIME vs TIMESTAMP, what does it represent? Think of DATETIME as being a picture of a clock on your wall. Think of TIMESTAMP as an instant in the galaxy. The value of a DATETIME is frozen. A TIMESTAMP is stored in UTC, converting from/to local time as you store/fetch.

alarmLatitude float(10,8) has issues

• (m,n) with FLOAT or DOUBLE
• FLOAT holds only about 7 significant digits, not 10
• 8 decimal places is overkill, see http://mysql.rjweb.org/doc.php/latlng#representation_choices

If you are tracking vehicles and need to keep huge amounts of data, see this for more tips on datatypes: http://mysql.rjweb.org/doc.php/schema_best_practices_mysql . (You have already followed most of the tips.)

Yes, another index will take space, but it may be worth it.

So, why did it use INDEX(fenceID)? In InnoDB, each secondary key includes the columns of the PK. So, it is effectively (fenceID, vehicleID, alarmDateTime, alarmTypeID). Since that was the smallest "covering" index for your query, the Optimizer picked it. But then it used only one column (alarmDateTime), and not even the first column.

Answer 2

Try to use the index by an expression:

CREATE INDEX idx_name ON alarm (gDateTime, (DATE(alarmDateTime)))

If It will not help then add generated column and index which uses it:

ALTER TABLE alarm 
ADD COLUMN alarmDate DATE AS (DATE(alarmDateTime)) /* VIRTUAL */ ,
ADD INDEX idx_name ON (gDateTime, alarmDate)

Then use new column instead of expression in your query.