Rick James Wrote:
-------------------------------------------------------
> If you want help with the query, please provide
> * SHOW CREATE TABLE tbl\G -- Not 'describe'; all
> relevant tables
> * SHOW TABLE STATUS LIKE 'tbl'\G -- need to see
> sizes
> * EXPLAIN SELECT ...\G
> and surround them with [ code ] and [ / code ]
SHOW CREATE TABLE category_childs;
CREATE TABLE `category_childs` (
`parent_id` bigint(20) DEFAULT NULL,
`category_id` bigint(20) DEFAULT NULL,
`sortorder` bigint(20) DEFAULT NULL,
KEY `parent_id` (`parent_id`,`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
SHOW CREATE TABLE category_item;
CREATE TABLE `category_item` (
`category_id` int(11) DEFAULT NULL,
`item_id` bigint(20) DEFAULT NULL,
`sortorder` int(11) DEFAULT NULL,
KEY `item_id` (`item_id`),
KEY `category_id` (`category_id`,`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
SHOW CREATE TABLE item;
CREATE TABLE `item` (
`id` bigint(20) DEFAULT NULL,
`currency_id` varchar(3) DEFAULT NULL,
`vendor_id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`description` text,
`weight` float DEFAULT NULL,
`length` float DEFAULT NULL,
`width` float DEFAULT NULL,
`height` float DEFAULT NULL,
`price` float DEFAULT NULL,
`price1` float DEFAULT NULL,
`price2` float DEFAULT NULL,
`price3` float DEFAULT NULL,
`price4` float DEFAULT NULL,
`price5` float DEFAULT NULL,
`price6` float DEFAULT NULL,
`enabled` tinyint(1) NOT NULL DEFAULT '1',
`quantity` int(11) DEFAULT NULL,
`main_pic` varchar(100) DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`adminka_id` bigint(20) DEFAULT NULL,
`alt_name` varchar(250) DEFAULT NULL,
`sex_id` int(11) DEFAULT NULL,
`supplier_id` int(11) DEFAULT NULL,
`only_selfdelivery` tinyint(1) NOT NULL DEFAULT '0',
`sale` tinyint(1) NOT NULL DEFAULT '0',
`publicy_date` datetime DEFAULT NULL,
`with_descr` int(11) DEFAULT NULL,
`short_descr` varchar(128) DEFAULT NULL,
UNIQUE KEY `id` (`id`),
KEY `vendor_id` (`vendor_id`),
KEY `price` (`enabled`,`price`),
KEY `with_descr` (`with_descr`,`enabled`),
KEY `sale` (`sale`),
FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
mysql> SHOW TABLE STATUS LIKE 'category_%'
-> ;
+--------------------+--------+---------+------------+-------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------------+--------+---------+------------+-------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| category_childs | MyISAM | 10 | Fixed | 2740 | 25 | 68500 | 7036874417766399 | 105472 | 0 | NULL | 2009-04-27 18:58:57 | 2009-04-27 19:00:22 | 2009-04-27 19:01:29 | cp1251_general_ci | NULL | | |
| category_hierarchy | MyISAM | 10 | Fixed | 702 | 13 | 9126 | 3659174697238527 | 41984 | 0 | NULL | 2009-04-27 18:58:58 | 2009-04-27 18:58:58 | 2009-04-27 19:01:29 | cp1251_general_ci | NULL | | |
| category_item | MyISAM | 10 | Fixed | 19609 | 17 | 333353 | 4785074604081151 | 1176576 | 0 | NULL | 2009-04-27 18:58:58 | 2009-04-27 18:59:10 | 2009-04-27 19:01:29 | cp1251_general_ci | NULL | | |
| category_itemtmp | MyISAM | 9 | Fixed | 515 | 17 | 8755 | 73014444031 | 1024 | 0 | NULL | 2009-01-29 11:02:25 | 2009-01-29 11:02:57 | NULL | cp1251_general_ci | NULL | | |
| category_keyword | MyISAM | 9 | Dynamic | 1160 | 42 | 48864 | 4294967295 | 12288 | 0 | NULL | 2005-08-24 03:02:34 | 2007-01-19 00:34:44 | 2007-01-19 00:35:12 | cp1251_general_ci | NULL | | |
+--------------------+--------+---------+------------+-------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
5 rows in set (0.00 sec)
mysql> EXPLAIN SELECT DISTINCT i . * , 'rub.'currency, i.enabled & ( i.quantity >1 ) AS onstock
-> FROM item i, category_item ic, category_childs cc
-> WHERE i.id = ic.item_id
-> AND cc.category_id = cc.category_id
-> AND cc.parent_id =6880
-> ORDER BY i.sale DESC , i.enabled DESC , cc.sortorder, ic.sortorder;
+----+-------------+-------+------+---------------+-----------+---------+----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+----------------+-------+----------------------------------------------+
| 1 | SIMPLE | cc | ref | parent_id | parent_id | 9 | const | 218 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | i | ALL | id | NULL | NULL | NULL | 15896 | Using join buffer |
| 1 | SIMPLE | ic | ref | item_id | item_id | 9 | podarkoff.i.id | 1 | Using where; Distinct |
+----+-------------+-------+------+---------------+-----------+---------+----------------+-------+----------------------------------------------+
3 rows in set (0.02 sec)
mysql> Select count(*) from item;
+----------+
| count(*) |
+----------+
| 15896 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from category_item;
+----------+
| count(*) |
+----------+
| 19609 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from category_childs;
+----------+
| count(*) |
+----------+
| 2740 |
+----------+
1 row in set (0.00 sec)
mysql> Aborted