Member Avatar for flashyflashy
flashyflashy 0 Newbie Poster

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.

  • 1 Contributor
  • 0 Replies
  • 131 Views

Dani AI

Generated

The idea from will work in principle: creating a small, leftmost key to narrow the search is what B-tree indexes are for. But there are easier, cheaper and more predictable ways than adding three separate 1-char columns.

MySQL lets you index a prefix of a string column, which gives the same narrowing effect without duplicating data. For short, mostly-unique permlinks you can also keep a full index. Important points:

  • A prefix index stores only bytes (not “characters” for multibyte charsets), so choose length with charset in mind.
  • InnoDB secondary indexes contain the primary key too, so index size and I/O cost include that overhead.
  • Very short prefixes (e.g., first character) usually have poor selectivity and cause many row reads; choose the smallest prefix that yields good distinctness.

Quick, practical workflow to decide and test:

  • Measure distinctness for different prefix lengths:
    SELECT COUNT(DISTINCT LEFT(permlink, N)) FROM your_table;
  • Check current index stats:
    SHOW INDEX FROM your_table;
ANALYZE TABLE your_table;
  • Try a prefix index and confirm with EXPLAIN:
    CREATE INDEX idx_perm_pref ON your_table (permlink(3));
EXPLAIN SELECT ... WHERE permlink = 'your_value';
  • If you prefer not to rely on prefix bytes or want an indexed column name, use a generated column and index it (MySQL 5.7+):
    ALTER TABLE your_table
ADD COLUMN permlink_pref VARCHAR(3) GENERATED ALWAYS AS (LEFT(permlink,3)) STORED,
ADD INDEX(idx_perm_pref(permlink_pref));

Final tips: prefer a prefix index or a generated column over adding redundant a1/a2/a3 columns. Always verify with EXPLAIN on real queries and benchmark on a copy of production data; if permlinks are highly unique, a full-column index or UNIQUE index may be the simplest and fastest choice.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.