MySQL Explain:什么导致“使用临时”;使用filesort”

I'm planning on creating a view using this SQL SELECT, but the explain for it shows it's using temporary and using filesort. I can't figure out what indices I need in order to fix this problem. Mostly, I'm wondering why it's using filesort intead of using an index to sort.I'm planning on creating a view using this SQL