How to search mysql table?

Anon

I am new to SQL and I have a couple of questions about searching mysql tables.

We have a mysql parts database and it is indexed by manufacturer's part number. We want to be able to search it either by using the 1st few characters of the number and return numbers that begin with these characters or by matching a substring of characters within the number.

Example 1:

part_number = ans234756

search for ans23 and retrieve all part numbers that begin with ans23.

Is

SELECT part_number FROM parts WHERE part_number LIKE 'ans23%';

the best/fastest way to do it?

Example 2:

part_number = ans234756

search for s234 and retrieve all part numbers that contain s234.

Is

SELECT part_number FROM parts WHERE part_number LIKE '%s234%';

the best/fastest way to do it? Will it include part numbers that begin with s234.

The reason for substring searches is because some parts do not have the part numbers printed on them all in one string. For example, the following part number

VNS123456/ST56

might be printed on the part as

VNS
123456/
ST56

So someome might try searching for 123456 or ST56.

Can you do a search using an index where there is no match and have it return the record with the index of the 'closest' match ( i.e. the last index it tested against the criteria)? Or return the 10 closest matches?

Let's say there are 1000 matches. Since this is a web based application we don't want to return them all at once. Say we want 25 at a time, is there a built in mechanism for doing that or would we have to remember the last record and get the next 26 starting with it?

Any help will be appreciated.

Bill

flipis

As for the SQL, it's ok, as far as i know. What do you mean by matching criteria? I mean, if you want to select all numbers that contain 564 and begin with 2, you do this:

SELECT number FROM number_table WHERE number LIKE '2%' AND number LIKE '%564%'

I could only advice you to play with the order of the criteria, that might speed up the query (i'm not really sure about this, but i've been 8 months working with MySQL and i've seen it do strange things depending on how you write the SQL.)

Well, i hope to help you

fLIPIS

bretticus

Bill Dandreta wrote:

search for s234 and retrieve all part numbers that contain s234.

SELECT part_number FROM parts WHERE part_number LIKE '%s234%';

the best/fastest way to do it? Will it include part numbers that begin with s234.

Using LIKE in this situation is the best way to go about it. Yes, "LIKE '%s234%'" should return records with part numbers that begin with s234 at the begining or the same string anywhere within the part number.

If you also had a parts description in each record that was rather lengthy, and you wished to query against this column as well, MySQL has a MATCH...AGAINST syntax that can return a 'search engine' style recordset you might be interested in as well:

http://www.mysql.com/doc/F/u/Fulltext_Search.html

If I were you (assuming I have an idea of what it's all about ;-)I would probably keep it simply and use LIKE.

As for returning just a few records at a time use LIMIT:

http://www.mysql.com/doc/S/E/SELECT.html