Speed up Large Mysql Query

Need to speed up a large SELECT in Mysql

[hughie]

Hi There

i've built a system that's working with a large mysql database (4 million records at this point) and it's going to easily push 20 million records

It contains two tables, one is the products, and table two is a table contain all the possible variations of the spelling of Product_Name

Table A - (about 15,000 entries)
Prod_ID (unique primary key)
Product_Name (var char 10)
etc.

Table B - 4 million entries
ID (unique - primary key)
Prod_ID ( Int - Index)
The_Text (Varchar 10) FULLTEXT index

Basically i'm matching "The_Text" in table B with the user input and pulling out the product ID's that match.

Typically this section is taking between 2 and 4 seconds to run at the moment.

here's my query

$result=$this->run_sql("SELECT Prod_ID FROM table_b WHERE Product_Name LIKE '%".$keywords."%' ");
if (mysql_num_rows($result))
{
while($row=mysql_fetch_array($result))
{
if (!in_array($row['Prod_ID'],$theArray))
{
$theArray[]=$row['Prod_ID'];
}
}
}

Are there any ways to speed things up?

Thanks,
hughie

[GoldFish]

Hi Hughie

A 'LIKE' query on a large dataset takes heaps of time. You might want to look into MySQL's Full-text Search Functions which will run queries a lot faster. Documentation is available at [dev.mysql.com ].

[hughie]

absolutely outstanding!

here's my results:
Using LIKE : 1.8079
User Match Against: 0.11493 seconds

I've combined that with Mysql Caching and my search is super-fast now

Many thanks,
hughie

[hughie]

a problem has emerged from the deep.

Heres the issue

say the database contains the following records

Prod ID prod name
1 -------- babcdefg
2 -------- hytzkaa

SELECT * FROM products WHERE Prod_Name LIKE '%abcd%'
returns ID 1

SELECT * FROM products WHERE (Prod) AGAINST ('abcd')
returns no rows

I think this would be fine searching for whole words but in this instance i'm looking for partial words.

is there any way to use the functionality of LIKE '%abcd%' whilst using MATCH?

Thanks,
hughie

[henry0]

You could use

WHERE MATCH (your, terms) AGAINST

if those terms where declared in:
ADD FULLTEXT .....(your, terms)...

[hughie]

Hi Henry

that's what i am using, although i've typed it wrong in my above example. I really need a way of using a wildcard each side of the request similar to LIKE whilst keeping the speed of MATCH AGAINST.

not sure it's possible...

[GoldFish]

An option is to make a new table which you fill with the most likely 'partial matches'. One row for each product with the partial word list (fulltext) and the product ID. Then you can run your 'match against' query on that table, and from there get the rest of the product data. This technique is often used to make a search facility: the table then usually contains the words after stemming and filtering.

Good luck!

[GoldFish]

And another solution I just thought of. If the 'match against' query returns no results, then do a 'like' query. I'd imagine that the majority of the searches would run quick, and the end-user always gets results.