Re: Please help with slow easy query

[Ilya Cheburaev]

I changed internal application rules of creation connections to db so it creates only 1 connection per request (earlier it created a lot of connections per request) and done some other application specific changes so it using now mysql much less but there is still some load of server.
tonight i'll try to upgrade php and apache so 'll see if it helps.
But there is still one factor that makes application work slow. It's a query. It is havy query and it's always appears in the slow-query.log (slow-time >= 1 s).
I already done with that query everything i found about optimization but it's still takes about 0.400 s to execute. And i think it's optimization of this query can really lower server's load.
Hope you can give me any ideas what to do with this query to optimize it.

So:

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 , i.name DESC


EXPLAIN:
+----+-------------+-------+------+-----------------+-----------+---------+--------------------------------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+-----------+---------+--------------------------------+-------+------------------------------------+
| 1 | SIMPLE | i | ALL | PRIMARY | NULL | NULL | NULL | 16028 | Using temporary; Using filesort |
| 1 | SIMPLE | ic | ref | PRIMARY,item_id | item_id | 8 | podarkoff.i.id | 1 | Distinct |
| 1 | SIMPLE | cc | ref | parent_id | parent_id | 16 | const,podarkoff.ic.category_id | 1 | Using where; Using index; Distinct |
+----+-------------+-------+------+-----------------+-----------+---------+--------------------------------+-------+------------------------------------+

mysql> describe item;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | 0 | |
| currency_id | varchar(3) | YES | | NULL | |
| vendor_id | int(11) | YES | MUL | NULL | |
| name | varchar(255) | YES | | NULL | |
| description | text | YES | MUL | NULL | |
| weight | float | YES | | NULL | |
| length | float | YES | | NULL | |
| width | float | YES | | NULL | |
| height | float | YES | | NULL | |
| price | float | YES | | NULL | |
| price1 | float | YES | | NULL | |
| price2 | float | YES | | NULL | |
| price3 | float | YES | | NULL | |
| price4 | float | YES | | NULL | |
| price5 | float | YES | | NULL | |
| price6 | float | YES | | NULL | |
| enabled | tinyint(1) | NO | MUL | 1 | |
| quantity | int(11) | YES | | NULL | |
| main_pic | varchar(100) | YES | | NULL | |
| create_date | datetime | YES | | NULL | |
| last_update | datetime | YES | | NULL | |
| adminka_id | bigint(20) | YES | | NULL | |
| alt_name | varchar(250) | YES | | NULL | |
| sex_id | int(11) | YES | | NULL | |
| supplier_id | int(11) | YES | | NULL | |
| only_selfdelivery | tinyint(1) | NO | | 0 | |
| sale | tinyint(1) | NO | MUL | 0 | |
| publicy_date | datetime | YES | | NULL | |
| with_descr | tinyint(1) | NO | MUL | 0 | |
| short_descr | varchar(128) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+-------+

mysql> describe category_hierarchy;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| category_id | int(11) | NO | PRI | 0 | |
| parent_id | int(11) | NO | PRI | 0 | |
| sortorder | int(11) | YES | MUL | NULL | |
+-------------+---------+------+-----+---------+-------+

mysql> describe category_childs;
+-------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| parent_id | bigint(20) | NO | MUL | 0 | |
| category_id | bigint(20) | NO | | 0 | |
| sortorder | bigint(20) | YES | | NULL | |
+-------------+------------+------+-----+---------+-------+

mysql> select count(*) from item;
+----------+
| count(*) |
+----------+
| 16028 |
+----------+

mysql> select count(*) from category_childs;
+----------+
| count(*) |
+----------+
| 4132 |
+----------+

mysql> select count(*) from category_hierarchy;
+----------+
| count(*) |
+----------+
| 703 |
+----------+


May be the goal of this query is not clear so:
We have categories:

cat1_level1
--cat2_level2
--cat3_level2
----cat4_level3
--cat5_level2

and we have items (products) inside this categories:
cat1_level1
--cat2_level2
----item1
----item2
----item3
--cat3_level2
----cat4_level3
----item4
----item5
----item6
--cat5_level2
----item7
----item8
----item9

And we need to show all items from category (for example) cat1_level1 so we have to find all items from child categories (recursively) and show:
item1
item2
item3
item4
item5
item6

it's not too hard but we have to order it by:
1) sale items (with sale flag shoul goes first of all - we have to sale it)
2) category (first there is items from cat2_level2 and then cat4_level3 (category_childs sortorder field))
3) item's own sortorder (item sortorder field)

about category_childs:
this table were created for fast search of all child categories.
It denormolizes DB but it helps to run this query faster.
It contains all childs of anly level down by tree for each category wich have childs. Looks something like this (with category replaced by ids):

parent_id | category_id
cat1_level1 |cat2_level2
cat1_level1 |cat2_level2
cat1_level1 |cat3_level2
cat1_level1 |cat4_level3
cat1_level1 |cat5_level2
cat3_level2 |cat4_level3

this query slow because of 3 things:
1) we have to sort by 4 fields. Without last 2 fields inside order by clause this query works 4x faster;
2) it's distincted because some items can be in 2 or more different categories.
3) mysql have to find all categories from category_childs then look for items of these categories from category_items and only them find all items.

Do you have any ideas of optimizing this query?