What is the fastest way to get the number of rows of innodb tables in mysql 4.0?

Question

MySQL 4.0 does not have information_schema, and the db ' show table status gives an approximate row count for innodb tables.

So what is the fastest way to get innodb table counts, of course, except for count (*), which can be slower with large tables.

Answer 1

Update

When using InnoDB, the only exact number of rows in the entire table is COUNT (*). Since your upgrade from 4.0 to 5.0 will only happen once, you just have to deal with speed.

Answer 2

! ! -.

CREATE TABLE meta (
`name` char(32) NOT NULL ,
`value_int` int ,
unique (name)
) ENGINE = INNODB;
insert into meta (name, value_int) values ('mytable.count', 0);

set delimiter |

CREATE TRIGGER mytablecountinsert AFTER INSERT ON mytable
    FOR EACH ROW BEGIN
        update meta set value_int=value_int+1 where name='mytable.count';
    END;
|

CREATE TRIGGER mytablecountdelete AFTER DELETE ON mytable
    FOR EACH ROW BEGIN
        update meta set value_int=value_int-1 where name='mytable.count';
    END;
|

Answer 3

MySQL. , InnoDB SELECT COUNT (*). , InnoDB , , .

:

1) , SELECT MAX (id) .

2) , , ( , , ). , , count (*) deleted_rows SELECT max (id) not_deleted.

3) . .

Here's a pretty technical discussion on this issue: http://mysqlha.blogspot.com/2009/08/fast-count-for-innodb.html

Answer 4

Well, using *it is definitely not optimal, but as soon as 1 column. I usually use a column idto count the number of rows.