2

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`)
) 
  • 1
    What tree model are you storing the categories in? Do you have the possibility to change the schema or just the query? Can you update the question with the structure of the tables? – Károly Nagy Sep 29 '16 at 8:55
  • yes, the table structure is indicated already – user1532587 Sep 29 '16 at 17:16
  • You haven't answered Karoly's questions. What model are you using? Seems like the Materilazed Path model but we can't know if you don't tell us. – ypercubeᵀᴹ Sep 29 '16 at 18:36
  • @ypercubeᵀᴹ I'm using Model Tree Structures with Materialized Paths. Each category has a relation to its parent and a path from the root to itself – user1532587 Sep 29 '16 at 23:19
  • I don't see a parent_id though. Can you add in the question a few sample rows from the category table? It would also help if you added the SHOW CREATE TABLE table_name; output for all 3. – ypercubeᵀᴹ Sep 29 '16 at 23:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.