mysql查询视图记录数,如何快速检查许多sql数据库表和视图，以查看它们是否为空或包含记录...

I am working on a SQLite database analysis tool in Java. My current objective is to check to see which tables and views in the database contain no records. My problem is a matter of speed. My biggest slowdown occures when trying to determine if a view is empty. I have tested individual queries against a single view in SQLiteSpy and I can't get the return time down below 1-2 seconds. Scaled up to hundreds of views, that performance is unacceptable to the point of not even bothering with the feature. Using my own code, I can check around 120 tables, with the largest being around 100,000 rows, with an average speed of 3.74ms.

My current query:

SELECT exists(SELECT 1 FROM table LIMIT 1);

As I said, this works fine for my tables, but with the views, it is too slow.

I have also tried the following:

SELECT count(*) FROM table;

SELECT count(1) FROM table;

SELECT 1 FROM table LIMIT 1;

I have also tried selecting a known column with a limit of one and had that take several seconds.

My last resort is to query the tables first and then the views and have a single timeout on the whole process. My test database has around 250 tables and views and each are not very large. My goal is to be able to report which are empty in under 1.5 seconds max. I want to be able to provide a feature like SQLiteSpy where the tables and views in the treeView are marked with either a green plus or a red minus symbol. I just can't figure out how they are able to mark them all so fast.

解决方案

This is SQLite specific.

You could try running the ANALYZE command, then querying the sqlite_statN tables to see which tables have a row count of zero.