Afternoon all
The problem: I am doing a simple query on a table, comparing 2 columns to
constants. The table is indexed with a compound index on these 2 columns.
The join optimizer only seems to notice that the first column is indexed,
and ignored the second column. The table is large (16M rows), innodb, all
details to follow.
I really need to get this query indexed. I have tried everything I can think
of, aside from upgrading to 4.1, though I will do that if there is a
realistic chance it will solve the problem, but this is a production server
and the upgrade is not straightforward to roll back from. I have read all
the pages related to query optimization and indexes in the manual, though it
could of course be something stupid I have done - in fact I hope it is. I
have tried optimize, analyze. I have tried ordering as index(Hash,Date)
instead of index(Date,Hash). Nothing will convice mysql to use the index on
the Hash column - explain stubbornly outputs 4 as the key_len instead of 25.
I have tried use index, force index, ignore index.
The server in question is a Dell 1750 with 3GB RAM dual 2.8GB h/t xeon,
mysql version 4.0.20 (mysql release, intel compiler build), slackware 9.0,
kernel 2.6.6.
Could the problem be related to memory size, ie is the index too big? If so,
how come it uses part of the index rather than none at all?
Any help very much appreciated and guruhood grovellingly acknowleged.
Thanks
Jim Page
Relevant data follows:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
mysql> explain SELECT * FROM r2 WHERE Date > "2004-09-14 15:35:41" AND Hash
= 'xj0001J01E4k0001K0001' limit 0,10;
+-------+-------+---------------+------+---------+------+---------+---------
----+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+-------+---------------+------+---------+------+---------+---------
----+
| r2 | range | hash | hash | 4 | NULL | 8354129 | Using
where |
+-------+-------+---------------+------+---------+------+---------+---------
----+
CREATE TABLE `r2` (
`RecipID` bigint(20) NOT NULL auto_increment,
`Date` timestamp(14) NOT NULL,
`AccountID` int(11) NOT NULL default '0',
`DomainID` int(11) NOT NULL default '0',
`EndUserID` int(11) NOT NULL default '-1',
`Recipient` text NOT NULL,
`MailInID` bigint(20) NOT NULL default '0',
`Status` enum('pending','virus','quarantine','spam','removed by
rule','fail','deleted','ok','rejected','sending','no
data','undefined','received','filtering','filtered','stalled') NOT NULL
default 'received',
`OtherID` bigint(20) default NULL,
`Attempts` int(11) NOT NULL default '1',
`Reported` enum('n','y') NOT NULL default 'n',
`Hash` varchar(21) NOT NULL default 'xj0000J0000k0000K0000',
`UserHash` varchar(18) default NULL,
PRIMARY KEY (`RecipID`),
KEY `MailInID` (`MailInID`),
KEY `Status` (`Status`),
KEY `hash` (`Date`,`Hash`)
) TYPE=InnoDB;
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
from show table status...
+-------------+--------+------------+----------+----------------+-----------
--+-----------------+--------------+-----------+----------------+-----------
----------+---------------------+------------+------------------------------
----------+-------------------------+
| Name | Type | Row_format | Rows | Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Create_options
| Comment |
+-------------+--------+------------+----------+----------------+-----------
--+-----------------+--------------+-----------+----------------+-----------
----------+---------------------+------------+------------------------------
----------+-------------------------+
| r2 | InnoDB | Dynamic | 16609743 | 155 |
2591031296 | NULL | 1784692736 | 0 | 33433662093 |
NULL | NULL | NULL |
| InnoDB free: 9775104 kB |
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
mysql> show index from r2;
+-------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+
| r2 | 0 | PRIMARY | 1 | RecipID | A |
16688555 | NULL | NULL | | BTREE | |
| r2 | 1 | MailInID | 1 | MailInID | A |
16688555 | NULL | NULL | | BTREE | |
| r2 | 1 | Status | 1 | Status | A |
18 | NULL | NULL | | BTREE | |
| r2 | 1 | hash | 1 | Date | A |
18 | NULL | NULL | | BTREE | |
| r2 | 1 | hash | 2 | Hash | A |
11081 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
