MySQL simple but slow query-CodePudding

I have a table TEST with two columns:

A varchar(250)
B tinyint(1)

The table has about 4 million rows. A contains UTF8 strings, B can only be 0 or 1.

select count(1) from TEST is very fast (as of MySQL Workbench 0,000 sec), but select count(1) from TEST where B=1 takes about 15 seconds (on a quite fast machine, but on a real table with more columns that should not matter for this problem). Adding an index for B did not help - it still makes a full table scan. Forcing the index usage did not help neither.

The storage engine is MyISAM and because there are much, much more selects than inserts/updates, this is probably the best choice.

How can this query be speeded up?

CodePudding user response：

Sad to say, you have presented a nasty problem. There's no magic that can avoid a full table or full index scan for the query you showed us.

Suggestions to improve the situation involve using some sort of summary table. In some other RDBMS SQL this would be called a "materialized view." For example you could create this table:

CREATE TABLE tablecounts AS
SELECT COUNT(*) num, B
  FROM mytbl
 GROUP BY B;
ALTER TABLE tablecounts ADD UNIQUE INDEX B (B);

Then once in a while, maybe from a recurring EVENT, you update it:

INSERT INTO tablecounts (num, B)
SELECT COUNT(*) num, B
  FROM mytbl
 GROUP BY B
    ON DUPLICATE KEY UPDATE SET num=VALUES(num);

Then your app can do SELECT num FROM tablecounts WHERE B=1 to get the count you mentioned in your question.

This isn't a perfect solution. This count will, of course, become stale as other parts of your application INSERT, UPDATE, or DELETE rows in your original table until you run the event. But you'll get an approximate count very quickly.

CodePudding user response：

B-tree indexes are most effective for high-cardinality data (i.e. columns with many possible values, where the data in the column is unique or almost unique). B-tree indices are bad on such a small cardinality columns. create bitmap index.

Bitmap Indexing is a special type of database indexing that uses bitmaps. This technique is used for huge databases, when column is of low cardinality and these columns are most frequently used in the query.

In your case there are just two values for B column so,low cardinality then use bitmap index use this

CREATE BITMAP INDEX Index_Name ON TEST (B);