Author Topic: MySQL anyone?  (Read 1527 times)

0 Members and 1 Guest are viewing this topic.

Offline Aginor

  • Spelljammer
  • 210
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
Member of the Scooby Doo model Fanclub "verticies and splines are the medium and he is the artist."

 

Offline blackhole

  • Still not over the rainbow
  • 29
  • Destiny can suck it
    • Black Sphere Studios
I've never encountered this before, but I checked the reference page and it said this:


Quote
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.

 

Offline Aginor

  • Spelljammer
  • 210
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  :)
Member of the Scooby Doo model Fanclub "verticies and splines are the medium and he is the artist."

 

Offline blackhole

  • Still not over the rainbow
  • 29
  • Destiny can suck it
    • Black Sphere Studios
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.

 

Offline Aginor

  • Spelljammer
  • 210
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....).
Member of the Scooby Doo model Fanclub "verticies and splines are the medium and he is the artist."

 

Offline Bobboau

  • Just a MODern kinda guy
    Just MODerately cool
    And MODest too
  • 213
what type of index are you using (hash/b-tree), and how big is the table?
Bobboau, bringing you products that work... in theory
learn to use PCS
creator of the ProXimus Procedural Texture and Effect Generator
My latest build of PCS2, get it while it's hot!
PCS 2.0.3


DEUTERONOMY 22:11
Thou shalt not wear a garment of diverse sorts, [as] of woollen and linen together

 

Offline Aginor

  • Spelljammer
  • 210
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!
Member of the Scooby Doo model Fanclub "verticies and splines are the medium and he is the artist."

 

Offline blackhole

  • Still not over the rainbow
  • 29
  • Destiny can suck it
    • Black Sphere Studios
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

 

Offline Aginor

  • Spelljammer
  • 210
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.  :)
Member of the Scooby Doo model Fanclub "verticies and splines are the medium and he is the artist."