I have tried, but with no difference.
I have changed some indexes and made the queries run faster, but I still found
a problem:
I use a module that does paging and it makes a select(*) and this query takes a
very long time.
I have also tried to do select(id) where the id column is the primary key, but
it runs very slow also.
I have also seen that select(*) with various where conditions is still very
slow.
Is there any trick to make the select(*) queries run faster within an InnoDB
table?
Thanks.
--
Octavian
----- Original Message -----
From: John Daisley
To: Octavian Rasnita
Cc: mysql@lists.mysql.com
Sent: Wednesday, June 23, 2010 3:55 PM
Subject: Re: Differences between 2 MySQL instances
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
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 5223 (20100623) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
