Quote:
Originally Posted by RustyBrooks
Not really. Explain will tell you how many rows are contained in the index. Clearly it's an estimation since the number doesn't match count(*). Although, having thought about it, I can't think of any reason you shouldn't be able to get an exact count of the number of elements in an index without having to count them.
That's postgres. mysql does not have a real equivalent, AFAIK, to explain analyze.
The dev environment is on my local machine. There is no data being pushed into it. Most of the config is stock. I agree that I don't have it optimized.
But there is no reason for 150ms to count(*) on a table of this size. I don't understand why it started behaving better after screwing with it but whatever, even 30ms is pretty poor.
The count(*) slowness is a fairly well known problem in mysql with innodb tables. Changing it to myisam made the count(*) under 1ms, although if you add a where it goes back 30ms or so, which supports the notion that count(*) on innodb tables does something like an index scan
Ah yes. I did get confused about the explain thing... I'm not the largest fan of MySQL either...
I think the first thing I pointed out was that MySQL has had an unfixed memory leak bug for the past 10 years, and it may be possible that this count(*) is a manifestation of that.
Not sure what your precise query is, but a count(*) without a where clause requires a full table scan, and this not a place for indexes to be used. A count(*) with a where clause likely will use an index.
IRT size_of index: indexes only have a mb size, not a precise number size. This is true in Postgres, and this query from MySQL seems to suggest the same:
Code:
select * from information_schema.INNODB_SYS_INDEXES;
I'd also point out that a used index is going to have that ~255 space. An unused index will be at zero (and if old, should be removed). I haven't looked too much, but I'm guessing that "n_fields" is the hard amount of indexes is needed to cover the whole table. If that is the case, a row count for an index is probably 255 / 4 (size of int, size of word (assembly word, not english), and so on), then multiply by n_fields to get a guess.
***
If you aren't too concerned about maximum precision at all times, a trick may be to grab the estimated row size from the information_schema. I've shown results from the estimation and a real table I have. This is from a client I work with about one week a month, and MySQL is never running at other times.
Code:
select table_rows
from information_schema.tables
where table_schema = 'obfuscated_schema_name'
and table_name = 'obfuscated_table_name';
-- => 1500
select count(*) from obfuscated_table_name;
-- => 1523