Speed up LIKE '%asdf%' or alternative

[ggggus]

I have a table with about 500,000 records in it, and the table contains manufacturer part numbers. Users need to be able to search the field with the manufacturer part number using partial numbers.

The part number field is indexed and it is a varchar(50) field.

I have been using a statement similar to:

SELECT * FROM man_pn WHERE pn LIKE '%search_string%';

As the database has grown, this search is taking longer and longer. Is there an index type that may speed this type of search up, or a different way of searching that will speed this up?
I'm familiar with fulltext searches, and use it frequently, it will not work in this situation, the part numbers may look something like: 234-x4fd 324-3
Manufacturer part numbers frequently include dashes, periods, spaces, and other non-word characters. Users need to be able to search on things like 34-x4 and have the above example of a part number returned.

***************************************
J. Jacobs

http://www.piercedmedia.com

 

[nego78]

I can be wrong but when you search

LIKE '&sth%'

than mysql don't use index on this field.

 

[guelphdad]

nego78 I'm assuming you meant:
LIKE '%sth%' and not LIKE '&sth%'

and yes you are correct, with a leading % then any index on the column is ignored.

 

[nego78]

guelphdad: yes i wrote & instead %

ggggus: maybe you can write script in perl or php which get all part numbers, split them into smaller pieces and you insert these pieces in new table with index
when you will search you select pieces nr from this table joining table with product id and grouping by product id

 

[KarveR]

If you stored a make or manufacturer and used that as well as a partial part number it would be considerably quicker.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[Kozusnik]

Is that a unique field? Either way, you may be better off breaking that field into it's own table with an auto-generated id value. Join that new table back to the original via a numeric id.

Mark

 

[ggggus]

It's a pretty small table to begin with (lots of rows but not many columns). Here is the table (names changed)

+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| part_ref_id     | mediumint(8) unsigned |      | PRI | NULL    | auto_increment |
| man_id          | smallint(5) unsigned  |      | MUL | 0       |                |
| man_pn          | varchar(50)           |      | MUL |         |                |
| part_id         | int(10) unsigned      |      | MUL | 0       |                |
+-----------------+-----------------------+------+-----+---------+----------------+

http://www.fitnessrepairparts.com/part_search/AdvancedSearchForm

That link is to the page that uses this information if you want to see how it works. It's the part number search form in the top right.

This website carries parts for all the major manufacturers of fitness equipment, and the idea is that customers can type in any manufacturer's part number and get a result back. I suppose we might add an optional select field for the manufacturer...that would speed up the search, but it would also reduce ease of use for the customer.

***************************************
J. Jacobs

http://www.piercedmedia.com