There is a table have 2 indexes, called state and CATEGORY, which has a very low cardinality respectively 4 and 24 within 7,110,590 rows in the table.
When running query of select statements that includes above 2 indexes in where clause, an optimizer try to index merge, Using intersect(state,CATEGORY), that is less efficient than even table scan, takes about 20 sec.
Is there any way to except a index if the cardinality of the index is lower than a specific directed number when an optimizer is about to make a execution plan?
The database is MariaDB-1:10.6.11 Community version
But other databases which has lower version than previous mentioned database server, 10.3.28-MariaDB, handles the same query in 5ms. Servers of older version run a table scan using high cardinality index(date). All mentioned servers are same replicas of a source database with slightly different versions. Depends on difference of their version, their optimizers create query that have a lot of difference in performance.
What factors could bring that difference?
Full explain extended in newer version having low performance
id 1 select_type SIMPLE table that_table type index_merge possible_keys CATEGORY,state key state,CATEGORY key_len 6,152 ref NULL rows 1778388 filtered 100 Extra Using intersect(state,CATEGORY); Using where; Using filesort
Full explain extended in old version having good performance
id 1 select_type SIMPLE table that_table type index possible_keys CATEGORY,state key DATE key_len 62 ref NULL rows 18 filtered 100 Extra Using where
Below is indexes on the table
# cardinality: 24, `CATEGORY` VARCHAR(50) DEFAULT '' NOT CREATE INDEX CATEGORY ON that_table (CATEGORY) ; # cardinality: 4, `state` VARCHAR(1) DEFAULT 'Y' NULL; CREATE INDEX state ON that_table (state); # cardinality: 7,110,590, `DATE` VARCHAR(20) DEFAULT '' NOT NULL; CREATE INDEX DATE ON that_table (DATE);
The problematic query:
EXPLAIN EXTENDED SELECT * FROM that_table WHERE category = 'blabla' AND state = 'Y' ORDER BY date DESC LIMIT 9;
A definition of table
CREATE TABLE `that_table` ( `UID` int(10) NOT NULL AUTO_INCREMENT, `state` varchar(1) DEFAULT 'Y', `CATEGORY` varchar(50) NOT NULL DEFAULT '', `DATE` varchar(20) NOT NULL DEFAULT '', # .... other columns PRIMARY KEY (`UID`), KEY `DATE` (`DATE`), KEY `CATEGORY` (`CATEGORY`), KEY `state` (`state`), # ... other indexes );