List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:September 28 1999 3:44pm
Subject:Re: Speed up LIKE or other solution?
View as plain text  
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
Thread
Speed up LIKE or other solution?(Steven Fletcher)28 Sep
  • Re: Speed up LIKE or other solution?Bob Kline28 Sep
    • Re: Speed up LIKE or other solution?(Steven Fletcher)28 Sep
      • Re: Speed up LIKE or other solution?Bob Kline28 Sep
        • Re: Speed up LIKE or other solution?(Steven Fletcher)28 Sep
        • Re: Speed up LIKE or other solution?Michael Widenius29 Sep
  • Re: Speed up LIKE or other solution?Dan Nelson28 Sep