I'm using MySQL, have categories in a tree model. One product belongs to only one (leaf) category.
What I want to accomplish is, given one category (any, could or not be a leaf or a root), return all products in a GROUP BY sentence.
I have managed to make the query, but the category table is about 100.000+ rows so it is very slow to execute.
SELECT Publication._id, GROUP_CONCAT(title) as title, GROUP_CONCAT(sold_quantity order by Scrap.stamp) as sold, GROUP_CONCAT(Scrap.stamp ORDER BY Scrap.stamp) as dates, GROUP_CONCAT(price ORDER BY Scrap.stamp) as prices, Category.path_from_root FROM Publication, Scrap, Category WHERE (path_from_root LIKE '%" + cat + "%' OR path_from_root LIKE '%" + cat + "%') AND Category._id = Publication.category_id AND Scrap.id = Publication.scrap GROUP BY(Publication._id) ORDER BY Publication._id LIMIT 1,10
If I remove the category table, the query is super fast. I have the ability to change the database structure. Category structure:
CREATE TABLE `Category` ( `id` int(11) NOT NULL AUTO_INCREMENT, `_id` varchar(300) NOT NULL, `name` varchar(300) DEFAULT NULL, `status` tinyint(4) NOT NULL DEFAULT '1', `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `total_items` int(11) NOT NULL DEFAULT '0', `active` tinyint(4) NOT NULL DEFAULT '1', `parse` tinyint(4) NOT NULL DEFAULT '0', `path_from_root` varchar(500) DEFAULT NULL, PRIMARY KEY (`id`) )