| Table with searchable string column [message #4] |
Fri, 11 August 2006 06:16  |
 vadim1508
Messages: 9
Registered: August 2006 |
Junior Member |
|
|
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++';) 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
|
|
|
| Re: Table with searchable string column [message #6 is a reply to message #4 ] |
Fri, 11 August 2006 06:29  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
|
|
| Re: Table with searchable string column [message #20 is a reply to message #11 ] |
Sat, 12 August 2006 09:26 |
vadim1508
Messages: 9
Registered: August 2006 |
Junior Member |
|
|
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?
[Updated on: Sat, 12 August 2006 10:56]
|
|
|
|
| Re: Table with searchable string column [message #23 is a reply to message #22 ] |
Sun, 13 August 2006 09:26 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
|
| Re: Table with searchable string column [message #28 is a reply to message #26 ] |
Sun, 13 August 2006 11:08 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
Full text search indexes are much more expensive. Generally they contain one index entry per word not one per object. So if you have
1.000.000 rows in the table, text in each having 10 words there will be about 10.000.000 entries in full text search index. Full text search indexes are a bit more complicated than that but this is good ballpark figure.
I would simply test how well it works for your application rather than getting too much in theoretical estimates - if you have the data already it is not that hard.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
|
| Re: Table with searchable string column [message #30 is a reply to message #29 ] |
Sun, 13 August 2006 11:32 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
Yes. Thank you for the link.
What is described is a way of ranking documents in MySQL "natural language" full text search. There is also boolean full text search which does not rank documents by relevance.
Honestly I'm not a big fan of this ranking model as it does not give any preference to phrases which may result in pretty bad ranking. For example searching for "Peter Zaitsev" may return documents which have a lot of "Peter" and a lot of "Zaitsev", for example telling story as "Peter Rabbit" met "Vasiliy Zaitsev" but there is nothing about Peter Zaitsev itself.
If you looke about Major search engiens they put very importants to words being in the same phrase or at least close by. They however also use a lot of other factors.
This should not be a big problem in your case of book matching however.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
|
|
Members
Search
Help
Register
Login
Home
