Slow Query - Bit of a Newbie

[Jon]

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