a faster way for FullText Search

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

a faster way for FullText Search

Post 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";
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Have you tried

Code: Select all

EXPLAIN SELECT DISTINCT ....
?
see http://dev.mysql.com/doc/refman/5.0/en/explain.html
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post by louie35 »

the problem only occurs when used "IN BOOLEAN MODE" otherwise is quite fast
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post 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
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

SELECT count(something) as myCount
FROM someTable
WHERE stuff=stuff
HAVING myCount > 0
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post by louie35 »

No that's not working. Throws an error that count_products field doesn't exist

Man my head hurts.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post 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
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post 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
Post Reply