[Apc-aa-coders] Strange problem with indexes on content table

[Marek T. <ma...@gn...>]

Hi,
I had a weirdest problem today. After an upgrade from MySQL 3.23 to=20
4.0.24_Debian-10sarge1 I have experienced an unbelievable perfrormance=20
drop with ActionApps. The content table has almost 1Gb which makes any=20
problems very noticeable. Even this query (no wildcards %) took about 20=20
sec.:

SELECT DISTINCT item.id as itemid ,item.publish_date,c1.number FROM item=20
INNER JOIN content as c0 ON (c0.item_id=3Ditem.id AND=20
(c0.field_id=3D'unspecified....1' OR c0.field_id is NULL)) INNER JOIN=20
content as c1 ON (c1.item_id=3Ditem.id AND (c1.field_id=3D'start_date....=
.2'=20
OR c1.field_id is NULL)) WHERE item.slice_id IN ( 'RX=E5=1Cp=87~=EDJ=10=B6=
=D4 |=06=F3' )=20
AND ( ( item.status_code=3D1 AND ( item.publish_date <=3D '1144061000' OR=
=20
item.publish_date IS NULL ) AND (item.expiry_date > '1144061000' OR=20
item.expiry_date IS NULL) ) ) AND ( (c0.number =3D "1") ) ORDER BY=20
item.publish_date DESC,c1.number DESC

The EXPLAIN showed:
c0 ALL item_id NULL NULL NULL 823268 Using where; Using temporary; Using=20
filesort
item eq_ref PRIMARY,slice_id_2,expiry_date PRIMARY 16 c0.item_id 1 Using=20
where
c1 ref item_id item_id 16 item.id 8232 Using where

I've never seen the NULL NULL there and obviously, using temporary=20
tables on 823268 records from the 1GB content table is a highway to=20
hell. Since the JOIN joins c0 on item_id, I tried to add another index=20
for content table called itemid, indexing solely the index_id column.=20
This in theory should not help at all, but in fact it made a dramatic=20
change, now it works correctly (on my other AA installations it also=20
works similar to this):

item range PRIMARY,slice_id_2,expiry_date slice_id_2 27 NULL 8032 Using=20
where; Using temporary; Using filesort
c0 ref item_id,itemid itemid 16 item.id 13 Using where
c1 ref item_id,itemid item_id 16 item.id 13 Using where

Anybody has any idea why this could be ? I'll remove the itemid index=20
later too see if it just was a matter of pushing mysql query optimizer=20
but at the moment I'm just happy.

Best
Marek