Hello.
InnoDB does not keep an internal count of rows in a table. (This would actually be somewhat complicated because of multi-versioning). To process a SELECT COUNT(*) FROM T statement, InnoDB must scan an index of the table, which will take some time if the table is not entirely in the buffer pool. See: http://dev.mysql.com/doc/mysql/en/InnoDB_restrictions.html Julian Legeny <[EMAIL PROTECTED]> wrote: > 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 > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
