Timeline for MySQL - Add index to low cardinality text column?
Current License: CC BY-SA 3.0
5 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 25, 2015 at 16:29 | comment | added | gview | I probably should have written "innodb block cache" rather than result set cache, but the idea is the same. The smaller the dataset, and less indexes involved the faster the db performs. This is why people avoid low cardinality indexes -- because they eat up valuable storage and are frequently not used. I don't disagree with the gist of your post, but it could easily be interpreted to mean "create low cardinality indexes -- just in case". | |
| Jun 24, 2015 at 22:43 | comment | added | Michael - sqlbot | @gview There is no intention to imply that indexes are somehow without cost. Indexes, of course, require space to exist and resources to maintain. The example is an attempt to dispel the pervasive myth that low cardinality indexes in MySQL are not worth their cost because the optimizer won't use them... and for exactly that reason, it is a deliberately extreme example, with an index "less useful" than OP's question contemplates. Yes, indexes consume space in the InnoDB buffer pool, but that memory is not shared with the query cache, which is the only place MySQL caches actual result sets. | |
| Jun 24, 2015 at 20:11 | comment | added | gview | Seems like a pretty misleading example. How often do people query against a multi million row table where the desired result is half the table? Add another more typical query with higher cardinality and does the optimizer choose to use that index? Your answer implies that indexes have no cost, when in fact they eat up space which is detrimental to the effectiveness of result set caching. | |
| Oct 25, 2013 at 4:20 | comment | added | Mike Purcell | Nice. Appreciate the write-up. I actually ended up converting the enums to varchars, then added the indices. Took some refactoring on the lower level api, but seems to work well. | |
| Oct 25, 2013 at 1:54 | history | answered | Michael - sqlbot | CC BY-SA 3.0 |