0

I use MySQL, too late until I realized that that queries took too long to respond needed required columns to be indexes.
I understand the way that indexes work, but I'm wondering if indexes could be implemented in a way that COUNT(*) or COUNT(col1) where col1 is indexed, would be as fast as executing 'WHERE COL=007'? I ask this because in my application I often need to make the count query. Decreasing the response would be much needed for a faster operation flow.

Shariq
  • 513
  • 4
  • 14
  • What is your query? Having a `count(*)` in the select portion should not prevent an index from being used. – CptMisery Feb 14 '17 at 20:16
  • You might want to look at this: http://stackoverflow.com/questions/286039/get-record-counts-for-all-tables-in-mysql-database – David Cram Feb 14 '17 at 20:16
  • MySQL doesn't use indexes on count or sum target columns. It has to iterate on each row for providing the results. – Lelio Faieta Feb 14 '17 at 20:26

1 Answers1

1

You say you understand how indexed work. But I think that is also a question of how aggregate functions work.

Lets say you make a

SELECT count(col) FROM table;

What does it do? Probably it presents every col from that table to count and count does a

counter++;

somewhere. That is a rather fast operation.

The speed of your count query depends on how fast each row can be presented to the count function. The index on col does not affect the speed because each row of table has to be scanned anyway. Indexes are only a means to find certain rows faster than a complete table scan.

On the other hand, a fast query like

SELECT count(col) FROM table WHERE col="rare value";

is fast because the 

SELECT col FROM table WHERE col="rare value";

is fast as well.

I hope this makes sense to you. Otherwise, why don't you provide us with some more detail and we try and pimp a real-life query?

nCessity
  • 735
  • 7
  • 23
  • `COUNT(*)` can be evaluated in *O(1)*, from table metadata. The table knows how many rows it has. It doesn't require a scan. Or an index. – user207421 Feb 14 '17 at 21:07
  • Maybe with your MySQL Version. I am using 5.6.35 which is not too old. And I get `SELECT count(*) FROM countTest; +----------+ | count(*) | +----------+ | 500000 | +----------+ 1 row in set (0,12 sec)` which doesn't seem to be O(1) – nCessity Feb 15 '17 at 10:00
  • https://dev.mysql.com/doc/internals/en/optimizer-group-by-related-conditions.html says: "If the table handler has a quick row-count available, then the query `SELECT COUNT(*) FROM Table1; ` gets the count without going through all the rows. This is true for MyISAM tables, but not for InnoDB tables." – nCessity Feb 15 '17 at 10:32