[Rt-devel] slowness around 3.4.1 and custom fields?

[seph]

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)

SELECT DISTINCT main.* FROM ((Tickets main  LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1  ON ((ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket')) AND( (ObjectCustomFieldValues_1.Disabled = '0')) AND(  main.id = ObjectCustomFieldValues_1.ObjectId))  JOIN CustomFields CustomFields_2  ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id) AND( (CustomFields_2.Name = 'Release Notes')))   WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (ObjectCustomFieldValues_1.Content IS NULL)OR(ObjectCustomFieldValues_1.Content IS NULL) ) ) ORDER BY main.id ASC;

SELECT COUNT(DISTINCT main.id) FROM ((Tickets main  LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1  ON ((ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket')) AND( (ObjectCustomFieldValues_1.Disabled = '0')) AND(  main.id = ObjectCustomFieldValues_1.ObjectId))  JOIN CustomFields CustomFields_2  ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id) AND( (CustomFields_2.Name = 'Release Notes')))   WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (ObjectCustomFieldValues_1.Content IS NULL)OR(ObjectCustomFieldValues_1.Content IS NULL) ) );

which then takes 2.5 minutes to run, during which apache times out
giving an error, the user hits reload, and I end up with a bunch of
spinning locks.

Explaining either of them gives:

+---------------------------+--------+---------------+---------+---------+---------------------------------------+-------+----------------------------------------------+
| table                     | type   | possible_keys | key     | key_len | ref                                   | rows  | Extra                                        |
+---------------------------+--------+---------------+---------+---------+---------------------------------------+-------+----------------------------------------------+
| main                      | ALL    | NULL          | NULL    |    NULL | NULL                                  |  3660 | Using where; Using temporary; Using filesort |
| ObjectCustomFieldValues_1 | ALL    | NULL          | NULL    |    NULL | NULL                                  | 15704 | Using where; Distinct                        |
| CustomFields_2            | eq_ref | PRIMARY       | PRIMARY |       4 | ObjectCustomFieldValues_1.CustomField |     1 | Using where; Distinct                        |
+---------------------------+--------+---------------+---------+---------+---------------------------------------+-------+----------------------------------------------+

I am running the query cache, and it shows no low memory prunes. mysql
is the debian build, version 4.0.21-1, SearchBuilder is 1.22

Checking my indexes, I had all the ones in the schema, except "CREATE
INDEX Links4 ON Links (Type,LocalBase);"

Even stranger is that RT seems to have decided to run that query every
time I display a ticket. But only me, not all the users.

Any ideas? Is there another index that should get added?

seph