table/query help

[a g]

Hello, my mysql knowledge is limited and after doing some reading, tests I am unable to find any solution and would appreciate any help. The task is to find products that with specific tags.

The main products table

CREATE TABLE IF NOT EXISTS `ds_item` (
`iid` int(11) NOT NULL auto_increment,
`status` int(5) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`date` int(11) NOT NULL default '0',
`dupdate` int(11) NOT NULL default '0',
`sale` smallint(2) NOT NULL default '0',
`price_list` varchar(50) NOT NULL default '0.00',
`price_sale` varchar(50) NOT NULL default '0.00',
`price_sale_avg` decimal(15,2) NOT NULL default '0.00',
`price_save` decimal(15,2) NOT NULL default '0.00',
`store_id` int(11) NOT NULL default '0',
`pid` varchar(110) NOT NULL,
`sku` varchar(100) NOT NULL,
`upc` varchar(100) NOT NULL,
`url` text NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`iid`),
KEY `store_id` (`store_id`),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2124683 ;


tags' table

CREATE TABLE IF NOT EXISTS `ds_item_cat` (
`rel_id` int(11) NOT NULL auto_increment,
`iid` int(11) NOT NULL default '0',
`cat` int(11) NOT NULL default '0',
PRIMARY KEY (`rel_id`),
KEY `iid_cat` (`iid`,`cat`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6127161 ;


stores' table

CREATE TABLE IF NOT EXISTS `ds_store` (
`store_id` int(11) NOT NULL auto_increment,
`store_name` varchar(255) NOT NULL default '',
`store_status` smallint(3) NOT NULL default '0',
`currency` char(3) NOT NULL default 'usd',
`note` varchar(255) NOT NULL,
PRIMARY KEY (`store_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=539 ;


now a sample query to get Women's Black Steve Madden Sandals

SELECT ds_item.iid, title, sale, price_list, price_sale, price_sale_avg, ds_item.store_id, description, store_name, currency FROM ds_item
INNER JOIN ds_item_cat AS c1 ON ds_item.iid=c1.iid
INNER JOIN ds_item_cat AS gender ON ds_item.iid=gender.iid AND gender.cat='4118'
INNER JOIN ds_item_cat AS brand ON ds_item.iid=brand.iid AND brand.cat='715'
INNER JOIN ds_item_cat AS color ON ds_item.iid=color.iid AND color.cat='4127'
LEFT JOIN ds_store ON (ds_item.store_id=ds_store.store_id)
WHERE status='0' AND c1.cat IN ('4160')
ORDER BY sale DESC, ds_item.dupdate DESC
LIMIT 0, 10

The problem: when I run explain on this query it shows that about 93947 rows need to be scanned. Any way to avoid this by optimizing the query, tables? Thank you.


the results of explain





Edited 5 time(s). Last edit at 03/03/2010 02:44PM by a g.