Timeline for Possible INDEX on a VARCHAR field in MySql
Current License: CC BY-SA 3.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 15, 2020 at 9:05 | history | edited | CommunityBot |
Commonmark migration
|
|
| Jun 13, 2020 at 16:24 | comment | added | RolandoMySQLDBA | @Pacerier once you create your own stopword list, you will have to recreate your Fulltext Indexes, just be prepared for them to be bigger because of allowing for more words. | |
| Jun 13, 2020 at 16:20 | comment | added | RolandoMySQLDBA | @Pacerier The standard stopword list that is built-in for MyISAM has 543 words. They will never enter in the FULLTEXT index (dev.mysql.com/doc/refman/8.0/en/…). The same goes for the 36 built in words for InnoDB (dev.mysql.com/doc/refman/8.0/en/…). What I recommend it to create your own stopword list. I usually use 'a','an', and 'the' in my stopword lists (See my StackOverlow post stackoverflow.com/a/6092216/491757) | |
| Jun 13, 2020 at 15:14 | comment | added | Pacerier | @Rolando, re "MySQL will not index 543 words"; Meaning? | |
| Feb 14, 2019 at 10:17 | comment | added | Erenor Paz | @Mike You should post a question on your own, maybe adding a link to this thread to add completeness to what you're asking :-) | |
| Apr 3, 2018 at 12:09 | comment | added | Accountant م |
Is this left-oriented like search like 'a%' ?
|
|
| May 23, 2017 at 12:40 | history | edited | CommunityBot |
replaced http://stackoverflow.com/ with https://stackoverflow.com/
|
|
| May 18, 2017 at 15:51 | comment | added | Michael | I apologise as this is an old thread, but my question related directly to this but I am not able to get a clear answer for my needs from reading over the above and other similar articles. My scenario is: I'm developing a very rudimentary stock system which consists of only one table for now. It is accessed externally through an API so all the configuration is held elsewhere - which is the reason why we only need a single table. The two columns which I am thinking about indexing, would have approximately 200 unique entries each, of length < 20 characters. Should I consider adding indexes? | |
| Apr 13, 2017 at 12:42 | history | edited | CommunityBot |
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
|
|
| Nov 28, 2015 at 14:10 | comment | added | Mr.M | It is not good to use MD5 or SHA1 to produce strings which will be indexed. Distribution of strings produced by hashing functions like MD5 or SHA1 is random in a large space which decreases efficiency of your index, which can slow down INSERT and SELECT statements. Here is post explaining it: code-epicenter.com/… | |
| Aug 5, 2015 at 11:54 | history | edited | Vérace | CC BY-SA 3.0 |
Fixed small, but important, typo.
|
| Jan 15, 2014 at 23:47 | comment | added | atxdba | The hash option here is still a text and 32 bytes for what is really 16 bytes. You can use use a bigint field with conv(left(md5('whatever'),16),16,-10). There's not a 16 byte numeric but you may find half of the md5 sufficient and then it's only 8 bytes in the index | |
| Mar 3, 2013 at 12:54 | comment | added | Mark Tower | I don't have reputation enough to vote your answer up but I must say it was GREAT. Thank you for the explanation and the examples. I think the hash indexing is the best for my case, it is an awesome solution. But still one question: what do you think the limit of rows for fast searches in the table is going to be? [using as KEY the VARCHAR(32) for searches] | |
| Mar 3, 2013 at 12:47 | vote | accept | Mark Tower | ||
| Mar 3, 2013 at 2:57 | history | answered | RolandoMySQLDBA | CC BY-SA 3.0 |