Sorry it's some mistakes with keys and table were not analyzed
Rick James Wrote:
-------------------------------------------------------
> This looks like a typo:
>
> -> AND cc.category_id = cc.category_id
Yes, it must be ci.category_id = cc.category_id
>
> so I don't trust the EXPLAIN
>
> What is this supposed to be?
>
> 'rub.'currency,
>
it's for easier php programming just insert into results value into resultset. (It's automaticly translated into html template)
>
> And this?
>
> i.enabled & ( i.quantity >1 ) AS onstock
this made for mark item as "on stock" or "available for sale" some items may be just turned off (enabled = 0) or it may be out of stock (quantity < 1) so we mark them as onstock = 0;
I may change it with only 1 field (or it may be regenerated after item update something like this)
>
>
> Probably not good to have ids be NULLable.
>
It was a wrong tables bottom of this message you can find right results
> Please try this on each table:
>
> ANALYZE TABLE tbl;
>
Done
>
> Looking back at the message of 2009-04-24 07:18,
> it appears that it picked the 'wrong' table to
> start with. The ANALYZE may fix that.
>
Yes it's =)
> How many rows in the result set? If it is more
> than about 40,000, the strange behaviour may be
> explainable -- that is, a table scan is probably
> better than jumping back and forth between the
> index and the data.
About 7000
Exec query differs between 0.4 sec and 2.0 secs...
So real data is:
mysql> SHOW CREATE TABLE category_childs;
ERROR 1317 (70100): Query execution was interrupted
It's strange but there is Mysql interrupted query itself.
mysql> SHOW CREATE TABLE category_childs;
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| category_childs | CREATE TABLE `category_childs` (
`parent_id` bigint(20) NOT NULL DEFAULT '0',
`category_id` bigint(20) NOT NULL DEFAULT '0',
`sortorder` bigint(20) DEFAULT NULL,
PRIMARY KEY (`parent_id`,`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE category_item;
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| category_item | CREATE TABLE `category_item` (
`category_id` int(11) NOT NULL DEFAULT '0',
`item_id` bigint(20) NOT NULL DEFAULT '0',
`sortorder` int(11) DEFAULT NULL,
PRIMARY KEY (`category_id`,`item_id`),
KEY `item_id` (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE item;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| item | CREATE TABLE `item` (
`id` bigint(20) NOT NULL DEFAULT '0',
`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,
PRIMARY KEY (`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 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
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 | 73728 | 0 | NULL | 2009-04-28 18:51:47 | 2009-04-28 18:52:41 | NULL | cp1251_general_ci | NULL | | |
| category_hierarchy | MyISAM | 10 | Fixed | 702 | 13 | 9126 | 3659174697238527 | 36864 | 0 | NULL | 2009-04-28 18:51:48 | 2009-04-28 18:51:48 | NULL | cp1251_general_ci | NULL | | |
| category_item | MyISAM | 10 | Fixed | 19592 | 17 | 333064 | 4785074604081151 | 708608 | 0 | NULL | 2009-04-28 18:51:48 | 2009-04-28 18:51:52 | NULL | 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.01 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 ic.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 | PRIMARY | PRIMARY | 8 | const | 198 | Using temporary; Using filesort |
| 1 | SIMPLE | ic | ref | PRIMARY,item_id | PRIMARY | 4 | podarkoff.cc.category_id | 33 | Using where |
| 1 | SIMPLE | i | eq_ref | PRIMARY | PRIMARY | 8 | podarkoff.ic.item_id | 1 | |
+----+-------------+-------+--------+-----------------+---------+---------+--------------------------+------+---------------------------------+
3 rows in set (0.00 sec)