advanced search result modifications

[DigitalShadow]

I have a secondary table that has various keywords associated with product_ids

When searching directly in the database, searching all 180000 entries for a keyword takes 0.0004 seconds

Searching through the store takes about 5 seconds

Am I missing something about the way that search works in the store?

Could I search this table more efficiently?

$from_str = "FROM (" . TABLE_PRODUCTS . " p
             LEFT JOIN " . TABLE_MANUFACTURERS . " m
             USING(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c )
	     LEFT JOIN " . TABLE_PRODUCTS_KEYWORDS . " pk on p.products_id = pk.products_id and p.products_id = pk.products_id";

        $where_str .= " OR (pk.keyword
                        = ':keywords'
                        AND p.products_id = pk.products_id)"; 


        $where_str = $db->bindVars($where_str, ':keywords', $search_keywords[$i], 'noquotestring');

[lat9]

I have a secondary table that has various keywords associated with product_ids

When searching directly in the database, searching all 180000 entries for a keyword takes 0.0004 seconds

Searching through the store takes about 5 seconds

Am I missing something about the way that search works in the store?

Could I search this table more efficiently?

$from_str = "FROM (" . TABLE_PRODUCTS . " p
             LEFT JOIN " . TABLE_MANUFACTURERS . " m
             USING(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c )
         LEFT JOIN " . TABLE_PRODUCTS_KEYWORDS . " pk on p.products_id = pk.products_id and p.products_id = pk.products_id";

        $where_str .= " OR (pk.keyword
                        = ':keywords'
                        AND p.products_id = pk.products_id)"; 


        $where_str = $db->bindVars($where_str, ':keywords', $search_keywords[$i], 'noquotestring');

There sure is! Try these code fragments instead:

$from_str = "FROM (" . TABLE_PRODUCTS . " p
             LEFT JOIN " . TABLE_MANUFACTURERS . " m
             USING(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c )
         LEFT JOIN " . TABLE_PRODUCTS_KEYWORDS . " pk on p.products_id = pk.products_id";

... to remove the replication of p.products_id = pk.products_id, and

        $where_str .= " OR (pk.keyword = ':keywords')";

... to remove the 'AND' condition (which was previously fulfilled by the left-join's 'ON' clause).

[DigitalShadow]

My first attempt looked like that.

It still adds about 5-7 seconds to the total search time if I search that table vs not

I think I've got the indexes correctly setup for the table.

I'm really confused and desperate because it is killing site performance.

Searching 82000 products without searching that table takes about 1 second on the store

Including that $where_str increases the search time to about 8-9 seconds

[lat9]

What are the field definitions for that products_keywords table?

[DigitalShadow]

You mean this?

	1	keyword_id Primary	int(11)			No	None		AUTO_INCREMENT
	2	products_id Index	int(11)			No	None		
	3	products_sku_id	varchar(16)	utf8_general_ci		No	None	
	4	keyword_date_added	datetime			No	None			
	5	keyword_who_added	varchar(32)	utf8_general_ci		No	None		
	6	keywordIndex	varchar(128)	utf8_general_ci		No	None			
	7	keyword_typeIndex	varchar(128)	utf8_general_ci		No	None

Action	Keyname	Type	Unique	Packed	Column	Cardinality	Collation	Null	Comment
Edit Edit	Drop Drop	PRIMARY	BTREE	Yes	No	keyword_id	177131	A	No	
Edit Edit	Drop Drop	idx_products_id	BTREE	No	No	products_id	22141	A	No	
Edit Edit	Drop Drop	idx_keyword	BTREE	No	No	keyword	7380	A	No	
products_id	177131	A	No
Edit Edit	Drop Drop	idx_keyword_type	BTREE	No	No	keyword_type	5	A	No	
products_id	59043	A	No

[lat9]

Where's the products_keywords::keyword field defined and, assuming that you've defined it as a string-value, why are you using that as an index (it's the latter that's most likely causing the search-time expansion).

[DigitalShadow]

Action	Keyname	Type	Unique	Packed	Column	Cardinality	Collation	Null	Comment
Edit Edit	Drop Drop	PRIMARY	BTREE	Yes	No	keyword_id	177534	A	No	
Edit Edit	Drop Drop	idx_products_id	BTREE	No	No	products_id	22191	A	No

still just as slow

I wonder it is because the table has multiple entries for the same product_id

[lat9]

That could certainly have some 'play' in the behavior.

[DigitalShadow]

There are three distinct keyword types, do you think there is any benefit to splitting them up into three different tables?

[DigitalShadow]

I've split them up, it has made the table smaller and halved the search time, managable until I find a better solution