CODEWITHSUNDEEP
mysqlsqlperformanceindexing
Introduction to programming
Introduction to programming

Reputation: 31

How to improve query speed in mysql query

I'm trying to optimize my query speed as much as possible. A side problem is that I cannot see the exact query speed, because it is rounded to a whole second. The query does get the expected result and takes about 1 second. The final query should be extended even more and for this reason i am trying to improve it. How can this query be improved?

The database is constructed as an electricity utility company. The query should eventually calculate an invoice. I basically have 4 tables, APX price, powerdeals, powerload, eans_power.

APX price is an hourly price, powerload is a quarterly hour volume. First step is joining these two together for each quarter of an hour.

Second step is that I currently select the EAN that is indicated in the table eans_power.

Finally I will join the Powerdeals that currently consist only of a single line and indicates from which hour, until which hour and weekday from/until it should be applicable. It consist of an hourly volume and price. Currently it is only joined on the hours, but it will be extended to weekdays as well.

MYSQL Query:

SELECT l.DATE, l.PERIOD_FROM, a.PRICE, l.POWERLOAD, 
SUM(a.PRICE*l.POWERLOAD), SUM(d.hourly_volume/4) 
FROM timeseries.powerload l 
INNER JOIN timeseries.apxprice a ON l.DATE = a.DATE 
INNER JOIN contracts.eans_power c ON  l.ean = c.ean 
LEFT OUTER JOIN timeseries.powerdeals d ON d.period_from <= l.period_from 
AND d.period_until >= l.period_until 
WHERE l.PERIOD_FROM >= a.PERIOD_FROM 
AND l.PERIOD_FROM < a.PERIOD_UNTIL 
AND l.DATE >= '2018-01-01' 
AND l.DATE <= '2018-12-31' 
GROUP BY l.date

Explain:

1   SIMPLE  c   NULL    system  PRIMARY,ean NULL    NULL    NULL    1   100.00  Using temporary; Using filesort 

1   SIMPLE  l   NULL    ref EAN EAN 21  const   35481   11.11   Using index condition

1   SIMPLE  d   NULL    ALL NULL    NULL    NULL    NULL    1   100.00  Using where; Using join buffer (Block Nested Loop)

1   SIMPLE  a   NULL    ref DATE    DATE    4   timeseries.l.date   24  11.11   Using index condition

Create table queries:

apxprice 

CREATE TABLE `apxprice` (
  `apx_id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `period_from` time DEFAULT NULL,
  `period_until` time DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`apx_id`),
  KEY `DATE` (`date`,`period_from`,`period_until`)
) ENGINE=MyISAM AUTO_INCREMENT=29664 DEFAULT CHARSET=latin1

powerdeals 

CREATE TABLE `powerdeals` (
  `deal_id` int(11) NOT NULL AUTO_INCREMENT,
  `date_deal` date NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `weekday_from` int(11) NOT NULL,
  `weekday_until` int(11) NOT NULL,
  `period_from` time NOT NULL,
  `period_until` time NOT NULL,
  `hourly_volume` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `type_deal_id` int(11) NOT NULL,
  `contract_id` int(11) NOT NULL,
  PRIMARY KEY (`deal_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

powerload 

CREATE TABLE `powerload` (
  `powerload_id` int(11) NOT NULL AUTO_INCREMENT,
  `ean` varchar(18) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `period_from` time DEFAULT NULL,
  `period_until` time DEFAULT NULL,
  `powerload` int(11) DEFAULT NULL,
  PRIMARY KEY (`powerload_id`),
  KEY `EAN` (`ean`,`date`,`period_from`,`period_until`)
) ENGINE=MyISAM AUTO_INCREMENT=61039 DEFAULT CHARSET=latin1

eans_power 

CREATE TABLE `eans_power` (
  `ean` char(19) NOT NULL,
  `contract_id` int(11) NOT NULL,
  `invoicing_id` int(11) NOT NULL,
  `street` varchar(255) NOT NULL,
  `number` int(11) NOT NULL,
  `affix` char(11) NOT NULL,
  `postal` char(6) NOT NULL,
  `city` varchar(255) NOT NULL,
  PRIMARY KEY (`ean`),
  KEY `ean` (`ean`,`contract_id`,`invoicing_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Sample data tables

apx_prices

powerdeals

powerload

eans_power

Result, without sum() and group by:

Result, with sum() and group by:

Upvotes: 1

Views: 251

Answers (1)

Rick James
Rick James

Reputation: 142208

Preliminary optimizations:

  • Use InnoDB, not MyISAM.
  • Use CHAR only for constant-lenght strings
  • Use consistent datatypes (see ean, for example)

For an alternative to using time-to-the-second, check out the Handler counts .

Because range tests (such as l.PERIOD_FROM >= a.PERIOD_FROM AND l.PERIOD_FROM < a.PERIOD_UNTIL) are essentially impossible to optimize, I recommend you expand the table to have one entry per hour (or 1 per quarter hour, if necessary). Looking up a row via a key is much faster than doing a scan of "ALL" the table. 9K rows for an entire year is trivial.

When you get past these recommendations (and the Comments), I will have more tips on optimizing the indexes, especially InnoDB's PRIMARY KEY.

Upvotes: 2

Related Questions