0

I'm using mysql 5.5

Here is my table:

CREATE TABLE `temperature_information` 
(
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `device` int(11) NOT NULL,
   `temperature` int(11) NOT NULL,
   `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   KEY `device` (`device`),
   KEY `date` (`date`),
   KEY `idx` (`device`, `date`)
) ENGINE=InnoDB AUTO_INCREMENT=25602738 DEFAULT CHARSET=latin1

This table has ~50m. records.

Here is my query:

SELECT 
    date AS ValueDate, MAX(temperature)
FROM 
    (SELECT 
         date, temperature 
     FROM 
         temperature_information 
     WHERE 
         device = 1111 
     ORDER BY 
         temperature DESC) c 
GROUP BY 
    DATE(ValueDate), HOUR(ValueDate)

This query returns Maximum temperature for each day. It's execution time is ~0.9 s. and subquery's time 0.003 sec.

I have index on date, device columns and multicolumn index idx on date and device. Explain says it uses device index for query, which is good (subquery is very fast). But to get Max temperature for each day I need to use Group BY. I know that fuctions on indexed column disables use of index, but I don't know workaround how to make it efficient and produce same results.

My question: is it possible to write query which would be more efficient and would produce same results or I should process rows returned by sub query and find Max temperature for each day myself (this would be written in c)?

Sub query returns 20-40k rows on average.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vygintas B
  • 1,624
  • 13
  • 31
  • First of all, your subquery isn't needed. Apply the `group by` directly, remove the `order by`. Then show us the explain for the query. When there's still performance problems, just consider to have your timestamp also as separate columns of datatypes date and time. – fancyPants Nov 06 '17 at 14:08
  • @fancyPants I am aware of it. Removing sub query doesn't make any significant improvement's. But thank you anyways. – Vygintas B Nov 06 '17 at 14:13
  • If you need to know _when_ the max occurred, see "groupwise max". – Rick James Nov 08 '17 at 23:16
  • Efficient SQL for groupwise max can be found here: http://mysql.rjweb.org/doc.php/groupwise_max – Rick James Nov 08 '17 at 23:17
  • I asked same question [dba.stackexchange](https://dba.stackexchange.com/questions/190274/mysql-performance-for-aggregate-function?noredirect=1#comment369543_190274) and got quit nice responce. – Vygintas B Nov 09 '17 at 05:31

0 Answers0