MySQL获取innodb表中的行数 [英] MySQL get the number of rows in an innodb table

问题描述

我有一个使用innodb的表.我知道该表大约有8900万行.使用

I have a table using innodb. I know the table has roughly 89 million rows. Using

SELECT COUNT(*) FROM table;

大约需要五分钟才能运行.我知道innodb没有针对无条件COUNT(*)查询进行优化.如何重组查询以更快地进行计数?只是添加WHERE 1 = 1会起作用，还是我需要查询特定字段?

takes about five minutes to run. I know that innodb is not optimized for unconditional COUNT(*) queries. How can I restructure the query to give me a count faster? Would just adding WHERE 1=1 work, or do I need to query a specific field?

我知道我可以使用SHOW TABLE STATUS获得大约的行数，但是我想在PHP脚本中获得该值，并且似乎有很多东西可以使用该方法进行挖掘.

I know I can get the approximate number of rows using SHOW TABLE STATUS, but I want to get the value in a PHP script, and it seems like there is a lot to dig through using that method.

推荐答案

如果您对估算的数字还可以，并且不想在PHP中运行SHOW TABLE STATUS，可以使用information_schema数据库:

If you are OK with the estimated number and just don't want to mess with running SHOW TABLE STATUS from PHP, you can use the information_schema DB:

SELECT TABLE_ROWS FROM information_schema.tables
WHERE TABLE_SCHEMA = 'my_db_name' 
AND TABLE_NAME = 'my_table_name';

这篇关于MySQL获取innodb表中的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！