Fixed Additional Index for deleting



I don't know if it should be in bugreports or suggestions - but:

We had big problems while deleting medias in XFMG. This took 1-2 minutes and resulted in server-errors:
Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction

After some digging we found out that the problem is this SQL:
SELECT * FROM xengallery_comment WHERE rating_id = 199439;

Unfortunately, there is no index on rating_id, therefor every time there hase to be done a full table scan for this. We have about 1,7 Mio entries here and this results in the problems described above.
We added an index and now everything works without an error.

So we highly suggest to add that index in the next update.

Chris D

XenForo developer
Staff member
Thank you, we've added this for the next release.

As you have quite a large table, you might prefer to run the query to add the index now (if you haven't already):

ALTER TABLE xengallery_comment ADD INDEX rating_id (rating_id)

When the query happens during the upgrade, it will just silently fail so there shouldn't be a problem adding it now. With such a big table, it might be worth doing it directly in mysql.

Thanks :)
Last edited: