You still have not shown
SHOW TABLE STATUS LIKE 'item'\G
The size of item could indicate that you don't have enough ram to cache everything, which would imply that you are hitting the disk more than you would like.
You have now presented three different flavors of the SELECT in question -- the ORDER BY changed; this could be significant. :(
Have you tried removing DISTINCT? It is probably redundant.
7000 rows returned -- The EXPLAIN implies that it is hitting 3 tables, ending with 7000 hits on the last table (item). This would be 7000 probes into the index (probably pretty well cached in the key_buffer), plus 7000 probes into the data (can't tell how well cached).
At worst, I would expect on the order of 7K disk hits, which could be roughly 7 seconds. Since your timing is better than that, I would guess that much of the data is cached.
So, what it is doing?
1. Hit the first two tables (pretty efficiently),
2. Randomly(?) fetch 7000 rows from item
3. Put those rows, plus the 4 extra fields (rub., onstock, sortorder, sortorder) in a tmp table (biggest number in the Profile)
4. Sort the rows (could not use any index, since it needed to find the rows by "id") -- this was only 70ms.
I would say that key_buffer_size = 100M is about right for your 1GB of RAM, and the table sizes. Probably no other tunables matter.
Do you need all 7000 rows? Do you need all the columns if 'item'?
One trick that _sometimes_ helps in a situation like this: Fetch the ids, then fetch the rows.
SELECT i2. * ,
'rub.' currency,
i2.enabled & ( i2.quantity >1 ) AS onstock
FROM item i2,
( SELECT DISTINCT i.id
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
) i1
WHERE i2.id = i1.id
That way the "tmp" table has less to haul around, possibly saving enough to speed up the overall query. Also, it may lead to using a MEMORY tmp table, instead of MyISAM (and possibly disk).
Aha -- `description` text -- this prevents the use of MEMORY for the tmp table.
Another tidbit -- don't use BIGINT unless your really need to. It takes 8 bytes; INT takes only 4. Smaller -> more cacheable -> fewer disk hits -> faster.
If my suggested rewrite of the query provides the desired rows, I would be quite interested to see what the Profile looks like.