MySQL Forums
Forum List  »  Optimizer & Parser

optimize range query(using between, group by and order by)
Posted by: Özgen Erbakan
Date: March 20, 2018 02:34AM

CREATE TABLE `stat_device` (
`date` date NOT NULL,
`company_id` varchar(50) NOT NULL COMMENT 'C1 Company ID',
`customer_id` varchar(50) DEFAULT NULL COMMENT 'C1 MSP/Enterprise ID',
`device_id_cdm` varchar(50) NOT NULL DEFAULT ''
PRIMARY KEY (`date`,`company_id`,`device_id_cdm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8';


SELECT
customer_id,
company_id,
date
FROM stat_device
WHERE date between '2016-01-01' and '2018-03-15'
and customer_id = 'aaa'
and company_id = 'bbb'
GROUP BY company_id , date
order by date;

If I run this query for a huge collection, it takes ages to respond and sometimes it time-outs. I'm wondering how to optimize the query by adding indexes or rewriting the query.

Appreciate any help.

Options: ReplyQuote


Subject
Views
Written By
Posted
optimize range query(using between, group by and order by)
555
March 20, 2018 02:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.