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
[EMAIL PROTECTED]
On Sep 7, 2006, at 12:18 AM, Dan Nelson wrote:
In the last episode (Sep 07), [EMAIL PROTECTED] 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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
