Re: How to search mysql?

Hi Bob,

Thank you for responding, I am quite new to SQL and I have a few more questions.

>It would be better to split up the part number into separate fields
>for this kind of searching;

In this case I don't see how we could break the part number into separate fields because they are from many manufacturers and can consist of almost any character string. 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.

>most database systems cannot search text
>fields efficiently unless an index is present, but if you search on
>substrings, the index cannot be used, i.e. the engine does a full
>table scan unless you include the beginning of the string ... then it
>can use the index.

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

>SELECT part_no FROM parts WHERE part_no LIKE 'ans23%';

Does that mean that the above statement uses an index and is fast?

If not, can you do a search using the index where there is no match and have it return the index of the 'closest' match ( i.e. the last index it tested against the criteria)?

Let's say there are 1000 matches. Since this is a web based application we don't want to return them all at once. If 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?

Is there a way to know how many total matches there are when you retrieve the 1st 25?

Bill Received on Sat May 04 2002 - 15:24:44 CEST