Geek Answers Handbook

Why is the counter (*) a slow query on some tables, but not on others?

I have a mysql database running on a Wamp server that I use to frequently tag Flickr data templates. As I loaded the data into the database, I ran a counting query to determine how many images I had already uploaded. I was surprised that it took 3 minutes 49 seconds to

select count(*) from image;

In a separate table "concept" I keep a list of tags that users give to their images. A similar query in the table "concept" took 0.8 seconds. The secret is that both tables have about 200,000 rows. select count(*) from image;returns 283,890, and select count(*) from concept;returns 213,357.

Here is a description of each table

Screenshot of mysql console with table descriptions

, "image" . , , , "" , , , .

SELECT table_name AS "Tables", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
ORDER BY (data_length + index_length) DESC;

"image" - 179,98 , "" - 15,45 .

mysql 64 , . , ? ?

+4
2

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

. , image 1973 ( ). 8 (16k), 35 486 . comcept 257 . 63 , 3,386 . , .

, .

, , , . - MySQL 15/16, , , .

​​ SELECT COUNT(*) . , .

, () .

, rows , :

SHOW TABLE STATUS LIKE 'image'


EXPLAIN SELECT COUNT(*) FROM image
+6

, , show table status . InnoDB, , , , .

+1

More articles:


All Articles