Re: Server just hanging (Copying to tmp table)

[Thomas Deliduka]

I don't know if this is a question of query optimization for me anymore. I know that I am the originator of this bug. Now I only found this today so I don't know if my problem is fixed or not. I noticed on one of my servers there was one particular query that was causing all the problems. This one:

select DISTINCT xno_items.itemid, prod_ref, title, short_title, subtitle, promo, author, maincat, inventory, price, strikeprice, retprice, reteprice, saleperc, onsale, clearance, blockp, saledt, xno_items.added, updated, thumb_image, xno_manufacturers.mfg_name
from xno_items
left join xno_item_cat on xno_items.itemid = xno_item_cat.itemid
left join xno_categories on xno_item_cat.catid = xno_categories.catid
left join xno_manufacturers on xno_items.mfgid = xno_manufacturers.mfgid
LEFT JOIN xno_prod_index ON xno_items.itemid = xno_prod_index.itemid
where xno_items.active = 1 AND ((xno_prod_index.word ='thermometer')) AND xno_categories.active = 1 order by xno_prod_index.quan DESC, xno_prod_index.density DESC, xno_items.ordered DESC, xno_items.title;

I change the join order to this:
LEFT JOIN xno_prod_index ON xno_items.itemid = xno_prod_index.itemid
left join xno_item_cat on xno_items.itemid = xno_item_cat.itemid
left join xno_categories on xno_item_cat.catid = xno_categories.catid
left join xno_manufacturers on xno_items.mfgid = xno_manufacturers.mfgid

and instead of 9 seconds to execute the query, it took 0.14 seconds.

So far today the server has been humming along without any hangs problems.

I know enough to be dangerous. Thanks for being patient!