Search mysql category tree

Question

I have the following schema in MySQL 5.1

CREATE TABLE  `mytest` (
  `category` varchar(32) ,
  `item_name` varchar(255)
  KEY `key1` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

Category Column

filled in this way

[:parent_parent_cat_id][:parent_cat_id][:leaf_cat_id]

10000200003000

if you can search all categories: parent_parent_category_id

SELECT * FROM mytest WHERE category LIKE "10000%";

using index key1;

but How to use an index when I want to search: parent_cat_id?

SELECT * FROM mytest WHERE category LIKE "%20000%";

Do you have any better solutions?

Answer 1

This model is called materialized path.

You just need to categoriesin a separate table and instead itemsrefer to this table, not the path:

SELECT  i.*
FROM    categories c
JOIN    items i
ON      i.category = c.id
WHERE   c.path BETWEEN
        (
        SELECT  path
        FROM    categories ci
        WHERE   ci.id = '20000'
        )
        AND
        CONCAT(
        (
        SELECT  path
        FROM    categories ci
        WHERE   ci.id = '20000'
        ), ':')

Answer 2

The second query cannot use the index.

, , , . MySQL, .

MySQL Quassnoi.