Re: Differences between 2 MySQL instances

[Octavian Rasnita]

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@stripped 
  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.rasnita@stripped>

    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: [phone]
  Email: john.daisley@stripped



__________ Information from ESET NOD32 Antivirus, version of virus signature database 5223
(20100623) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com