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`)
)
category
table? It would also help if you added theSHOW CREATE TABLE table_name;
output for all 3. – ypercubeᵀᴹ Sep 29 '16 at 23:25