Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
louie35
Forum Contributor
Posts: 144 Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:
Post
by louie35 » Mon Jul 16, 2007 3:22 am
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";
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Mon Jul 16, 2007 3:32 am
louie35
Forum Contributor
Posts: 144 Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:
Post
by louie35 » Mon Jul 16, 2007 3:50 am
the problem only occurs when used "IN BOOLEAN MODE" otherwise is quite fast
louie35
Forum Contributor
Posts: 144 Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:
Post
by louie35 » Mon Jul 16, 2007 6:06 am
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 » Mon Jul 16, 2007 7:30 am
SELECT count(something) as myCount
FROM someTable
WHERE stuff=stuff
HAVING myCount > 0
louie35
Forum Contributor
Posts: 144 Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:
Post
by louie35 » Mon Jul 16, 2007 8:52 am
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 » Mon Jul 16, 2007 8:55 am
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.
louie35
Forum Contributor
Posts: 144 Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:
Post
by louie35 » Mon Jul 16, 2007 8:59 am
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
louie35
Forum Contributor
Posts: 144 Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:
Post
by louie35 » Mon Jul 16, 2007 10:01 am
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