I have a table with 15,00,000 rows which has a column PERMLINK which is used in 'where' clause of my select queries. To speed up query I have used it as INDEX. What size of index is optimum for names.?
and I have a new idea but don't know how mysql really works.
I'm thinking of adding 3 columns a1,a2,a3 which are to contain first 3 characters of permlink and use new index (a1,a2,a3).
Consider the table below
ID permlink
1 aaa
2 aab
3 aac
4 aba
5 abb
6 abc
7 aca
8 acb
9 acc
10 baa
11 bab
12 bac
13 bba
14 bbb
15 bbc
16 bca
17 bcb
18 bcc
19 caa
20 cab
21 cac
22 cba
23 cbb
24 cbc
25 cca
26 ccb
27 ccc
If I set permlink as index and of size 1, if I search for value bbb, mysql will start searching for character b, then b, then b thus searches 9 rows+2 rows=11 rows.
ID prm a1 a2 a3
1 aaa a a a
2 aab a a b
3 aac a a c
4 aba a b a
5 abb a b b
6 abc a b c
7 aca a c a
8 acb a c b
9 acc a c c
10 baa b a a
11 bab b a b
12 bac b a c
13 bba b b a
14 bbb b b b
15 bbc b b c
16 bca
17 bcb
18 bcc
19 caa
20 cab
21 cac
22 cba
23 cbb
24 cbc
25 cca
26 ccb c c b
27 ccc c c c
If I use index of (a1,a2,a3) pointer directly jumps to row 10 and then goes to row 13 and 14. thus just three rows to check.
I'm not sure if it works this way, I'm relatively new to mysql structure and operations. Put some light on.