CODEWITHSUNDEEP
mysqlsqldatabaseindexing
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

How to improve count performance without indexing more fields?

There are over 2 millions record in the table.

I want to count how many errors (with checked) in the table and how many has been checked.

I do two queries:

SELECT count(*) as CountError FROM table WHERE checked = 1 AND error != ''

-

SELECT count(*) as Checked FROM table WHERE checked = 1

The performance is really slow, it take about 5 mins to get the result. How to improve this?

I have already have index on status field for the UPDATE performance.

If I index on checked field - then UPDATE performance will be effected which I do not want that.

UPDATE happen more than SELECT.

The table are Innob

Upvotes: 1

Views: 187

Answers (2)

Guffa
Guffa

Reputation: 700222

You can try if making both counts in the same query is faster:

select
  count(*) as CountError,
  sum(case when error != '' then 1 else 0 end) as Checked
from table
where checked = 1

However, the difference will probably not be much to talk about. If you really want a difference then you need to add an index. Consider what the impact really would mean, and make an actual test to get a feel for what the impact could really be. If the update gets 10% slower and the select gets 100000% faster, then it might still be worth it.

Upvotes: 3

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

Your problem here is simply that your checked field is either 1 or 0 which means that MySQL needs to do a table scan even though you have a key as it's unable to efficiently determine where the split between 0 and 1 is, especially on large amounts of rows.

The main advisory I would offer is the one which you don't want, which is to index checked as then SELECT SUM(checked) AS Checked FROM table WHERE checked=1 would be able to use the index without hitting the table.

Ultimately though, that's not a trivial query. You may wish to look at some way of archiving counts. If you have a date or timestamp then you could set up a task daily which would could store the count(*)'s for the previous day. That in turn would leave you fewer rows to parse on-the-fly.

Without further information as to the exact purpose of this table, the reason why you won't allow an index on that column etc. it is hard to suggest anything more helpful than the above + throwing hardware at it.

Upvotes: 0

Related Questions