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?
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.)
sorry for the delay, it took me a couple days to find a test mysql.
Thank you for the index suggestions, they do seem to fix it.
CREATE INDEX ObjectCustomFieldValues3
ON ObjectCustomFieldValues( ObjectId, ObjectType, Disabled );
This shall be called index 1
CREATE INDEX ObjectCustomFieldValues4
ON ObjectCustomFieldValues( ObjectId, Disabled );
and this index 2
CREATE INDEX Tickets7 ON Tickets( Type );
and this index 3
As a test case: I dropped the test db, restored from backup, applied
whichever of the indexes, than ran my slow query. I did this 5 times
for ever possible permutation of the indexes. Here are the averages
(in seconds):
It creates a huge (261 byte) key because of ObjectType, which is a
negative for IO reasons.
I don’t think index maintaince really hurts me any, so I’m just going
to create all three. I assume whichever ones are proper will make it
into rt 3.4.2
Of course, here’s another slow query that appears unhelped by those indexes:
SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2 WHERE ((Transactions_1.ObjectType = ‘RT::Ticket’)) AND ((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ( ( (Attachments_2.Content LIKE ‘%customer%’)AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) OR(main.Subject LIKE ‘%customer%’));
explain says this:
| table | type | possible_keys | key | key_len | ref | rows | Extra |
| main | ref | PRIMARY,Tickets4,Tickets5,Tickets7 | Tickets7 | 17 | const | 1921 | Using where |
| Transactions_1 | ref | PRIMARY,Transactions1 | Transactions1 | 64 | const | 21362 | Using where; Using index |
| Attachments_2 | ALL | Attachments2 | NULL | NULL | NULL | 27442 | Using where |
SELECT COUNT(DISTINCT main.id)
FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2
WHERE ((Transactions_1.ObjectType = ‘RT::Ticket’))
AND ((main.EffectiveId = main.id))
AND ((main.Status != ‘deleted’))
AND ((main.Type = ‘ticket’))
AND ( (
(Attachments_2.Content LIKE ‘%customer%’)
AND(Attachments_2.TransactionId = Transactions_1.id)
AND(main.id = Transactions_1.ObjectId)
)
OR(main.Subject LIKE ‘%customer%’));
the slow logs say it took 30 minutes to run.
Eeep.
This one is harder to fix, and I don’t have the right shaped database
to test on. The LIKEs on Content and Subject are very expensive…
Off the top of my head… you could try this:
CREATE INDEX Transactions2 ON Transactions ( ObjectType, id );
but I don’t think it’ll help much (or at all)
What are your Tickets4 and Tickets5 indexes? They’re not in my schema
file.
We could also try creating something like
CREATE INDEX Tickets8 ON Tickets ( Type, EffectiveId, Status );
Maybe its time to look at the MySQL full text search capabilities
again.
SELECT COUNT(DISTINCT main.id)
FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2
WHERE ((Transactions_1.ObjectType = ‘RT::Ticket’))
AND ((main.EffectiveId = main.id))
AND ((main.Status != ‘deleted’))
AND ((main.Type = ‘ticket’))
AND ( (
(Attachments_2.Content LIKE ‘%customer%’)
AND(Attachments_2.TransactionId = Transactions_1.id)
AND(main.id = Transactions_1.ObjectId)
)
OR(main.Subject LIKE ‘%customer%’));
the slow logs say it took 30 minutes to run.
Eeep.
This one is harder to fix, and I don’t have the right shaped database
to test on. The LIKEs on Content and Subject are very expensive…
CREATE INDEX Transactions2 ON Transactions ( ObjectType, id );
but I don’t think it’ll help much (or at all)
It won’t because of the ‘%’ in front of the part you want to search on.
It will your index search and most of not all databases will revert to
full table scans hence the 30min runtime.
Oracle has the same problem and I cheated on two fronts to get out of
this kind of problems:
I use the full text search capabilities of Oracle
hacked the searchbuilder part where the ‘%’ are pre and post pended to
the query string.
I told my people to use explicit ‘%’ when they want to search with
wildcards AND told them that if they search on Content AND use a
wildcard in front of their search that they are gone wait a bit and that
their collegues would get no more coffee for them
So the way to go for mysql is to use full text indices.
If anyone is interested in what I have done for Oracle I could make a
patch, which is not general, I don’t know how to check if the dbh is an
Oracle connection or not. If this is possible then the patch should work
for anybody whether you’re using Oracle or not.
