List:General Discussion« Previous MessageNext Message »
From:Douglas Sims Date:September 7 2006 6:03am
Subject:Re: problem with InnoDB
View as plain text  
Hi Prasad

This question got me a bit interested as we're thinking of moving  
some MyISAM tables to InnoDB and I haven't used it much.

I decided to test some of these ideas so I created an innodb table  
and put some data into it and tried some selects:

(Running on MacBook Pro, 2.0ghz, 1gb RAM, OS X 10.4.7)


mysql> select @@version;
+---------------------+
| @@version           |
+---------------------+
| 5.0.19-standard-log |
+---------------------+
1 row in set (0.00 sec)

mysql> show create table t5;
+------- 
+----------------------------------------------------------------------- 
------------------------------------------------------------------------ 
-------------+
| Table | Create  
Table                                                                    
                                                                         
      |
+------- 
+----------------------------------------------------------------------- 
------------------------------------------------------------------------ 
-------------+
| t5    | CREATE TABLE `t5` (
   `id` int(9) NOT NULL auto_increment,
   `name` varchar(32) default NULL,
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------- 
+----------------------------------------------------------------------- 
------------------------------------------------------------------------ 
-------------+
1 row in set (0.00 sec)


Then I inserted a million rows of random strings:

#!/usr/bin/perl

use DBI;

$db = DBI->connect('DBI:mysql:test', 'test', 'test') || die "Content- 
type: text/html\n\nSorry could not connect to DB<br />";

foreach (1..1000000) {
   $name='';
   foreach (1..30) {$name.=chr(65+rand(25));}
   $db->do('INSERT INTO t5 (name) VALUES ('.$db->quote($name).')');
   print "Now inserting $_\n";
   }


and watched the server while it was inserting.  I tried "SELECT COUNT 
(*)" repeatedly as the table was filled:


mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 16464    |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 27343    |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 63263    |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 118442   |
+----------+
1 row in set (0.05 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 182230   |
+----------+
1 row in set (0.42 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 272427   |
+----------+
1 row in set (0.28 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 407541   |
+----------+
1 row in set (0.60 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 506970   |
+----------+
1 row in set (0.56 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 650197   |
+----------+
1 row in set (0.58 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 756860   |
+----------+
1 row in set (0.79 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------- 
+-----------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State  |  
Info                                                            |
+----+------+-----------+------+---------+------+-------- 
+-----------------------------------------------------------------+
| 37 | test | localhost | test | Query   | 0    |        | show  
processlist                                                |
| 38 | test | localhost | test | Query   | 0    | update | INSERT  
INTO t5 (name) VALUES ('BJWCKTSWFNIJCFKMUIKFPTUMEIAFDG') |
+----+------+-----------+------+---------+------+-------- 
+-----------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------- 
+-----------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State  |  
Info                                                            |
+----+------+-----------+------+---------+------+-------- 
+-----------------------------------------------------------------+
| 37 | test | localhost | test | Query   | 0    |        | show  
processlist                                                |
| 38 | test | localhost | test | Query   | 0    | update | INSERT  
INTO t5 (name) VALUES ('AICBWBYTSUXGKMKQCBDKAJSPIKETAV') |
+----+------+-----------+------+---------+------+-------- 
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 885597   |
+----------+
1 row in set (1.02 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 1000000  |
+----------+
1 row in set (0.85 sec)

mysql> select max(id) from t5;
+---------+
| max(id) |
+---------+
| 1000000 |
+---------+
1 row in set (0.01 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 1000000  |
+----------+
1 row in set (0.72 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 1000000  |
+----------+
1 row in set (0.71 sec)


Finally, just to make sure there wasn't anything funny going on with  
caching, I stopped and restarted the server and did the same queries  
again:

mysql> select max(id) from t5;
+---------+
| max(id) |
+---------+
| 1000000 |
+---------+
1 row in set (0.01 sec)

mysql> select count(*) from t5;
+----------+
| count(*) |
+----------+
| 1000000  |
+----------+
1 row in set (0.70 sec)


Thus, if you're not going to be deleting rows from the table,  
selecting the MAX() of an AUTO INCREMENT field should be a lot faster  
than a COUNT(*) and will give the same answer.  But in any case, 20  
seconds for a table with only a million rows seems rather high.

Finally, I wondered how this would compare to MyISAM table  
performance.  I created a table ("T6") from the innodb table as  
follows (MyISAM is configured as the default database type on this  
MySQL).  Note that it took only 2.31 seconds to create the table and  
put a million rows in it!


mysql> create table t6 select * from t5;
Query OK, 1000000 rows affected (2.31 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> show create table t6;
+------- 
+----------------------------------------------------------------------- 
-----------------------------------------------------------+
| Table | Create  
Table                                                                    
                                                   |
+------- 
+----------------------------------------------------------------------- 
-----------------------------------------------------------+
| t6    | CREATE TABLE `t6` (
   `id` int(9) NOT NULL default '0',
   `name` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------- 
+----------------------------------------------------------------------- 
-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from t6;
+----------+
| count(*) |
+----------+
| 1000000  |
+----------+
1 row in set (0.00 sec)

mysql> select max(id) from t6;
+---------+
| max(id) |
+---------+
| 1000000 |
+---------+
1 row in set (0.43 sec)

mysql> select max(id) from t6;
+---------+
| max(id) |
+---------+
| 1000000 |
+---------+
1 row in set (0.43 sec)


But then I realized that the "CREATE TABLE... SELECT..." syntax  
doesn't get the constraints or indexes from the old table, so I added  
a primary key index and did the same queries again:

mysql> alter table t6 add primary key(id);
Query OK, 1000000 rows affected (4.74 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> select max(id) from t6;
+---------+
| max(id) |
+---------+
| 1000000 |
+---------+
1 row in set (0.00 sec)

mysql> select count(*) from t6;
+----------+
| count(*) |
+----------+
| 1000000  |
+----------+
1 row in set (0.00 sec)


I wonder if the reason for the 20-second SELECT COUNT(*) which you  
are seeing might not have more to do with memory allocation on the  
server?  Or perhaps Dan's suggestion that the InnoDB primary index  
holds the entire row might be the clue.  How big are your rows?


Note to self: stop going to Starbucks for coffee just before bedtime.


Douglas Sims
Doug@stripped



On Sep 7, 2006, at 12:18 AM, Dan Nelson wrote:

> In the last episode (Sep 07), prasad.ramisetti@stripped said:
>> Hi Dan,
>>
>> Thanks for yur response. Does it makes sense to create an index on a
>> primary key ..as that is my smallest field ?
>
> It might, because in an InnoDB table, your primary index also holds
> your row data.  So it's actually your largest index.  A full scan of a
> secondary index on your primary key may very well run faster than a
> scan of the primary index itself, for the purposes of "SELECT
> COUNT(*)".  Best way to find out is to try it :)
>
> -- 
> 	Dan Nelson
> 	dnelson@stripped
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
RE: problem with InnoDBprasad.ramisetti4 Sep
  • Re: problem with InnoDBDan Nelson4 Sep
RE: problem with InnoDBprasad.ramisetti7 Sep
  • Re: problem with InnoDBChris7 Sep
  • Re: problem with InnoDBDouglas Sims7 Sep
  • Re: problem with InnoDBDan Nelson7 Sep
    • Re: problem with InnoDBDouglas Sims7 Sep
RE: problem with InnoDBprasad.ramisetti7 Sep
  • Re: problem with InnoDBJochem van Dieten7 Sep
RE: problem with InnoDBprasad.ramisetti7 Sep
  • Re: problem with InnoDBPaul McCullagh7 Sep
    • Re: problem with InnoDBGabriel PREDA10 Sep