Hey folks,
I'm a little stuck. I got the following tables:
CREATE TABLE `items` ( `id` int(10) unsigned NOT NULL auto_increment, `unlocked` tinyint(1) NOT NULL default '0', `name` varchar(200) NOT NULL, `meta_cat_type` enum('blah','foo','bar') default NULL, `post_time` int(10) unsigned NOT NULL default '0', `unlock_time` int(10) unsigned NOT NULL, `allow_comments` tinyint(1) NOT NULL default '1', `posted_by` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `post_time` (`post_time`), KEY `meta_cat_type` (`meta_cat_type`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- --------------------------------------------------------CREATE TABLE `item_cats` ( `item_id` int(10) unsigned NOT NULL, `cat_id` smallint(5) unsigned NOT NULL, KEY `item_id` (`item_id`), KEY `cat_id` (`cat_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- --------------------------------------------------------CREATE TABLE `item_filters` ( `user_id` int(10) unsigned NOT NULL, `cat_id` smallint(5) unsigned NOT NULL, `meta_cats` set('blah','foo','bar') NOT NULL, `filter_type` enum('overview','global') NOT NULL, UNIQUE KEY `id` (`user_id`,`cat_id`,`filter_type`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
So each item can belong to many categories. Pretty much standard stuff.
Now I have a typical view-items-with-pagination-and-stuff thing set up, so I need to get the total number of items and the items on the current page. My current query to get these looks like this, but it's rather slow as the sub query has to be executed once for every row in the items table (which currently has about 50,000 rows and is growing continuously).
SELECT COUNT( * )FROM itemsWHERE unlocked =1AND ( NOT EXISTS ( SELECT * FROM item_filters f, item_cats c WHERE f.user_id = "823" AND f.cat_id = c.cat_id AND c.item_id = items.id AND f.filter_type = "overview" AND FIND_IN_SET( items.meta_cat_type, meta_cats ))
Any help on how to speed this up is greatly appreciated! Thanks in advance. )
I'm a little stuck. I got the following tables:
CREATE TABLE `items` ( `id` int(10) unsigned NOT NULL auto_increment, `unlocked` tinyint(1) NOT NULL default '0', `name` varchar(200) NOT NULL, `meta_cat_type` enum('blah','foo','bar') default NULL, `post_time` int(10) unsigned NOT NULL default '0', `unlock_time` int(10) unsigned NOT NULL, `allow_comments` tinyint(1) NOT NULL default '1', `posted_by` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `post_time` (`post_time`), KEY `meta_cat_type` (`meta_cat_type`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- --------------------------------------------------------CREATE TABLE `item_cats` ( `item_id` int(10) unsigned NOT NULL, `cat_id` smallint(5) unsigned NOT NULL, KEY `item_id` (`item_id`), KEY `cat_id` (`cat_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- --------------------------------------------------------CREATE TABLE `item_filters` ( `user_id` int(10) unsigned NOT NULL, `cat_id` smallint(5) unsigned NOT NULL, `meta_cats` set('blah','foo','bar') NOT NULL, `filter_type` enum('overview','global') NOT NULL, UNIQUE KEY `id` (`user_id`,`cat_id`,`filter_type`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
So each item can belong to many categories. Pretty much standard stuff.
Now I have a typical view-items-with-pagination-and-stuff thing set up, so I need to get the total number of items and the items on the current page. My current query to get these looks like this, but it's rather slow as the sub query has to be executed once for every row in the items table (which currently has about 50,000 rows and is growing continuously).
SELECT COUNT( * )FROM itemsWHERE unlocked =1AND ( NOT EXISTS ( SELECT * FROM item_filters f, item_cats c WHERE f.user_id = "823" AND f.cat_id = c.cat_id AND c.item_id = items.id AND f.filter_type = "overview" AND FIND_IN_SET( items.meta_cat_type, meta_cats ))
Any help on how to speed this up is greatly appreciated! Thanks in advance. )
Comment