Page 1 of 1

a faster way for FullText Search

Posted: Mon Jul 16, 2007 3:22 am
by louie35
I have this search page which uses Full-Text index, but is very slow.
On more then 3 words it takes ages.

Is there a way to improve the speed or is there a better search option out there?

this is what I have at the moment:

Code: Select all

//$search_mode could be "" or IN BOOLEAN MODE

$sSql = "SELECT DISTINCT 
`products_tbl`.`product_id`,
`products_tbl`.`maincat_id`,
`products_tbl`.`cat_id`,
`products_tbl`.`brand_id`,
`products_tbl`.`product_name`,
`products_tbl`.`product_desc`,
`products_tbl`.`product_image`,
`products_tbl`.`product_status`,
`categories`.`cat_name`,
`brands_tbl`.`brand_name`,
`maincategory`.`maincat_name`,
MATCH ($match_tbl) AGAINST ('$sq_search') AS `score`";

$from_table = " FROM products_tbl
Inner Join `product_seller` ON `products_tbl`.`product_id` = `product_seller`.`product_id` 
Inner Join `seller_tbl` ON `product_seller`.`seller_id` = `seller_tbl`.`id` 
Inner Join `categories` ON `products_tbl`.`cat_id` = `categories`.`cat_id` 
Inner Join `brands_tbl` ON `products_tbl`.`brand_id` = `brands_tbl`.`brand_id` 
Inner Join `maincategory` ON `products_tbl`.`maincat_id` = `maincategory`.`maincat_id`";
$where_query = " WHERE ";
	if(($sMaincatid != '') && ($sMaincatid != NULL)){
		$where_query .= 	" (`products_tbl`.`maincat_id`='".$sMaincatid."') AND ";
	}
$where_query .= "(`products_tbl`.`product_status` =  '0' AND `product_seller`.`product_s_status` =  '0' AND `seller_tbl`.`status` =  '0') AND MATCH($match_tbl) AGAINST ('$sq_search'$search_mode)";
$sSql = $sSql.$from_table.$where_query ."ORDER BY `score` DESC LIMIT $offset, $rowsPerPage";

Posted: Mon Jul 16, 2007 3:32 am
by volka
Have you tried

Code: Select all

EXPLAIN SELECT DISTINCT ....
?
see http://dev.mysql.com/doc/refman/5.0/en/explain.html

Posted: Mon Jul 16, 2007 3:50 am
by louie35
the problem only occurs when used "IN BOOLEAN MODE" otherwise is quite fast

Posted: Mon Jul 16, 2007 6:06 am
by louie35
change the sql and moved few INNER JOINs and works perfect

Code: Select all

$sSql = "SELECT DISTINCT `products_tbl`.`product_id`,`products_tbl`.`maincat_id`,`products_tbl`.`cat_id`,`products_tbl`.`brand_id`,
`products_tbl`.`product_name`,`products_tbl`.`product_desc`,`products_tbl`.`product_image`,
`products_tbl`.`product_status`,`categories`.`cat_name`,`brands_tbl`.`brand_name`,`maincategory`.`maincat_name`,
(SELECT Count(`product_seller`.`product_s_id`) AS `count_products` 
FROM `product_seller` 
Inner Join `seller_tbl` ON `product_seller`.`seller_id` = `seller_tbl`.`id`
Inner Join `products_tbl` ON `products_tbl`.`product_id` = `product_seller`.`product_id`
WHERE
`product_seller`.`product_id` =  '`products_tbl`.`product_id`' AND
`product_seller`.`product_s_status` =  '0' AND
`seller_tbl`.`status` =  '0'),
MATCH ($match_tbl) AGAINST ('$sq_search'$search_mode) AS `score`";
$from_table = " FROM products_tbl
Inner Join `categories` ON `products_tbl`.`cat_id` = `categories`.`cat_id` 
Inner Join `brands_tbl` ON `products_tbl`.`brand_id` = `brands_tbl`.`brand_id` 
Inner Join `maincategory` ON `products_tbl`.`maincat_id` = `maincategory`.`maincat_id`";
//Inner Join `product_seller` ON `products_tbl`.`product_id` = `product_seller`.`product_id` 
//Inner Join `seller_tbl` ON `product_seller`.`seller_id` = `seller_tbl`.`id` 
$where_query = " WHERE ";
	if(($sMaincatid != '') && ($sMaincatid != NULL)){
		$where_query .= 	" (`products_tbl`.`maincat_id`='".$sMaincatid."') AND ";
	}
//AND `product_seller`.`product_s_status` =  '0' AND `seller_tbl`.`status` =  '0'
$where_query .= "(`products_tbl`.`product_status` =  '0' ) AND MATCH($match_tbl) AGAINST ('$sq_search'$search_mode)";
$sSql = $sSql.$from_table.$where_query ."ORDER BY `score` DESC LIMIT $offset, $rowsPerPage";
but how do I make sure that I only show products where count_products > 0

Posted: Mon Jul 16, 2007 7:30 am
by Begby
SELECT count(something) as myCount
FROM someTable
WHERE stuff=stuff
HAVING myCount > 0

Posted: Mon Jul 16, 2007 8:52 am
by louie35
No that's not working. Throws an error that count_products field doesn't exist

Man my head hurts.

Posted: Mon Jul 16, 2007 8:55 am
by Begby
Then you put it after the wrong where clause. I would try to find the right spot for it, but that is a lot of SQL and I don't want my head to hurt either.

Posted: Mon Jul 16, 2007 8:59 am
by louie35
is the AS.... in the wrong place but still not working right if i change to :

Code: Select all

//.................. start
(SELECT Count(`product_seller`.`product_s_id`) 
FROM `product_seller` 
Inner Join `seller_tbl` ON `product_seller`.`seller_id` = `seller_tbl`.`id` 
Inner Join `products_tbl` ON `products_tbl`.`product_id` = `product_seller`.`product_id` 
WHERE 
`product_seller`.`product_id` =  '`products_tbl`.`product_id`' AND 
`product_seller`.`product_s_status` =  '0' AND 
`seller_tbl`.`status` =  '0')  AS `count_products`, //notice here
MATCH ($match_tbl) AGAINST ('$sq_search'$search_mode) AS `score`";
//..........more sql code
if gives me few 1000s everytime

Posted: Mon Jul 16, 2007 10:01 am
by louie35
this is the best I could come up with so far using NOT IN....
It seems to work fine in both modes:

Code: Select all

$sSql = "SELECT DISTINCT `products_tbl`.`product_id`,`products_tbl`.`maincat_id`,`products_tbl`.`cat_id`,`products_tbl`.`brand_id`,
`products_tbl`.`product_name`,`products_tbl`.`product_desc`,`products_tbl`.`product_image`,
`products_tbl`.`product_status`,`categories`.`cat_name`,`brands_tbl`.`brand_name`,`maincategory`.`maincat_name`,
MATCH ($match_tbl) AGAINST ('$sq_search') AS `score`";
$from_table = " FROM products_tbl
Inner Join `categories` ON `products_tbl`.`cat_id` = `categories`.`cat_id` 
Inner Join `brands_tbl` ON `products_tbl`.`brand_id` = `brands_tbl`.`brand_id` 
Inner Join `maincategory` ON `products_tbl`.`maincat_id` = `maincategory`.`maincat_id`";
//Inner Join `product_seller` ON `products_tbl`.`product_id` = `product_seller`.`product_id` 
//Inner Join `seller_tbl` ON `product_seller`.`seller_id` = `seller_tbl`.`id` 
$where_query = " WHERE `products_tbl`.`product_id`
 NOT IN ( SELECT `product_seller`.`product_s_id`  FROM `product_seller`  WHERE `product_seller`.`product_id` =  `products_tbl`.`product_id` AND `product_seller`.`product_s_status` =  '0') AND";
	if(($sMaincatid != '') && ($sMaincatid != NULL)){
		$where_query .= 	" (`products_tbl`.`maincat_id`='".$sMaincatid."') AND ";
	}
//AND `product_seller`.`product_s_status` =  '0' AND `seller_tbl`.`status` =  '0'
$where_query .= "(`products_tbl`.`product_status` =  '0' )AND MATCH($match_tbl) AGAINST ('$sq_search'$search_mode)";
$sSql = $sSql.$from_table.$where_query ." ORDER BY `score` DESC LIMIT $offset, $rowsPerPage";
if any of you have a better alternative i would love to hear it because is doing my t***s in