Very slow simple mysql query

I have a very slow mysql query as follows:

SELECT function,  CONVERT_TZ(`time`, '+01:00','+01:00') 
FROM `function_logger` 
WHERE unit_id=3067785 
  and part_id=3 and channel=0  
  and `time` > NOW()-INTERVAL 1 DAY 
order by time;

for the following table structure

CREATE TABLE IF NOT EXISTS `function_logger` (
  `id` int(11) NOT NULL,
  `unit_id` int(11) NOT NULL,
  `part_id` tinyint(4) NOT NULL DEFAULT '0',
  `channel` tinyint(4) NOT NULL DEFAULT '0',
  `function` tinyint(11) NOT NULL,
  `time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `function_logger`
  ADD PRIMARY KEY (`id`),
  ADD KEY `unit_id` (`unit_id`);

The table contains about 80 million records but takes around a minute to run. Once cached it is fine.

unit_id contains around 3000 different random numbers, part_id upto 10 and channel upto 3.

Is there anyway to speed this up?

Would partitioning help?

Solution

Assuming a uniform distribution of unit_id, then selecting a specific one (of 3000) amongst 80m records leaves you with over 25k records to examine.

Since you have no further indexing to assist, MySQL must currently retrieve and inspect every single one of those 25k records in order to determine whether they match the remaining filter criteria.

Adding a composite index (i.e. one that is defined over multiple columns) would help you here—MySQL can thereby further reduce the records that it need examine. However, with such low cardinality, part_id and channel may not help much. It's not clear what sort of cardinality might exist from time, but that's probably a good starting point:

CREATE INDEX unit_time ON function_logger (unit_id, time)

You could add the other filter columns too (though beware that time should come last, since you're searching a range upon it)—however, the more columns in the index, the slower that writes to the table will become (and the larger that the index file and memory footprint will be).

The fastest read performance would be obtained from a covering index:

CREATE INDEX covering ON function_logger (
  unit_id, part_id, channel, time, function
)