![]() we should not use typical VARCHAR(255) but rather VARCHAR(191) because in case utf8mb4 and VARCHAR(255) same part of data are stored off-page and you can not create index for column VARCHAR(255) but for VARCHAR(191) you can. with utf8mb4 which is 4-byte and can also store emoticons (we should never more use 3-byte utf8) and we can avoid errors like Incorrect string value: \xF0\x9F\x98\. You can read other ways of calculating number of N in my post Prefixed indexes in MySQL. In this table there are 1637 distinct values multiplied by 6 bytes is about 9KB, and imagine how this number would grow if our table contains million of rows. In table definition column last_name is defined as VARCHAR(16), and this means we have saved 6 bytes (or more if there are UTF8 characters in the last name) per entry. This means that we can make index on column last_name with indexing only first 10 characters. Here are results for N=9 select count(*) as cnt, left(last_name,9) as prefix There are much more occurrences of every prefix, which means we have to increase number N until the values are almost the same as in the previous example. Now we are going to find the most frequently occurring last_name prefixes, beginning with five-letter prefixes. Let's say we want to add column last_name in table employees to the index, and we want to define the smallest number N which will produce the best index selectivity.įirst let us identify the most frequent last names: select count(*) as cnt, last_nameĪs you can see, the last name Baba is the most frequent one. ![]() I will make demonstration on the this database dump. There are two approaches you can do calculations for your database table. Trick is to find the minimal number N which will result to maximal index selectivity. In this scenario IS=2/3=0.66 which means we increased index selectivity, but we have also increased the size of index. Let us now see what will happen if we increase number of indexed characters to two (N=2). In this case, index selectivity is equal to IS=1/3 = 0.33. If we index only the first character (N=1), then index table will look like the following table: +-+-+ Here is one example for test table: +-+-+ Index selectivity is ratio of the total distinct indexed values and total number of rows. The prefix should be long enough to make the index nearly as useful as it would be if you’d indexed the whole column.īefore we go further let us define some important terms. MySQL requires that you define first N characters to be indexed, and the trick is to choose a number N that’s long enough to give good selectivity, but short enough to save space. MySQL disallows indexing a full value of BLOB, TEXT and long VARCHAR columns because data they contain can be huge, and implicitly DB index will be big, meaning no benefit from index. ![]() Reference: MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length To solve this problem, specify a figure less than 256 as the size for VARCHAR field. VARCHAR can only accepts up to 256 characters, so anything such as VARCHAR(512) will force MySQL to auto-convert the VARCHAR(512) to a SMALLTEXT datatype, which subsequently fails with error 1170 on key length if the column is used as primary key or unique or non-unique index. ![]() It happens in a situation such as when you specify VARCHAR column as primary key, but wrongly set its length or characters size. Sometimes, even though you don’t use TEXT or BLOB related type in your table, the Error 1170 may also appear. By default, VARCHAR is limited to a maximum of 255 characters and its limit must be specified implicitly within a bracket right after its declaration, i.e VARCHAR(200) will limit it to 200 characters long only. If you can't do that, and wanting to place a limit on the TEXT or BLOB column, try to use VARCHAR type and place a limit of length on it. The solution to the problem is to remove the TEXT or BLOB column from the index or unique constraint or set another field as primary key. The error will also pop up when you try to convert a table column from non-TEXT and non-BLOB type such as VARCHAR and ENUM into TEXT or BLOB type, with the column already been defined as unique constraints or index. However, MySQL doesn’t support a key length limit on TEXT or BLOB. So, when using BLOB or TEXT types as an index, the value of N must be supplied so that MySQL can determine the key length. With full BLOB or TEXT without the length value, MySQL is unable to guarantee the uniqueness of the column as it’s of variable and dynamic size. ![]() So The error mainly happens when there is a field/column type of TEXT or BLOB or those belong to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make a primary key or index. The error happens because MySQL can index only the first N chars of a BLOB or TEXT column. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |