MySQL Forums
Forum List  »  Optimizer & Parser

Re: Primary key index with a DATETIME as first part of the compound key is never used
Posted by: Rick James
Date: January 02, 2012 09:06PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Primary key index with a DATETIME as first part of the compound key is never used
1449
January 02, 2012 09:06PM


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.