Announcement

Announcement Module
Collapse
No announcement yet.

mysql not using index

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql not using index

    Hi, I have these 2 tables;

    CREATE TABLE `transaction_it_200701` (
    `transid` varchar(100) NOT NULL default '',
    `service` varchar(100) NOT NULL default '',
    .....
    PRIMARY KEY (`transid`),
    KEY `data` (`start_date`)

    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

    CREATE TABLE `transactionlog_it_200701` (
    `code` bigint(20) NOT NULL auto_increment,
    `type` varchar(100) NOT NULL default '',
    `transid` varchar(100) NOT NULL default '0',
    `transLogid` varchar(100) NOT NULL default '0',
    `status` int(11) NOT NULL default '0',
    .....
    PRIMARY KEY (`code`),
    KEY `transidentifier` (`transid`,`transLogid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;


    Both the tables contains about 3 million of rows.

    I have this query:
    explain SELECT * FROM transaction_it_200701 transaction INNER JOIN transactionlog_it_200701 transactionlog ON transaction.transid = t
    ransactionlog.transid WHERE transaction.start_date >= '2007-01-22 00:41:00' and transaction.start_date <= '2007-01-26 00:41:59' GROUP BY 1,3;

    and the EXPLAIN result is:

    +----+-------------+----------------+--------+-------------- ---+---------+---------+------------------------------------ +---------+---------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+----------------+--------+-------------- ---+---------+---------+------------------------------------ +---------+---------------------------------+
    | 1 | SIMPLE | transactionlog | ALL | transidentifier | NULL | NULL | NULL | 2489139 | Using temporary; Using filesort |
    | 1 | SIMPLE | transaction | eq_ref | PRIMARY,data | PRIMARY | 300 | reporting_1.transactionlog.transid | 1 | Using where |
    +----+-------------+----------------+--------+-------------- ---+---------+---------+------------------------------------ +---------+---------------------------------+
    2 rows in set (0.00 sec)


    It seems the second table index in not used.

    In production this query takes 10 minutes!! :-(

    Could you help me?

    Thanks.
    Cillu.

  • #2
    Index is used on transaction table but not transactionlog...

    You did not list which type start_date has. Also check what kind of selectivity does this range have - might be it is large and so full table scan is more efficient.

    Also note you use SELECT * together with GROUP BY 1,3 which is extremely fragile.

    Comment

    Working...
    X