If you’ve been reading enough database related forums, mailing lists or blogs you probably heard complains about MySQL being unable to handle more than 1.000.000 (or select any other number) rows by some of the users. On other hand it is well known with customers like Google, Yahoo, LiveJournal,Technocarati MySQL has installations with many billions [...]
Indexes in MySQL
June 2, 2006 By 29 Comments
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 [...]
