Table with searchable string column

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I have a table (about 5 columns), which is loaded once and never updated. One of its columns is string (titles up to 100 chars). The content of this column will be searched as the whole value (select * from books where title = ‘Effective C++’:wink: or as a single word (select * from books where title like ‘%C++%’;). The table contains millions raws. Selects are done mostly on this string column.

What is the effective design?
Is creating index for my string column is sufficient? I am doing LOAD DATA LOCAL INFILE. What is faster to create an index while creating a table or first load data without index an only than create an index?
Do I need index for all 100 chars, if most of the time I have no more than 30-40 chars?
Maybe I need FULLTEXT index?

Thanks a lot for your help!

Vadim

2

Vadim,

You should load the data in table with Index it is faster in MySQL as separate index creation does full table rebuild. Plus for MyISAM table LOAD DATA INFILE automatically does index build once your data is loaded.

Now regarding indexing - index on title will help “=” queries and prefix like queries, such as like “C++%” but it will not help LIKE “%word%” Queries.

For such queries using full text search should be good idea.

Speaking about indexing - I would start with indexing full titles - if title is 30 chars long index entry will also only take about 30 chars, or even less for MyISAM tables as key compression is used.

3

Thank you!
If I am using FULLTEXT for my column does the indexing this column becomes redundant?

4

No,

You should have both.

Normal index will be used for = lookup while Full text search for substring matching.

5

Understood. Thank you.
BTW, when I load my db using LOAD DATA LOCAL INFILE command from number of files one after another,does it still worth to define index in initial table definition or it’s better to load all the files without index and only then apply an index?

6

If it’s a large data set remove indexes before you load the file, then alter the table and re-apply the indexes.

7

If you do LOAD DATA INFILE in table with existing indexes answer depends on table type:

For MyISAM Table: Do ALTER TABLE DISABLE KEYS; before the load
Do ALTER TABLE ENABLE KEYS after the load.

If you need table to be always accessible consider using 2 tables and performing load to both of them, swapping which table is active.

For Innodb Table:
Do load in existing table.

Innodb Tables can’t build indexes by sort so it is waste of time to add and recreate indexes.

For MyISAM tables removing/adding index may be faster than loading into table with indexes but ENABLE/DISABLE keys should be even better.

Note: It applies to loading large portions of the table - 5-10%+ for small loads overhead of index rebuild may be too large.

Note2: make sure myisam_max_sort_file_size is large enough otherwise indexes may be rebuilt by keycache which is way too slow.