MySQL simple but slow query

user_6212907 0
Viewed 164

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?

indexing query-optimization mysql
2 Answers

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.

Edit Why is simply counting all your rows so miraculously fast? @BillKarwin explained that in his comment. You use the MyISAM storage engine, which maintains the table's overall row count in its metadata and so can satisfy SELECT COUNT(*) FROM mytbl instantaneously. Once you add a WHERE clause it needs to scan all rows.

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.

Edit If you actually need the detailed results of SELECT A FROM tbl WHERE B = 0 an index on (B, A) will help your performance by only scanning the relevant rows. If most of your rows have B = 1 the index will help a lot by giving MySql a way to ignore them completely.

user_2056080

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);
user_62856000
Related