mysql query help

Portal Home > Knowledgebase > Articles Database > mysql query help

Posted by kamurj, 03-03-2010, 03:54 PM
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') GROUP BY ds_item.iid 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 http://img218.imageshack.us/img218/8274/explain.gif
Posted by kamurj, 03-03-2010, 05:12 PM
I removed the group by part from the sql but it does not seem to make any difference.
Posted by tim2718281, 03-03-2010, 05:21 PM
You need to say what the tables and columns mean. Otherwise it's just too much effort to understand what you're trying to do.
Posted by kamurj, 03-03-2010, 05:43 PM
Sure tim2718281, in ds_item: status: 0 if in stock, 1 if out of stock date: date when the item was added to database dupdate: date when the product was last updated sale: sale percentage (80, 70) off etc. pid: is manufactures internal id for the product, sometimes same as the sku, sometiems different. ds_item_cat iid: is the same as ds_item.iid cat: holds category ids and ds_store is just store_info where the item is sold, it is linked with ds_item through store_id which both tables have. Thanks for looking into it.
Posted by mattle, 03-03-2010, 05:57 PM
It appears that the only key you are using in your where clause is the status key. I imagine there are 93K rows with status=0. You should be able to reproduce the first line of the explain with simply: select * from ds_item where status='0'; Your table order could be very important here. You may want to try SELECT'ing FROM ds_item_cat first, and then doing the rest of your JOINs, including a JOIN to ds_item. Something like this: SELECT i.whatever FROM ds_item_cat AS c1 INNER JOIN ds_item i ON i.iid=c1.iid # the rest of your joins WHERE i.status=0 AND c1.cat IN (foo) The first index that will be used is ds_item_cat.cat (assuming you have one), against foo, which hopefully is less than 93k. From there, everything should follow. It looks like your tables are indexed well, but your outermost query just returns too many results. I would make the FROM the table from which you expect the least results.
Posted by tim2718281, 03-03-2010, 06:18 PM
Ah, OK, I think I get it; the category ids don't necessarily mean anything in themselves - there will be all manner of categories depending on the product. And they are outside your control. So, you need to find item ids (iid) which have several rows in ds_item_cat, one row for each category you are searching for. The thing is, because the rows are so small, reading 100,000 rows will take very little time. Indexing won't help, because it will take about as long to read an index as to read the table. But that should only be 10-20 milliseconds. How long is the query actually taking?
Posted by kamurj, 03-03-2010, 06:25 PM
Thanks Mattle, you are correct, there will be 93K if not more with status=0. I tried to change the query around a little bit as you suggested by that did not seem to make any difference. explain SELECT ds_item.iid, title, sale, price_list, price_sale, price_sale_avg, ds_item.store_id, description, store_name, currency FROM ds_item_cat AS c1 INNER JOIN ds_item ON ds_item.iid=c1.iid INNER JOIN ds_item_cat AS gender ON c1.iid=gender.iid AND gender.cat='4118' INNER JOIN ds_item_cat AS brand ON c1.iid=brand.iid AND brand.cat='715' INNER JOIN ds_item_cat AS color ON c1.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') LIMIT 0, 10 still scans 94K plus rows.
Posted by kamurj, 03-03-2010, 06:29 PM
Sorry just saw your last post, it takes about 0.1908 sec to return 10 results.
Posted by mattle, 03-04-2010, 09:44 AM
That's actually not that bad considering this is kind of a loose-ish product search across 100K-ish products. Are you seeing real performance problems from this?
Posted by kamurj, 03-04-2010, 09:48 AM
No real performance issues mattle, just thought I probably have something wrong in the query syntax, table keys causing such large scan of a table.