Re: Indexing problems

[KimSeong Loh]

I actually have no explanation on the index recreation, as in why it gets slower after a day and fast again after the index is recreated.

There is a "using filesort", this is required to sort the result based on the ORDER BY. If the result set is big and there is insufficient buffer for sorting, then the disk will be used in the sorting process.

See this http://dev.mysql.com/doc/mysql/en/Server_status_variables.html
Do a FLUSH STATUS
Do a SHOW STATUS and keep a copy of the data
Run your query once
Do another SHOW STATUS and compare with the earlier data,

Post the difference here, maybe we can find some hint.