Slow Select com índices. Como pode ser melhorado?

Eu corro select em uma única tabela (Engine=InnoDB), não muito complexa, mas com muitas linhas.

A primeira seleção para um id é mais lenta, demora alguns segundos para 9M de linhas, as próximas seleções são muito mais rápidas, mesmo quando mudo a consulta.

Eu tentei mysql no Windows e mariadb no Linux.

Eu corro o comando select assim:

select `id`,count(*), sum(`counts`) from reference
    where `id`=848
      and `started`<= '2000-01-04 00:00:00'
      and `ended`  >= '2000-01-03 00:00:00';

ou assim:

 select min(`counts`),max(`counts`) from reference where `id`=848 ;

não importa qual foi a primeira consulta, a primeira é mais lenta.

quando eu corro no mariadb no linux, o id consequente às vezes era rápido, mas no mysql no windows também era lento na primeira vez. isso me fez pensar que talvez eu estivesse perdendo alguma coisa.

testes, resultado e medida de tempo e podem ser encontrados abaixo.

Obrigado por qualquer ajuda!

Este é o meu db:

create database my_test_db default char set utf8 ;
use my_test_db;
create table items (
    `id` int(11) not null auto_increment, 
    `name` varchar(50), 
    `description` varchar(250) default '', 
    primary key (`id`), 
    unique key item_name_unique(`name`)
);
create table reference (
    `id` int(11) not null,
    `started` datetime not null,
    `ended` datetime not null,
    `counts` int(11) not null,
    key fk_item_id_idx (`id`),
    key idx_started (`started`),
    key idx_ended (`ended`),
    constraint fk_item_id foreign key (`id`) references items(`id`)
              on delete no action on update no action
);

Uma exibição gráfica:

MariaDB [my_test_db]> describe items;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(50)  | YES  | UNI | NULL    |                |
| description | varchar(250) | YES  |     |         |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [my_test_db]> describe reference;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id      | int(11)  | NO   | MUL | NULL    |       |
| started | datetime | NO   | MUL | NULL    |       |
| ended   | datetime | NO   | MUL | NULL    |       |
| counts  | int(11)  | NO   |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Eu usei o seguinte programa para criar 2 infiles:

#include <iostream>
#include <fstream>
#include <string>
#include <ctime>
#include <sstream>
#include <cstring>
#include <random>
#define TM_BUF_SIZE 32

#ifndef WIN32
#define localtime_s(PTM,PTIME_T) localtime_r(PTIME_T,PTM)
#endif
int main(int argc, char** argv)
{
    int id_max, count_iterations, time_frame;
    if(argc!=4)
    {
        std::cerr<<"Missing Arguments!!"<<std::endl;
        std::cerr<<"Usage: DataGen item_count time_iteration time_frame"<<std::endl;
        return -1;
    }
    id_max  = (int)strtol(argv[1],nullptr,0);
    count_iterations = (int)strtol(argv[2],nullptr,0);
    time_frame = (int)strtol(argv[3],nullptr,0);

    std::random_device r;
    std::default_random_engine re(r());
    std::uniform_int_distribution<int> uni_dist(0, 15);
    std::tm temp, tmStart,tmEnd;
    char bufStart[TM_BUF_SIZE], bufEnd[TM_BUF_SIZE];
    std::memset(&temp, 0, sizeof(tm));

    std::ofstream fitems("items.dat");
    for (int id = 1; id <= id_max; id++)
    {
        fitems << id << "\tid-" << id << "\titem.number." << id << std::endl;
    }
    temp.tm_year = 100;
    temp.tm_mday = 1;
    time_t ts_start = mktime(&temp);
    time_t ts_end;
    int iteration_left = count_iterations;
    std::ofstream frefs("references.dat");
    while(iteration_left--)
    {
        ts_end = ts_start + time_frame;
        localtime_s(&tmStart, &ts_start);
        localtime_s(&tmEnd, &ts_end);
        std::strftime(bufStart, TM_BUF_SIZE, "%Y-%m-%d %H:%M:%S.0", &tmStart);
        std::strftime(bufEnd, TM_BUF_SIZE, "%Y-%m-%d %H:%M:%S.0", &tmEnd);
        for (int id = 1; id <= id_max; id++)
        {
            int count = uni_dist(re);
            frefs << id << "\t" << bufStart << "\t" << bufEnd << "\t"<<count<< std::endl;
        }
        ts_start = ts_end;
        if(iteration_left && 0 == iteration_left % 100)
        {
            std::cout<<iteration_left<<" iterations left"<<std::endl;
        }
    }
    std::cout<<"Done!"<<std::endl;
        return 0;
}

copiou no linux usando:

g++ -std=c++0x dataGen.cpp -o DataGen

execute o programa DataGen assim:

DataGen 3000 3000 60

O programa cria 2 arquivos: "items.dat" e "references.dat"

carregue os dados no banco de dados:

use my_test_db;
load data infile '/root/items.dat' into table items;
load data infile '/root/references.dat' into table reference;

Então eu preenchi as tabelas com muitas linhas: itens com 3K de linhas e referência com 9M de linhas.

agora estou executando selects na referencemesa:

Aqui estão os resultados:

#first time for this id: 
MariaDB [my_test_db]> select `id`,count(*), sum(`counts`) from reference where `id`=848 and `started`<= '2000-01-03 00:00:00' and `ended`>='2000-01-02 00:00:00';
+------+----------+---------------+
| id   | count(*) | sum(`counts`) |
+------+----------+---------------+
|  848 |     1442 |         10640 |
+------+----------+---------------+
1 row in set (3.31 sec)

#next query for same id change time filters:
MariaDB [my_test_db]> select `id`,count(*), sum(`counts`) from reference where `id`=848 and `started`<= '2000-01-04 00:00:00' and `ended`>='2000-01-03 00:00:00';
+------+----------+---------------+
| id   | count(*) | sum(`counts`) |
+------+----------+---------------+
|  848 |      121 |           944 |
+------+----------+---------------+
1 row in set (0.03 sec)

#next query for same id change time filters again:
MariaDB [my_test_db]> select `id`,count(*), sum(`counts`) from reference
    where `id`=848
      and `started`<= '2000-01-02 00:00:00'
      and `ended`  >= '2000-01-01 00:00:00';
+------+----------+---------------+
| id   | count(*) | sum(`counts`) |
+------+----------+---------------+
|  848 |     1441 |         10848 |
+------+----------+---------------+
1 row in set (0.06 sec)

-- altera apenas o id:

MariaDB [my_test_db]> select `id`,count(*), sum(`counts`) from reference
    where `id`=1848
      and `started`<= '2000-01-02 00:00:00'
      and `ended`  >= '2000-01-01 00:00:00';
+------+----------+---------------+
| id   | count(*) | sum(`counts`) |
+------+----------+---------------+
| 1848 |     1441 |         10576 |
+------+----------+---------------+
1 row in set (2.63 sec)

#use same id change time filters: 
MariaDB [my_test_db]> select `id`,count(*), sum(`counts`) from reference
    where `id`=1848
      and `started`<= '2000-01-02 12:00:00'
      and `ended`  >= '2000-01-01 12:00:00';
+------+----------+---------------+
| id   | count(*) | sum(`counts`) |
+------+----------+---------------+
| 1848 |     1442 |         10780 |
+------+----------+---------------+
1 row in set (0.03 sec)

#use consequent id is also fast:
MariaDB [my_test_db]> select `id`,count(*), sum(`counts`) from reference
    where `id`=1849
      and `started`<= '2000-01-02 12:00:00'
      and `ended`  >= '2000-01-01 12:00:00';
+------+----------+---------------+
| id   | count(*) | sum(`counts`) |
+------+----------+---------------+
| 1849 |     1442 |         11001 |
+------+----------+---------------+
1 row in set (0.11 sec)

-- outra consulta - mesmo id - rápido

MariaDB [my_test_db]> select min(counts),max(counts) from reference where `id`=1849 ;
+-------------+-------------+
| min(counts) | max(counts) |
+-------------+-------------+
|           0 |          15 |
+-------------+-------------+
1 row in set (0.03 sec)

#again it is slow for other id
MariaDB [my_test_db]> select min(counts),max(counts) from reference where `id`=1800 ;
+-------------+-------------+
| min(counts) | max(counts) |
+-------------+-------------+
|           0 |          15 |
+-------------+-------------+
1 row in set (2.36 sec)

-- descreve a consulta:

MariaDB [my_test_db]> describe select `id`,count(*), sum(`counts`) from reference where `id`=1849 and `started`<= '2000-01-02 12:00:00' and `ended`>='2000-01-01 12:00:00';
+------+-------------+-----------+------+--------------------------------------+----------------+---------+-------+------+-------------+
| id   | select_type | table     | type | possible_keys                        | key            | key_len | ref   | rows | Extra       |
+------+-------------+-----------+------+--------------------------------------+----------------+---------+-------+------+-------------+
|    1 | SIMPLE      | reference | ref  | fk_item_id_idx,idx_started,idx_ended | fk_item_id_idx | 4       | const | 2999 | Using where |
+------+-------------+-----------+------+--------------------------------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)