Hello,
I have the following table under MySQL 5.1.43-community under Windows, and
under MySQL 5.0.82sp1 Source distribution under Linux):
CREATE TABLE `table_name` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tip_ticker` tinyint(1) NOT NULL,
`symbol` varchar(20) NOT NULL,
`market` varchar(20) NOT NULL,
`date` date DEFAULT NULL,
`time` time DEFAULT NULL,
`price` decimal(20,4) unsigned DEFAULT NULL,
`price_adjusted` double DEFAULT NULL,
`volume` bigint(20) unsigned DEFAULT NULL,
`volume_adjusted` double(255,0) unsigned DEFAULT NULL,
`bid` decimal(20,4) unsigned DEFAULT NULL,
`ask` decimal(20,4) unsigned DEFAULT NULL,
`bid_volume` bigint(20) unsigned DEFAULT NULL,
`ask_volume` bigint(20) unsigned DEFAULT NULL,
`trades` int(10) unsigned DEFAULT NULL,
`change_percent` decimal(20,4) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `i1` (`date`,`time`,`id`),
KEY `i2` (`symbol`,`date`,`time`,`id`),
KEY `i3` (`tip_ticker`,`date`,`time`,`id`),
KEY `i4` (`symbol`,`market`,`date`,`time`),
KEY `i5` (`trades`,`date`,`time`,`symbol`,`market`),
KEY `i6` (`change_percent`,`date`,`time`,`symbol`,`market`),
KEY `i7` (`date`,`time`,`symbol`,`market`)
) ENGINE=InnoDB AUTO_INCREMENT=1154030054 DEFAULT CHARSET=latin1
I have tried the following query under both MySQL servers:
explain select * from table_name
where
symbol='etc'
and market='etc2'
and date>='2010-01-01'
and tip_ticker=1
order by trades, date, time, symbol, market
limit 20\G
The result under Windows is:
id: 1
select_type: SIMPLE
table: table_name
type: index
possible_keys: i1,i2,i3,i4,i7
key: i5
key_len: 57
ref: NULL
rows: 4058
Extra: Using where
But the result under Linux is:
id: 1
select_type: SIMPLE
table: table_name
type: range
possible_keys: i1,i2,i3,i4,i7
key: i4
key_len: 48
ref: NULL
rows: 96000
Extra: Using where; Using filesort
This query obviously takes a much longer time than the one under Windows.
I have also tried to force index(i5) under Linux in order to force using the
same index as under Windows:
explain select * from table_name
force index(i5)
where
symbol='etc'
and market='etc2'
and date>='2010-01-01'
and tip_ticker=1
order by trades, date, time, symbol, market
limit 20\G
But the result is:
id: 1
select_type: SIMPLE
table: table_name
type: index
possible_keys: NULL
key: i5
key_len: 57
ref: NULL
rows: 11020086
Extra: Using where
Even though this query uses the same index as the one under Windows, the number
of estimated rows is approximately the total number of rows in the table and it
also takes a very long time to complete.
Do you have any idea why this works differently under Linux? Is it because
under Linux I have MySQL 5.0 and under Windows MySQL 5.1 and I definitely need
to upgrade?
There are some differences between the global variables that start with innodb_
under Windows and Linux, but I don't know if those differences make InnoDB to
choose another index.
Thank you.
--
Octavian
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 5220 (20100623) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
