Julian,
maybe the secondary index tree LOGIN_NAME is so small that it fits completely in the buffer pool? Then there is no file I/O, and the scan is faster.
For this reason, COUNT(secondary_index_column) can be faster than COUNT(*), which is performed on the PRIMARY, clustered index.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php
Order MySQL technical support from https://order.mysql.com/
----- Original Message ----- From: "Julian Legeny" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Thursday, November 18, 2004 5:22 PM
Subject: Poor performance for executing SELECT COUNT() for 50 000 records
Hello,
I'm using MySQL 4.0.20a.
I have a question about index performance for sql query SELECT COUNT(*) or
SELECT COUNT(ID) or SELECT COUNT(LOGIN_NAME) for huge data (50.000 records).
I have table BF_USER:
CREATE TABLE BF_USER ( ID INTEGER NOT NULL AUTO_INCREMENT, DOMAIN_ID INTEGER NOT NULL, FIRST_NAME VARCHAR(50) NOT NULL, LAST_NAME VARCHAR(50) NOT NULL, EMAIL VARCHAR(50) NOT NULL, LOGIN_NAME VARCHAR(30) NOT NULL, PASSWORD VARCHAR(30) NOT NULL, MODIFICATION_DATE TIMESTAMP NOT NULL, CREATION_DATE TIMESTAMP NOT NULL DEFAULT 0, CONSTRAINT BF_USR_PK PRIMARY KEY (ID), CONSTRAINT BF_USR_LOGIN_UQ UNIQUE (LOGIN_NAME), CONSTRAINT BF_USR_EMAIL_UQ UNIQUE (EMAIL), INDEX IND_USER_DOMAIN_ID (DOMAIN_ID), CONSTRAINT BF_USR_FK FOREIGN KEY (DOMAIN_ID) REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE ) TYPE=INNODB;
And index on FK column DOMAIN_ID and unique column LOGIN_NAME:
CREATE INDEX LST_LOGIN_NAME ON BF_USER (DOMAIN_ID, LOGIN_NAME);
After creating this table and index, there will be created following indexes:
PRIMARY - index on key ID (automatically created)
LOGIN_NAME - unique index on column LOGIN_NAME (automatically created)
EMAIL - unique index on column EMAIL (automatically created)
LST_LOGIN_NAME - combined index on columns DOMAIN_ID and LOGIN_NAME.
(created manually)
I fill table BF_USER by 50.000 records, there are some differencies between time durations for executing SELECT COUNT() command. I have try 3 types of COUNT():
1.) SELECT COUNT(*) FROM BF_USER; --------------------------------- - I have execute this command 3-times (on 50.000 items) and here are time durations of this select:
Query 1 elapsed time (seconds) - SQL query: 1,281 Query 1 elapsed time (seconds) - SQL query: 1,407 Query 1 elapsed time (seconds) - SQL query: 1,375
When I EXPLAIN this query, i can see: type : Index possible_keys : <null> key : PRIMARY key_len : 4 extra : Using index
- it means, there is used PRIMARY index, but I thing, there is poor performance for this query.
2.) SELECT COUNT(ID) FROM BF_USER; ---------------------------------- - I have execute this command 3-times (on 50.000 items) and here are time durations of this select:
Query 1 elapsed time (seconds) - SQL query: 1,313 Query 1 elapsed time (seconds) - SQL query: 1,484 Query 1 elapsed time (seconds) - SQL query: 1,343
When I EXPLAIN this query, i can see: type : Index possible_keys : <null> key : PRIMARY key_len : 4 extra : Using index
- it means, there is used PRIMARY index, but I thing, there is poor performance for this query. It is the same as SELECT COUNT(*)
3.) SELECT COUNT(LOGIN_NAME) FROM BF_USER; ------------------------------------------ - I have execute this command 3-times (on 50.000 items) and here are time durations of this select:
Query 1 elapsed time (seconds) - SQL query: 0,437 Query 1 elapsed time (seconds) - SQL query: 0,063 Query 1 elapsed time (seconds) - SQL query: 0,062
When I EXPLAIN this query, i can see: type : Index possible_keys : <null> key : LOGIN_NAME key_len : 30 extra : Using index
- it means, there is used LOGIN_NAME index, and also performance is MUCH BETTER as for count(*)/count(id). First time it takes more time but each following is very fast.
My question is, WHY there is better performance for executing COUNT(LOGIN_NAME) as for COUNT(ID). Both commands are used indexes I don't understand why index for ID is too slow. Is there a possibility speed up SELECT COUNT(*) ?
Thank you for your advice, with best regards,
Julian Legeny
--
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]
