mysql category tree search
- by ffffff
I have the following schema on MySQL 5.1
CREATE TABLE `mytest` (
`category` varchar(32) ,
`item_name` varchar(255)
KEY `key1` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
category column is filled with like that
[:parent_parent_cat_id][:parent_cat_id][:leaf_cat_id]
"10000200003000"
if you can search all of the under categories :parent_parent_category_id
SELECT * FROM mytest WHERE category LIKE "10000%";
it's using index key1;
but
How to use index when I wanna search :parent_cat_id?
SELECT * FROM mytest WHERE category LIKE "%20000%";
Do you have a better solutions?
