Re: slowness around 3.4.1 and custom fields?

At Tue, 22 Mar 2005 16:50:12 -0500,
seph wrote:
> 
> I've got a 3.4.1 install against mysql, and I'm seeing a lot of
> slowness around certain custom field searches. It seems pretty
> reproducible by searching for a Custom Field IS Null. (Like Null is
> fine) It results in the mysql query: (here are the last 2 from the
> mysql-slow log)

Ick.

ObjectCustomFieldValues2 isn't kicking in because CustomField is the
first element.. but it's the join key so it isn't being picked up.
(Although this has to do with ObjectCustomFieldValues.CustomField not
being nullable I think.)




Try this one...

CREATE INDEX ObjectCustomFieldValues3
   ON ObjectCustomFieldValues( ObjectId, ObjectType, Disabled );

It creates a huge (261 byte) key because of ObjectType, which is a
negative for IO reasons.

You may want to try this instead:

CREATE INDEX ObjectCustomFieldValues4
   ON ObjectCustomFieldValues( ObjectId, Disabled );


You could also try this one.. but I don't think it'll help much.

CREATE INDEX Tickets7 ON Tickets( Type );

( I played with a few variants, such as...
   Type, EffectiveId
   Type, id
  but on my really poor test database, it didn't show much
  difference.  But, try these on your larger dataset.)

(Why won't it help?  Because Tickets.Type is going to have a very low
cardinality in most places.)


-R