Have you tried running 'OPTIMIZE TABLE' on the tables in question to make sure statistics are up to date.
I would expect the vast majority of queries to run faster on MySQL 5.1 (with identical settings, hardware and operating system). 2010/6/23 Octavian Rasnita <octavian.rasn...@ssifbroker.ro> > 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 > > -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Microsoft SQL Server 2005/2008 Database Administrator Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
