From: Octavian Rasnita
Date: June 23 2010 5:44am
Subject: Differences between 2 MySQL instances
List-Archive: http://lists.mysql.com/mysql/221974
Message-Id: <2320E7FA277B462CBAE2DEC1B25F6563@teddy11>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_004B_01CB12B0.572D6D10"

------=_NextPart_000_004B_01CB12B0.572D6D10
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

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=3DInnoDB AUTO_INCREMENT=3D1154030054 DEFAULT CHARSET=3Dlatin1=20

I have tried the following query under both MySQL servers:

explain select * from table_name
where
symbol=3D'etc'
and market=3D'etc2'
and date>=3D'2010-01-01'
and tip_ticker=3D1
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=20

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=20

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=3D'etc'
and market=3D'etc2'
and date>=3D'2010-01-01'
and tip_ticker=3D1
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=20

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 signatur=
e database 5220 (20100623) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


------=_NextPart_000_004B_01CB12B0.572D6D10--