Is this an efficient search

Anon

$sql="select products.*, brand_wt.brand_wt, brand_lsc.brand_lsc FROM products, brand_wt, brand_lsc
WHERE brand_lsc.brand_lsc LIKE '$search_text%' AND products.brand_lsc_id=brand_lsc.brand_lsc_id
OR brand_wt.brand_wt LIKE '%$search_text%' AND products.brand_wt=brand_wt.brand_wt_id
OR product_name LIKE '%$search_text%'
group by product_id";

Quick Summary :
brand_wt and brand_lsc are joins from products. Products stores the ID"s of brand_wt and brand_lsc (in case I wrote it incorrectly)

I need to search through products including the field of brands that contain the linked brand names. Then display the product name and the text in the associated field for the product.

So :
Ex. of fields
brand_wt_id= ID=1 brand_wt=soap
brand_lcs_id=ID=1 brand_lcs=liquid

product_name=ivory
brand_wt=1
brand_lcs_id=1

Final Display after search for "SOAP" through table products
product_name=ivory
brand_wt=soap
brand_lcs_id=liquid

I know this is long. But figured to much info is better then to little. Thanks for any help yo can give. By the way, this works, but I need to group all the information so as not to show redundancies. And the searches take about two seconds.

[deleted]

Your query looks a bit strange...

select products.*,
brand_wt.brand_wt,
brand_lsc.brand_lsc
FROM products, brand_wt,
brand_lsc
WHERE
brand_lsc.brand_lsc LIKE '$search_text%'
AND products.brand_lsc_id=brand_lsc.brand_lsc_id

OR brand_wt.brand_wt LIKE '%$search_text%'
AND products.brand_wt=brand_wt.brand_wt_id

OR product_name LIKE '%$search_text%'
GROUP BY product_id";

are you trying to join products with brand_lsc on the id
and join products with brand_wt on the id
and then see if brands_lsc.brand_lsc contains search_text
AND
either brant_wt.brand_wt contains search_text
or product_name contains search_text?

or are you looking to do seperate joins for each of the LIKE statements?

Parenthesis might help you and your database work out what you are trying to do.

Are you using any indexes? they can speed your search up quite a bit.
Try running your query manually using the EXPLAIN command in SQL, that will tell you just what your database is trying to do to the query and which indexes (if any) it found usefull. If it couldn't use any indexes, it's time to create some indexes.

Anon

You are right. I am trying to joing all of the brands_id with products_id and search through PRODUCTS database for matches. Products is the table I want to search through, as if the linked BRANDS were part of it. So I could find that text as well.

The query I used works, but is slow.. 12 second searches now. No there are no indexes yet, I was waiting to see if I could create a more efficient query first.

Scott

vincent wrote:

Your query looks a bit strange...

select products.*,
brand_wt.brand_wt,
brand_lsc.brand_lsc
FROM products, brand_wt,
brand_lsc
WHERE
brand_lsc.brand_lsc LIKE '$search_text%'
AND products.brand_lsc_id=brand_lsc.brand_lsc_id

OR brand_wt.brand_wt LIKE '%$search_text%'
AND products.brand_wt=brand_wt.brand_wt_id

OR product_name LIKE '%$search_text%'
GROUP BY product_id";

or are you looking to do seperate joins for each of the LIKE statements?

Parenthesis might help you and your database work out what you are trying to do.

Are you using any indexes? they can speed your search up quite a bit.
Try running your query manually using the EXPLAIN command in SQL, that w....

[deleted]

You can try putting the clauses in the WHERE clause in a different order.
Most queries are parsed on a left-to-right basis, so for example:

SELECT x FROM table WHERE X > 1 AND X < 1000;

this will first select all records where "x>1" which is allmost all records,
and from that set the records with "x<1000" are taken

that is slower than

SELECT x FROM table WHERE x < 1000 AND X > 1;

because the in the second query, all records that have x>1000 are eliminated in the first condition, and the "x>1" condition is only applied to the remaining records.

The same goes for JOIN statements, but they are tricky.
It's a good idea to read the manual of your database engine to see how it handles JOINs and exactly what it does when you JOIN two tables.

And it's allways a good idea to use indexes. But... be carefull about which indexes you create, too many indexes is not good either.

sbroad

Vincent,

You know the thing is, I only have 384 products in my product table. It just seems even without indexes, this search should be fast, not 10 seconds. Man, this is frustrating.

I guess I kinda feel that to query my products database while including the link_id items should not take this long.

Product = 384 items
brand_w = 24 items
brand_wt = 19 items
brand_lsc = 10 items

All product is, is referenes to the brands for the brand name in the product.

So basically, it seems logical to me, that a query should work like this,

Load product database.
Include names of brands which are linked by id.
Query products database for information that is like product name, brand_ls, brand_w.

And since the product has the id of the brands, it should just be included without an exhaustive search for the brand name which is linked via id in the product.

Is this train of thought correct?

P.S. - I appreciate the replies, you have already helped with your suggestions.

sbroad

The new sql statement. While resting from frustration, I realized in a moment of peace the error of my ways. I was basically quering EVERYTHING before which resulted in a query of 300,000+ rows of information which caused the slow down! Relief, 2 days of this crap, I learned alot about joins in the process though. Thanks vinny for your help.

INNER JOIN brand_lsc ON brand_lsc.brand_lsc_id=products.brand_lsc_id
LEFT JOIN brand_w ON brand_w.brand_w_id=products.brand_w_id
LEFT JOIN brand_wt ON brand_wt.brand_wt_id=products.brand_wt
where brand_wt.brand_wt like '%$search_text%' or product_name like '%$search_text%'";
// WHERE brand_w like '%m%' or brand_lsc like '%m%' or brand_wt.brand_wt like '%100%'";