MySQL does not always make a right decision about indexes usage. Condsider a simple table:
1 2 3 4 5 6 |
CREATE TABLE `t2` ( `ID` int(11) default NULL, `ID1` int(11) default NULL, `SUBNAME` varchar(32) default NULL, KEY `ID1` (`ID1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
1 |
SELECT COUNT(*) FROM t2 |
; 250001 (V1)
1 |
SELECT COUNT(*) FROM t2 WHERE ID1=1 |
; 83036 (V2) (execution time = 110 ms) That is index selectivity by condition (ID1=1) is V2/V1 = 0.3321 or 33.21% It is said (e.g. book “SQL Tuning”) if selectivity over 20% then a full table […]
Read More