I use this query:

 SELECT 
     logdate, 
     SUM(bytes)/1024/1024/1024 as traffic, 
     customer 
 FROM 
     `Logdata` 
 WHERE 
     customer is not null AND 
     logdate >= "2016-07-01" AND 
     logdate <= "2016-07-30" 
 GROUP By logdate, customer

on this table with currently around 6 mio rows (but there will be 10 times more rows):

CREATE TABLE `Logdata` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `logdate` date DEFAULT NULL,
  `logtime` time DEFAULT NULL,
  `bytes` int(10) UNSIGNED DEFAULT NULL,
  `uri` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `customer` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `method` smallint(6) DEFAULT '200',
  `region` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

with this keys:

  ADD PRIMARY KEY (`id`),
  ADD KEY `IDX_logdate` (`logdate`),
  ADD KEY `IDX_method` (`method`),
  ADD KEY `IDX_customer` (`customer`),
  ADD KEY `IDX_customer_logdate` (`logdate`,`customer`);

and this execution plan:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
1 | SIMPLE | Logdata | NULL | index | IDX_logdate,IDX_customer,idx_customer_logdate | idx_customer_logdate | 391 | NULL | 6247535 | 25.00 | Using where

InnoDB Config is:

InnoDB buffer pool / data size: 2.0G/1.1G
InnoDB buffer pool instances: 2

With ~6mio rows in the table the query takes 14 seconds. Which will get worse as the amount of rows is increasing in a very fast pace.

The questions:

  • Would i benefit in this case from the myisam table engine?

  • What could i do to further optimize the query or the settings?

up vote 2 down vote accepted

Would i benefit in this case from the myisam table engine?

No you won't.

What could i do to further optimize the query or the settings?

Materialize the query.

MySQL does not have built-in means to do that easily (similar to indexed views in SQL Server or materialized views in Oracle) so you'll have to put some effort into it.

Create a table like this:

CREATE TABLE log_customer_date
        (
        customer BIGINT NOT NULL,
        logdate DATE NOT NULL,
        sumbytes BIGINT NOT NULL,
        countrecords BIGINT NOT NULL,
        PRIMARY KEY (customer, logdate)
        )

fill it:

INSERT
INTO    log_customer_date
SELECT  customer, logdate, SUM(bytes) sumbytes, COUNT(*) countrecords
FROM    logdata
GROUP BY
        customer, logdate

and add further records in a trigger:

INSERT
INTO    log_customer_date
VALUES  (NEW.customer, NEW.logdate, NEW.bytes, 1)
ON DUPLICATE KEY
UPDATE  sumbytes = sumbytes + VALUES(sumbytes),
        countrecords = countrecords + VALUES(countrecords)

or in a script every once in a while:

INSERT
INTO    log_customer_date
SELECT  customer, logdate, SUM(bytes) sumbytes, COUNT(*) countrecords
FROM    logdata
WHERE   id > :lastid
GROUP BY
        customer, logdate
ON DUPLICATE KEY
UPDATE  sumbytes = sumbytes + VALUES(sumbytes),
        countrecords = countrecords + VALUES(countrecords)

, recording the highest id by the moment of the insert somewhere.

You are I/O-bound. That cannot be solved by tuning!

  • Normalize region, customer, etc. This will save a lot of space and shrink the data, thereby making queries faster.
  • Consider not splitting date and time -- it is usually less complex in the long run to have a single DATETIME.
  • As @Quassnoi spells out, summarize the day's data each night. Put the results into a summary table. Then fetch from that table. This will speed things up a lot.
  • In composite indexes, put the '=' column first, the 'range' column last: INDEX(customer, logdate).

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.