SELECT * FROM category c LEFT JOIN category_description cd ON (c.category_id = cd.category_id) WHERE c.parent_id = '47' AND cd.language_id = '2' AND c.status = '1' ORDER BY c.sort_order, LCASE(cd.name)

d 	select_type 	table 	partitions 	type 	possible_keys 	key 	key_len 	ref 	rows 	filtered 	Extra 	
1 	SIMPLE 	c 	NULL	ref 	PRIMARY,parent_id 	parent_id 	4 	const 	7 	10.00 	Using where; Using temporary; Using filesort
1 	SIMPLE 	cd 	NULL	ref 	PRIMARY 	PRIMARY 	8 	shop.c.category_id,const 	3 	100.00 	NULL

id 	select_type 	table 	partitions 	type 	possible_keys 	key 	key_len 	ref 	rows 	filtered 	Extra 	
1 	SIMPLE 	c 	NULL	ref 	PRIMARY,parent_id 	parent_id 	4 	const 	7 	10.00 	Using where
1 	SIMPLE 	cd 	NULL	ref 	PRIMARY 	PRIMARY 	8 	shop.c.category_id,const 	3 	100.00 	NULL

category 	CREATE TABLE `category` (
 `category_id` int(11) NOT NULL AUTO_INCREMENT,
 `image` varchar(255) DEFAULT NULL,
 `parent_id` int(11) NOT NULL,
 `top` tinyint(1) NOT NULL,
 `column` int(3) NOT NULL,
 `sort_order` int(3) NOT NULL,
 `status` tinyint(1) NOT NULL,
 `date_added` datetime DEFAULT NULL,
 `date_modified` datetime DEFAULT NULL,
 PRIMARY KEY (`category_id`),
 KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM AUTO_INCREMENT=166 DEFAULT CHARSET=utf8
category_description 	CREATE TABLE `category_description` (
 `category_id` int(11) NOT NULL,
 `language_id` int(11) NOT NULL,
 `name` varchar(255) NOT NULL,
 `description` text NOT NULL,
 `meta_description` varchar(255) NOT NULL,
 `meta_keyword` varchar(255) NOT NULL,
 `seo_title` varchar(255) NOT NULL,
 `seo_h1` varchar(255) NOT NULL,
 `meta_title` varchar(255) NOT NULL,
 `page_h1` varchar(255) NOT NULL,
 PRIMARY KEY (`category_id`,`language_id`,`name`) USING BTREE,
 KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8