MySQL Forums
Forum List  »  Full-Text Search

Slow Query - Bit of a Newbie
Posted by: Jon
Date: September 06, 2006 08:10PM

Hi,

I'm having problems with an oscommerce book store I'm working on. I have had to modify the table structures a bit to include additional fields but am finding the full-text search implementation very slow. I have tried reading various topics on indexing and my.cnf optimisation but still haven't been able to fix this.

Here is an example of the query that is causing the problem:

-------------------------------------
TO COUNT NUMBER OF MATCHES
-------------------------------------

SELECT count( DISTINCT p.products_id ) AS total
FROM products p
LEFT JOIN manufacturers m
USING ( manufacturers_id ) , products_description pd
LEFT JOIN specials s ON p.products_id = s.products_id, categories c, products_to_categories p2c
WHERE p.products_status = '1'
AND p.products_id = pd.products_id
AND pd.language_id = '1'
AND p.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND MATCH (
p.products_name, p.products_sub_title, p.products_series, p.products_author, p.products_publisher, p.products_model
)
AGAINST (
'+php +mysql'
IN BOOLEAN
MODE
)

-------------------------------------
FOLLOWED BY (to get first 15 matches)
-------------------------------------

SELECT DISTINCT p.products_image, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id,
IF (
s.status, s.specials_new_products_price, NULL
) AS specials_new_products_price,
IF (
s.status, s.specials_new_products_price, p.products_price
) AS final_price
FROM products p
LEFT JOIN manufacturers m
USING ( manufacturers_id ) , products_description pd
LEFT JOIN specials s ON p.products_id = s.products_id, categories c, products_to_categories p2c
WHERE p.products_status = '1'
AND p.products_id = pd.products_id
AND pd.language_id = '1'
AND p.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND MATCH (
p.products_name, p.products_sub_title, p.products_series, p.products_author, p.products_publisher, p.products_model
)
AGAINST (
'+php +mysql'
IN BOOLEAN
MODE
)
ORDER BY pd.products_name
LIMIT 0 , 15

-------------------------------------
CURRENT TABLE STRUCTURE FOR 'products' [Has about 300,000 rows]
-------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
`products_id` int(11) NOT NULL auto_increment,
`products_quantity` int(4) NOT NULL default '0',
`products_model` varchar(25) default NULL,
`products_image` varchar(64) default NULL,
`products_image_med` char(1) default NULL,
`products_image_lrg` char(1) default NULL,
`products_image_sm_1` char(1) default NULL,
`products_image_xl_1` char(1) default NULL,
`products_image_sm_2` char(1) default NULL,
`products_image_xl_2` char(1) default NULL,
`products_image_sm_3` char(1) default NULL,
`products_image_xl_3` char(1) default NULL,
`products_image_sm_4` char(1) default NULL,
`products_image_xl_4` char(1) default NULL,
`products_image_sm_5` char(1) default NULL,
`products_image_xl_5` char(1) default NULL,
`products_image_sm_6` char(1) default NULL,
`products_image_xl_6` char(1) default NULL,
`products_price` decimal(15,4) NOT NULL default '0.0000',
`products_listprice` decimal(15,4) NOT NULL default '0.0000',
`products_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
`products_last_modified` datetime default NULL,
`products_date_available` datetime default NULL,
`products_weight` decimal(5,2) NOT NULL default '0.00',
`products_status` tinyint(1) NOT NULL default '0',
`products_tax_class_id` int(11) NOT NULL default '0',
`manufacturers_id` int(11) default NULL,
`products_ordered` int(11) NOT NULL default '0',
`products_name` varchar(250) NOT NULL default '',
`products_sub_title` varchar(220) default NULL,
`products_author` varchar(150) default NULL,
`products_publisher` varchar(90) default NULL,
`products_series` varchar(110) default NULL,
`products_origin_country` varchar(30) default NULL,
`products_edition` varchar(100) default NULL,
`products_binding` varchar(60) default NULL,
`products_pages` int(6) NOT NULL default '0',
`products_firmsale` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`products_id`),
UNIQUE KEY `products_model` (`products_model`),
KEY `idx_products_date_added` (`products_date_added`),
KEY `products_status` (`products_status`),
KEY `products_ordered` (`products_ordered`),
FULLTEXT KEY `products_sub_title` (`products_sub_title`),
FULLTEXT KEY `products_author` (`products_author`),
FULLTEXT KEY `products_publisher` (`products_publisher`),
FULLTEXT KEY `products_series` (`products_series`),
FULLTEXT KEY `products_origin_country` (`products_origin_country`),
FULLTEXT KEY `products_edition` (`products_edition`),
FULLTEXT KEY `products_binding` (`products_binding`),
FULLTEXT KEY `title_index` (`products_name`,`products_sub_title`,`products_series`,`products_author`,`products_publisher`,`products_origin_country`,`products_binding`,`products_model`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=634511 ;

-----------------

Any suggestions for ways to improve speed (server has 2GB ram)? PLease let me know if you need any additional information.

Many thanks
Jon

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Query - Bit of a Newbie
7582
September 06, 2006 08:10PM
2519
September 29, 2006 12:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.