Hi,
shredding users, the longest database part was this query:
SELECT main.* FROM Attachments main WHERE (main.Creator = ‘123’) ORDER
BY main.id ASC
Beside maybe mentioning an index on Attachments.Creator for shredder and
maybe omit the sorting, wouldn’t it be better to “SELECT main.id FROM
Attachments …” which wouldn’t send the Content column?
Chris
Hi,
shredding users, the longest database part was this query:
SELECT main.* FROM Attachments main WHERE (main.Creator = ‘123’) ORDER
BY main.id ASCBeside maybe mentioning an index on Attachments.Creator for shredder and
maybe omit the sorting, wouldn’t it be better to “SELECT main.id FROM
Attachments …” which wouldn’t send the Content column?
Hi,
It depends on the RT instance you have. Have you analysed the query plan of original query? How about the proposed solutions?
Without looking at that, it sounds like that the first two things you suggested about the index and sorting would provide way more gain than just removing columns from the retrieval list if it’s for a RT instance where attachments are basically small (about a few hundreds of KB).
Cheers,
Wallace Reis/wreis
Software Engineer
signature.asc (455 Bytes)
Hi,
shredding users, the longest database part was this query:
SELECT main.* FROM Attachments main WHERE (main.Creator = ‘123’)
ORDER BY main.id ASCBeside maybe mentioning an index on Attachments.Creator for
shredder and maybe omit the sorting, wouldn’t it be better to
“SELECT main.id FROM Attachments …” which wouldn’t send the
Content column?Hi,
It depends on the RT instance you have. Have you analysed the
query plan of original query? How about the proposed solutions?
Without looking at that, it sounds like that the first two things
you suggested about the index and sorting would provide way more
gain than just removing columns from the retrieval list if it�s for
a RT instance where attachments are basically small (about a few
hundreds of KB).
There’s no reason to not limit the set of columns down. The index
will probably help the most, I agree – removing the sort is unlikely
to make much difference, as sorting by the primary key is pretty cheap
in most databases.
Hi,
shredding users, the longest database part was this query:
SELECT main.* FROM Attachments main WHERE (main.Creator = ‘123’)
ORDER BY main.id ASCBeside maybe mentioning an index on Attachments.Creator for
shredder and maybe omit the sorting, wouldn’t it be better to
“SELECT main.id FROM Attachments …” which wouldn’t send the
Content column?Hi,
It depends on the RT instance you have. Have you analysed the
query plan of original query? How about the proposed solutions?
Without looking at that, it sounds like that the first two things
you suggested about the index and sorting would provide way more
gain than just removing columns from the retrieval list if it�s for
a RT instance where attachments are basically small (about a few
hundreds of KB).There’s no reason to not limit the set of columns down. The index
will probably help the most, I agree – removing the sort is unlikely
to make much difference, as sorting by the primary key is pretty cheap
in most databases.
- Alex
Hi,
attached some details on query times and index creation.
Without this index shred the user with the WebUI results in killing
mysqld in my case. Shred the user with sbin/rt-shredder takes constantly
30 minutes due to the Attachments query.
After creating the index shred a user with the WebUI takes 1 minute.
I think you should highly recommend the index in the shredder
documentation or even think about adding this index for all users.
Chris
rt-shredder-attachments-index.txt (1.83 KB)
attached some details on query times and index creation.
Without this index shred the user with the WebUI results in killing
mysqld in my case. Shred the user with sbin/rt-shredder takes constantly
30 minutes due to the Attachments query.
After creating the index shred a user with the WebUI takes 1 minute.I think you should highly recommend the index in the shredder
documentation or even think about adding this index for all users.
I’ve added this index to the list of suggested indexes in the shredder
documentation (commit 3d7e596 on 4.0-trunk); we’ll discuss if it makes
sense to apply more generally.