I have been searching forums about this for a few days now, and I thought I could ask you, maybe there are some database professionals around here. So if you have experience with SQL, especially MySQL, I would appreciate your help.
I have a DB and I do quite normal statements like
SELECT ev_id, ev_time, su_id FROM event_table WHERE su_ID = 384 ORDER BY ev_time;
But recently I tested some function of my php interface and wondered why it was very slow. (27s)
So I used the EXPLAIN function of MySQL and I realised that the ORDER BY expression causes MySQL to sort on the file system instead of the RAM (EXPLAIN says "file sort"). That normally happens when ordering by a column that contains a varchar or something similar, but ev_time is tinyint...
I already tried setting the "sort_buffer" variable to something higher (16 MB instead of 1) but nothing happens.
I wondered if there could be something wrong with the indices I am using (both ev_id and ev_time are indexed), but I can't see anyting going wrong. I don't know why MySQL is using filesort.
Does anyone of you know that problem and how I could solve it?
Thanks in advance