In the last episode (Sep 28), Steven Fletcher said:
> Hi all.
>
> I have a table containing about 800,000 companies of the format shown
> below. We will be performing searches on the table using LIKEs as shown
> below (searching for a "Cat Shop" in Bolton):
>
> Select * From companies Where ( (company LIKE '%cat%' OR type LIKE
> '%cat%') OR (company LIKE '%shop%' OR type LIKE '%shop%') ) AND ( add1
> LIKE '%bolton%' OR add2 LIKE '%bolton%' OR add3 LIKE '%bolton%' OR add4
> LIKE '%bolton%' OR district LIKE '%bolton%' OR town LIKE '%bolton%' OR
> county LIKE '%bolton%' )
Ow ow ow. Indexes can't help you when you use LIKE '%word%'. The
usual way to fix this is to build a "Words" table, and then build
linking tables that link a word to a particular field in your Companies
table.
So you'd have
Companies table:
Id | Company | Type | Add1 | Add2 | Add3 | Town |
---+------------+------+---------------+------+------+--------+
1 | Bob's cats | Shop | 123 Main St. | | | Bolton |
Id | word Words table
---+-----
0 | bob Two unique indexes, one on ID, one on Word
1 | cats
2 | shop
3 | 123
4 | main
5 | bolton
WordID | CompID | Field Words_Companies table
-------+--------+------
0 | 1 | comp Make field an ENUM type with the all of
1 | 1 | comp your fields in Companies as values.
2 | 1 | type
3 | 1 | add1 unique Index (Compid,Field)
4 | 1 | add1 unique index (wordid)
5 | 1 | town
Then you can do queries like
Select c.* FROM Companies c, Words w1, Words w2, Words w3,
Words_Companies wc1, Words_Companies wc2, Words_Companies wc3
where w1.word="cat" and w1.id=wc1.wordid and
wc1.field in ("comp","type") and c.id=wc1.compid
and w2.word="shop" and w2.id=wc2.wordid and
wc2.field in ("comp","type") and c.id=wc2.compid
and w3.word="bolton" and w3.id=wc3.wordid and
wc3.field in ("add1","add2","add3","add4","dist","town","county") and c.id=wc2.compid
Every time you change a record in the Companies table you will have to
handle updating the other tables, but you will get extremely fast
searches.
--
Dan Nelson
dnelson@stripped
