Jump to content

Allow category names to show on search results


Dr. Gonzo

Recommended Posts

To test:

In admin, Store Settings, Advanced tab, enable debug mode and enter your computer's IP address (www.whatismyip.com) in the nearby field.

Execute the search using a keyword.

There will be a grey section below the page. Look for a long query in a red color. It will be about the 24th query and it will start with something similar to the following:
SELECT I.*, MATCH (I.product_code,I.description,I.name) AGAINST

 

Make sure the list of columns in MATCH includes these original three as well as any others you added when you altered the database table.

Link to comment
Share on other sites

SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND ((I.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) LIMIT 12 OFFSET 0 -- (0.0041220188140869 sec) [CACHE WRITE]
[14] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND ((I.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) -- (0.01549506187439 sec) [CACHE WRITE]

 

Link to comment
Share on other sites

These queries are when CubeCart takes an alternate approach to having MySQL perform a search.

But, I see that this part:

((I.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]'))

has no search terms inside the [[::]] sequence. That's not good. It tells me that an edit to the code got messed up, or the browser isn't sending any terms to give to the search query.

This alternate approach is used when the primary approach fails to find anything. And if the fact is that search terms are not being given to the code to use to search with, that would understandable.

But, the primary search approach would have its query listed in the debug section. Unless....

When using the primary search approach, there is a limit to the search word length that must be met. (I do not know what that is - I think it is database server dependent. Assume it is four letters.) There must be at least one word that has the number of characters at or above the minimum search word length. Probably the database server will ignore any word(s) that are below this length. The alternate approach is used if there is no word with the number of characters required.

 

Link to comment
Share on other sites

Ok found out what was causing the sequence to be blank. Turns out this line of code:

public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'fulltext') {

had been changed to:

public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'like') {

during the modifications for the product table's seo_meta_keywords search and once I changed it back to 'fulltext', the sequence now shows my keywords. However we are back in the same boat we were in before with the code not querying the category table's 'seo_meta_keywords' field.

Link to comment
Share on other sites

So I think you are saying that examining the queries listed in the grey debug section, you find the query that starts with:
SELECT I.*, MATCH (I.product_code,I.description,I.name) AGAINST

but should also have (maybe I.seo_meta_keywords along with) C.cat_name,C.cat_desc,C.seo_meta_keywords

as well?

What columns are listed in the MATCH group?

Link to comment
Share on other sites

This is the log from searching and generating a hit on product name:

[13] SELECT I.*, MATCH (I.product_code,I.description,I.name) AGAINST('10amp' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (MATCH (I.product_code,I.description,I.name) AGAINST('10amp' IN BOOLEAN MODE)) >= 0.5 ORDER BY Relevance DESC LIMIT 12 OFFSET 0 -- (0.0097630023956299 sec) [CACHE WRITE]

[14] SELECT COUNT(I.product_id) as count, MATCH (I.product_code,I.description,I.name) AGAINST('10amp' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (MATCH (I.product_code,I.description,I.name) AGAINST('10amp' IN BOOLEAN MODE)) >= 0.5 GROUP BY I.product_id ORDER BY Relevance DESC -- (0.0014050006866455 sec) [CACHE WRITE]

And this is the log from searching for a keyword under a product's seo_meta_keywords field(notice it generated a hit but is missing the keyword in the routine).

[22] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND ((I.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) LIMIT 12 OFFSET 0 -- (0.0097780227661133 sec) [CACHE WRITE]

[23] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND ((I.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) -- (0.010262012481689 sec) [CACHE WRITE]

And I unfortunately do not see any reference to the c.cat_name or c.seo_meta_keywords fields I defined under the custom sql earlier in the post.

I also notice something at the top of the debug log where it gives a PhP notice: "classes/cubecart.class.php:746 - Undefined index: search" when I search on a term used in a product name and a similar notice and an additional notice when searching on something that generates a hit on a seo_meta_keywords term: "classes/catalogue.class.php:1542 - Undefined variable: order"

Link to comment
Share on other sites

This:

MATCH (I.product_code,I.description,I.name)

tells me the ALTER statement that adds a FULLTEXT index to the CubeCart_category table did not take, or this edit in catalogue.class.php was not performed:

Find:
$indexes = $GLOBALS['db']->getFulltextIndex('CubeCart_inventory', 'I');

Add after:
$indexes = array_merge($indexes,$GLOBALS['db']->getFulltextIndex('CubeCart_category', 'C'));

In the debug section, just above the queries numbered 13 and 14, you should also see:

SHOW INDEX FROM `CubeCart_inventory`; -- (0 sec) [CACHE WRITE]
SHOW INDEX FROM `CubeCart_category`; -- (0 sec) [CACHE WRITE]
SHOW VARIABLES LIKE 'ft_min_word_len' -- (0 sec) [CACHE WRITE]

If the one for Cubecart_category is not there, then the edit above was not done.

You MUST use an external database utility, such as phpMyAdmin, to make a change to the existing FULLTEXT index on Cubecart_inventory. It is not possible to ALTER an existing INDEX. The utility knows this and will ALTER the table by first DROPping the index, then ADDing the index back with the new columns to include.

 

Link to comment
Share on other sites

I went in to phpMyAdmin and attempted to alter the existing index and received the error that the used table type does not support FULLTEXT indexes. So maybe that explains why the advanced option did not allow that sql command to go through.

Ok update on search. I have the code for $indexes you laid out for the catalogue.class.php file, the table has been set to a myISAM engine and has allowed me to specify fulltext indexing which I did directly through myPhPAdmin, and the debug still shows this:

[19] SHOW INDEX FROM `CubeCart_inventory`; -- (0.001507043838501 sec) [CACHE READ]
[20] SHOW INDEX FROM `CubeCart_category`; -- (0.001507043838501 sec) [CACHE READ]
[21] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.001507043838501 sec) [CACHE READ]
[22] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND ((I.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) LIMIT 12 OFFSET 0 -- (0.0090909004211426 sec) [CACHE WRITE]
[23] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND ((I.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) -- (0.0072259902954102 sec) [CACHE WRITE]
Link to comment
Share on other sites

We're back to the case where CubeCart isn't getting any words to search on.

 

Find this code in catalogue.class.php:

if ($search_mode == 'fulltext' && $search_str_len >= $max_word_len) {
	switch (true) {
	case (preg_match('#[\+\-\>\<][\w]+#iu', $search_data['keywords'])):


Add the following ABOVE it:
$GLOBALS['debug']->debugMessage('The words to search are: '.$search_data['keywords']);
$GLOBALS['debug']->debugMessage('The $search_mode is: '.$search_mode);
$GLOBALS['debug']->debugMessage('The $search_str_len is: '.$search_str_len);
$GLOBALS['debug']->debugMessage('The $max_word_len is: '.$max_word_len);

When you next make a search, the debug section at the bottom of the web page should have a sub-section called Debug Messages. Those entries should give us a clue why CubeCart is jumping to the alternate search method.

Link to comment
Share on other sites

Here is the debug message section:

[0] The words to search are: 10ah
[1] The $search_mode is: fulltext
[2] The $search_str_len is: 4
[3] The $max_word_len is: 4

Weird thing is, now all those MATCH statements show my keywords AND show the c.cat_name, c.cat_desc, c.seo_meta_keywords we were looking for:

[14] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1603] \"SELECT I.*, MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE)) >= 0.5 ORDER BY Relevance DESC LIMIT 12 OFFSET 0 \" - Unknown column \'C.cat_name\' in \'field list\'','1462310409'); -- (0.00051498413085938 sec) [ERROR - NOT CACHED]

[15] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1603] \"SELECT I.*, MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE)) >= 0.5 ORDER BY Relevance DESC LIMIT 12 OFFSET 0 \" - Unknown column \'C.cat_name\' in \'field list\'','1462310409'); -- (0.00051498413085938 sec) [ERROR - CACHE WRITE]
[
16] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1609] \"SELECT COUNT(I.product_id) as count, MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE)) >= 0.5 GROUP BY I.product_id ORDER BY Relevance DESC \" - Unknown column \'C.cat_name\' in \'field list\'','1462310409'); -- (0.00062894821166992 sec) [ERROR - NOT CACHED]

[17] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1609] \"SELECT COUNT(I.product_id) as count, MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE)) >= 0.5 GROUP BY I.product_id ORDER BY Relevance DESC \" - Unknown column \'C.cat_name\' in \'field list\'','1462310409'); -- (0.00062894821166992 sec) [ERROR - CACHE WRITE]

 

Link to comment
Share on other sites

Did you also make this edit in catalogue.class.php:

// Score matching string
$match = sprintf("MATCH (%s) AGAINST('%s' %s)", implode(',', $indexes), $words, $mode);
$match_val = '0.5';

/* EDITED! */ $query = sprintf("SELECT I.*, %2\$s AS Relevance FROM %1\$sCubeCart_category AS C, %1\$sCubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM %1\$sCubeCart_pricing_group $group_id GROUP BY product_id) as G ON G.product_id = I.product_id $joinString WHERE I.product_id IN (SELECT product_id FROM `%1\$sCubeCart_category_index` as CI INNER JOIN %1\$sCubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (%2\$s) >= %4\$s %3\$s %5\$s %6\$s", $GLOBALS['config']->get('config', 'dbprefix'), $match, $whereString, $match_val, $order_string, $limit);
/* This part in $query above:
   %1\$sCubeCart_category AS C,
   is NEW! */
  if ($search = $GLOBALS['db']->query($query)) {

The $query statement has been edited to include the CubeCart_category table.

What I see in the queries is:
FROM CubeCart_inventory AS C,

It needs to be:
FROM CubeCart_category AS C,

So, you have an edit to fix in two places.

Link to comment
Share on other sites

Ahhhh, stupid me. Ok after the change the search is a little slow but it does return not only a hit on the seo_meta_keywords from a product, but also from category as well.

I also notice that it is returning irrelevant items that do not contain the keyword I specified unless I go back to 'like' instead of 'fulltext' at this line:

public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'like') {

		$per_page = (!is_numeric($per_page) || $per_page < 1) ? 10 : $per_page;

		$original_search_data = $search_data;

not to mention the search is sped up substantially. But if I do this, it is now not generating hits on the seo_meta_keywords field for categories but still returning hits from seo_meta_keywords for products.

Oh, I noticed why in the code. If $search_mode is 'like' then it bypasses that code we added to include the category fields.

Link to comment
Share on other sites

The search is a little slow, eh?

Look at the red queries again. The ones with MATCH in them. At the end of the query is the amount of time it took to send the query to database, the time it took for the database to assemble a response, and send that response back to CubeCart.

I believe the most time consuming portion of CubeCart's page Request/Response cycle is the template rendering engine. Therefore, it is important that you enable the cache in admin, Store Settings, Advanced tab.

Returning irrelevant items, eh?

That's the sucky part of MySQL's "Relevance" MATCH mode. The MySQl search algorithm details are gory and, if the amount of data to search through is sparse, the results are not what you may expect. Once you've got a thousand products (and now that you've included the category table, once you've got a thousand categories), the MySQl MATCH algorithm works much, much better.

Link to comment
Share on other sites

I do have caching enabled under the advanced tab. I noticed this code here(I remarked the original to add the seo_meta_keywords code update from Dirty Butter's thread on the subject):

 

/*$rlike = '';
					if (!empty($search_data['keywords'])) {
						$searchwords = preg_split( '/[\s,]+/', $GLOBALS['db']->sqlSafe($search_data['keywords']));
						foreach ($searchwords as $word) {
							$searchArray[] = $word;
						}

						$noKeys = count($searchArray);
						$regexp = '';
						for ($i=0; $i<$noKeys; ++$i) {
							$ucSearchTerm = strtoupper($searchArray[$i]);
							if (($ucSearchTerm != 'AND') && ($ucSearchTerm != 'OR')) {
								$regexp .= '[[:<:]]'.$searchArray[$i].'[[:>:]].*';
							}
						}
						$regexp = substr($regexp, 0, strlen($regexp)-2);
						
**** Changed to search product meta keywords
					$rlike = " AND (I.name RLIKE '".$regexp."' OR I.description RLIKE '".$regexp."' OR I.product_code RLIKE '".$regexp."')";
					}*/
					$rlike = '';
					if (!empty($search_data['keywords'])) {
					    $searchwords = preg_split( '/[ ,]/', $GLOBALS['db']->sqlSafe($search_data['keywords']));
					    foreach ($searchwords as $word) {
						if ((strtoupper($word) != 'AND') && (strtoupper($word) != 'OR')) $searchArray[] = $word;
					    }

					    $regexp = '';
					    $columnsToSearch = array(
						'I.name',
						'I.description',
						'I.product_code',
						'I.seo_meta_keywords',						
					    );					    

					    foreach($columnsToSearch as $col) {
						for ($i=0, $noKeys=count($searchArray); $i<$noKeys; ++$i) {
						    $regexp[$col][] = $col." RLIKE '[[:<:]]".$searchArray[$i]."[[:>:]]'";
						}
						$regexp[$col] = '(' . implode(' AND ', $regexp[$col]) . ')';
					    }
					    $rlike = " AND (" . implode(' OR ', $regexp) . ")";					
					}
					
					$q2 = "SELECT I.* FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_pricing_group $group_id GROUP BY product_id) as G ON G.product_id = I.product_id $joinString WHERE I.product_id IN (SELECT product_id FROM `".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_category_index` as CI INNER JOIN ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 ".$whereString.$rlike;

Is it possible to alter this SELECT statement here in this code for  } else { 'like' to query the category data? If so that would not only increase the responsiveness of the search but also return hits from category seo_meta_keywords. At the moment there is no reference to that table for this part of the code.

Link to comment
Share on other sites

Ya, I took a whack at it by adding CubeCart_category as C, right before CubeCart_inventory AS I LEFT JOIN ( with no luck, the page fails to load and comes back blank. I appear to be back in the same boat though with not being able to generate a proper hit on seo_meta_keywords for categories. 

Some peculiar things I am noticing while working with the code thus far:

I notice I get an error at the top of the debug log telling me that $order is an undefined variable if I use 'like' (and I believe this may be causing the blanks in the query we were seeing earlier) and I notice it's when RLIKE is used.

If I get a hit on part of a product name, I get the same product listed over and over again when using 'fulltext' search (almost like a loop).

I get the same irrelevant products returned if I search using a known term I specified under a category or product seo_meta_keywords field.

I get nothing returned if I use a keyword that is nowhere to be found in any product or category fields (which I would expect) but notice the blank query when using RLIKE:

[12] SHOW INDEX FROM `CubeCart_inventory`; -- (0.00047087669372559 sec) [CACHE WRITE]
[13] SHOW INDEX FROM `CubeCart_category`; -- (0.00048589706420898 sec) [CACHE WRITE]
[14] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.00055718421936035 sec) [CACHE WRITE]
[15] SELECT I.*, MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST('testing4' IN BOOLEAN MODE) AS Relevance FROM CubeCart_category AS C, CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST('testing4' IN BOOLEAN MODE)) >= 0.5 ORDER BY Relevance DESC LIMIT 12 OFFSET 0 -- (9.3560028076172 sec) [CACHE WRITE]
[16] SHOW INDEX FROM `CubeCart_inventory`; -- (9.3560028076172 sec) [CACHE READ]
[17] SHOW INDEX FROM `CubeCart_category`; -- (9.3560028076172 sec) [CACHE READ]
[18] SHOW VARIABLES LIKE 'ft_min_word_len' -- (9.3560028076172 sec) [CACHE READ]
[19] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND ((I.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) LIMIT 12 OFFSET 0 -- (0.0071730613708496 sec) [CACHE WRITE]
[20] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND ((I.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) -- (0.0065910816192627 sec) [CACHE WRITE]
Debug Messages:
[0] The words to search are: testing4
[1] The $search_mode is: fulltext
[2] The $search_str_len is: 8
[3] The $max_word_len is: 4
[4] The words to search are: testing4
[5] The $search_mode is: like
[6] The $search_str_len is: 8
[7] The $max_word_len is: 4

 

Link to comment
Share on other sites

Notices can be ignored:
$order is an undefined variable

This helps programmers when writing the code that $order has not been seen before, and thus may be misspelled, etc. CubeCart follows the school of programming where this is of no concern.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...