Best way to pull large amounts of data MYSQL

[ikaris69]

i have two tables as follows using mysql

product features
-product_id int(11)
-feature_value

product_description
-product_id int(11)
-description_value

i need to be able to search tese two for all product id;s that have the keyword i specify in them. just for a simple search on our website. the issue is there are 50k+ records in each and it takes forever to run the sql statement
im trying fulltext indexes but im having trouble joining the two its still takes a long time to run. does anyone know of a good way to pull text from a database of this size relativly fast?

thanks in advance

-JLR

[si_the_geek]

What is the query you are currently using?

[ikaris69]

select * from product_features, product_description where product_features.product_id = product_description.product_id and match (product_features.features_value) against ('light') and match (product_description.description_value) against ('light');

[si_the_geek]

I presume you have already tried using Like (and found it to be slower), so I'll ignore that.

I don't use MySQL, so my knowledge of Match is limited to the online documentation (here).

I don't know if it would help to specify a modifier as shown there (eg: IN NATURAL LANGUAGE MODE), or if the default is what you need anyway.

It has some info about how the index should be created (especially via the 'fine tuning' link), which you should definitely check - as even a minor error there can drastically reduce the speed. You should also consider the tips it gives, such as the length of words (it can make a surprising difference to speed - but also limits what you can search for).


Even if that doesn't help, there are other ways of speeding things up... as you are searching both tables and then joining them, you should ideally run on just one of them first, and then do the second search using what you already know (if the join is Inner [there must be a match in both] only scan the matching rows, otherwise only scan the non-matching rows). You could use a temporary table for this, or perhaps sub-queries.

Note also that "select *" slows things down.. it means that the query engine needs to find out what the fields are (minor slowdown), and that all fields are stored/joined/transmitted even if you don't need them (potentially major slowdown). If speed matters, you should always list each of the fields you want in the Select clause.

[penagate]

Also, consider a third-party full-text search tool, such as Sphinx. I've not used it, but I've heard it can be much quicker than MySQL's built-in full-text searching.