Hard Light Productions Forums
Off-Topic Discussion => Programming => Topic started by: Aginor on January 19, 2010, 03:05:04 am
-
Hi guys,
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
-Aginor
-
I've never encountered this before, but I checked the reference page and it said this:
With EXPLAIN SELECT ... ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column.
If you haven't indexed your tables, that would probably explain the massive slowdowns. Add an index and things will go much faster.
-
That was my first thought also. There are already two indexes that could be used. (ev_id, ev_time)
And there is the problem: EXPLAIN tells me that I can't use them. But it doesn't tell me why. I even tried FORCE INDEX. But no luck.
Anyway, thanks for the answer :)
-
That was my first thought also. There are already two indexes that could be used. (ev_id, ev_time)
And there is the problem: EXPLAIN tells me that I can't use them. But it doesn't tell me why. I even tried FORCE INDEX. But no luck.
Anyway, thanks for the answer :)
when you create the tables you have to have one index set as the primary index or it gets confused, I think. You probably did this though so I haven't the faintest clue what's going on.
-
Yeah, I have a primary index. That's not the problem. I already checked that.
Thanks anyway. I guess I'll have to find a solution outside of the database (I'll omit the ORDER BY and do the sorting of the lines in PHP if everything else fails, that's still faster than waiting 20-40 seconds for a database query....).
-
what type of index are you using (hash/b-tree), and how big is the table?
-
Problem solved.
But fyi, Bobboau: The table has 33 million lines. Indices are b-tree, IIRC.
The problem was that mySQL didn't use any indices in the first place. Increasing the sort_buffer_size didn't work, but I solved the problem by creating a new compound index over su_id+ev_time. (the other way round it doesn't work that good) Also, sometimes I have to FORCE index, otherwise it doesn't use it.
It now 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!
-
It now runs smoothly (although there *could* be a problem with writing performance now, since I have redundant indices. I'll have to check that.)
Read speed >>> write speed
-
That's right, but we have a lot of writing in our DB, it is a special case.
Anyway, it seems that it doesn't matter, it works. :)