Prefix index, which means that the first n characters are generally selected as the index for a field of type Varchar/text/blob. Indexing a long character column can make the index big and easy. Some of the characters at the beginning of the index, which can greatly save the index space and thus improve the efficiency of the index, but this also reduces the selectivity of the index. The selectivity of an index is the ratio of non-repeating index values (also known as cardinality, cardinality) and the total number of records in a data table.
Calculation formula:
SELECT COUNT (DISTINCT (a))/count (*) from table1;
SELECT COUNT (DISTINCT (UUID), 2))/count (1) as Ind2,
COUNT (DISTINCT ((UUID), 3))/count (1) as Ind3,
COUNT (DISTINCT ((UUID), 4))/count (1) as Ind4,
COUNT (DISTINCT ((UUID), 5))/count (1) as Ind5,
COUNT (DISTINCT ((UUID), 6))/count (1) as Ind6,
COUNT (DISTINCT (left (UUID), 7))/count (1) as Ind7
from Table_a;
When selecting =5, the index length is 5[effect closer to 1]
How to Fugasso the last few words of a field instead of adding an entire field
There is a field in the table Image_url, want to index him but this field has a lot of content is repeated as follows:
Image_url
Http://img1.douban.com/lpic/s10419031.jpg
Http://img1.douban.com/lpic/s10419532.jpg
Http://img2.douban.com/lpic/s10412033.jpg
Http://img2.douban.com/lpic/s10412034.jpg
Would it be a problem to index the whole field?
Can you just give this field the following 13 characters Fugasso, because these 13 characters characters is my search data?
Plus, what do you pay attention to when you use this index later?
As far as I know, MySQL does not have a suffix index (please correct me if it is wrong);
If the truth is to add an index to this field and do not want the whole field, the program may be a little more than a step.
Create a field image_url prefix index by your number of characters. Reverse word nginx storage when accessing this field. The results are as follows:
gpj.13091401s/cipl/moc.nabuod.1gmi//:p TTH
When you look at the query, reverse it.
SELECT COUNT (DISTINCT (Left (REVERSE (msg_id), 2))/count (1) as Ind2,
COUNT (DISTINCT (Left (REVERSE (msg_id), 3))/count (1) as Ind3,
COUNT (DISTINCT (Left (REVERSE (msg_id), 4))/count (1) as Ind4,
COUNT (DISTINCT (Left (REVERSE (msg_id), 5))/count (1) as Ind5,
COUNT (DISTINCT (Left (REVERSE (msg_id), 6))/count (1) as Ind6,
COUNT (DISTINCT (Left (REVERSE (msg_id), 7))/count (1) as Ind7
from Table_a;
When =6 is selected, the index length is 6
The inverse function is used because the previous few are the same, so the data is written back into the table when it is built, and it can be reversed using reverse.
0929mysql prefix index How to find the right number of digits