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.