MySQL anyone?

[Aginor]

Hi guys,

I have been searching SQL manuals and other 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

 

[KrisV]

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Maybe this applies here:

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

I don't know if that page offers any practical help beyond what you've already tried (increasing buffer sizes). Try putting a composite index on ev_time + su_ID?

 

[Aginor]

No, unfortunately there wasn't much for that particular problem.
But I solved it anyway.

sort_buffer_size didn't do the job, but the composite index did. I had to put it the other way round, though (su_id + ev_time).

Now everything runs smoothly. (although there *could* be a problem with writing performance now, since I have redundant indices. I'll have to check that.)

Thanks for your input!