Re: Primary key index with a DATETIME as first part of the compound key is never used

[Rick James]

Try this on 5.5; it works fine on 5.1.30:

use strict;
use Time::HiRes;
use DBIx::DWIW;

# Works ok in 5.1.30; claimed to be bad in 5.5
# forum 115  thread 507085

my $start = Time::HiRes::time();

# Change as needed:
my $db = new DBIx::DWIW(Host=>'localhost', User=>'try', Pass=>$ARGV[0], DB=>'try')
      or die $@;

ReCreate();
Populate();
HashPrint(qq{SHOW TABLE STATUS LIKE t507085});
my $select = qq{SELECT *
        FROM `t507085`
        WHERE dateDim = '2014-04-03 00:00:00'
          AND accountDim = 4
          AND execCodeDim = 2
          AND operationTypeDim = 1
          AND junkDim = 2
          AND ipCountryDim = 3
};
HashPrint(qq{EXPLAIN $select});
Select($select);
my $elaps = Time::HiRes::time() - $start;
printf "%.1f sec.\n", $elaps;
exit;

sub ReCreate
{
    my $sql = qq{DROP TABLE t507085};
    $db->Execute($sql);
    die $@ if $@;

    $sql = qq{CREATE TABLE `t507085` (
        `dateDim` datetime NOT NULL,
        `accountDim` mediumint(8) unsigned NOT NULL,
        `execCodeDim` smallint(5) unsigned NOT NULL,
        `operationTypeDim` tinyint(3) unsigned NOT NULL,
        `junkDim` tinyint(3) unsigned NOT NULL,
        `ipCountryDim` smallint(5) unsigned NOT NULL,
        `count` int(10) unsigned NOT NULL,
        `amount` bigint(20) NOT NULL,
        PRIMARY KEY (`dateDim`,`accountDim`,`execCodeDim`,`operationTypeDim`,`junkDim`
      ,`ipCountryDim`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8};
    $db->Execute($sql);
    die $@ if $@;
}

sub Populate
{
    my $n = 5;            ## Change to make more or less data
    for my $date ('2014-04-01', '2014-04-02', '2014-04-03', '2014-04-04', '2014-04-05', '2014-04-06', '2014-04-07', '2014-04-08') {
    for my $acct (1..$n) {
    for my $exec (1..$n) {
    for my $oper (1..$n) {
    for my $junk (1..$n) {
    for my $ipco (1..$n) {
    my $sql = qq{INSERT INTO t507085 VALUE
                    ('$date', $acct, $exec, $oper, $junk, $ipco, 111*rand(), 9999*rand())};
    $db->Execute($sql);
    die $@ if $@;
    }}}}}}
}

sub HashPrint
{
    my ($sql) = @_;
    my $h = $db->Hash($sql);
    for my $k (keys %$h)
    {
        printf "%20s = %s\n", $k, $h->{$k};
    }
}

sub Select
{
    my ($sql) = @_;
    my $start = Time::HiRes::time();
    my @row = $db->FlatArray($sql);
    die $@ if $@;
    my $elaps = Time::HiRes::time() - $start;
    printf "Select (%.2f ms) %s\n", $elaps, join(', ', @row);
}

What version of 5.5 are you using?

If this does not work well on 5.5, then file a bug at bugs.mysql.com.

My output was

               Extra =
             key_len = 17
                 ref = const,const,const,const,const,const
               table = t507085
                rows = 1
                 key = PRIMARY
         select_type = SIMPLE
       possible_keys = PRIMARY
                  id = 1
                type = const
Select (0.00 ms) 2014-04-03 00:00:00, 4, 2, 1, 2, 3, 95, 5773
650.3 sec.

Note that the EXPLAIN does what it should do, and the select is very fast.