[SOLVED] Using temporary; Using filesort -- odd issue

[phpknight]

I am trying to optimize every database query I run.  Here is an issue somebody might be able to help me with.  I have a query that returns 51 rows.  If I request LIM 0, 25 or even LIM 0, 51, my EXPLAIN statements come back great.  However, if I do the same query with LIM 0, 100, then it adds

 

Using where; Using temporary; Using filesort

 

Why would this be the case?  The application is simply allowing the uses to select how many items per page, and 100 is one of the default values.

 

I've tried using a straight join and switching the table order.  That helps get rid of some of them, but firesort remains.

[mezise]

Hi,

 

you sort the result set. My first guess is that for "LIMIT 0, 100" sorting data size is too big to be processed in memory buffers. Try to increase variables from this statement:

 

SHOW VARIABLES LIKE "%sort%";

 

Michal

[phpknight]

I did what you said.  Here is the result.  Let me know which variables look off to you.

 

max_length_for_sort_data 1024

max_sort_length 1024

myisam_max_sort_file_size 9223372036854775807

myisam_sort_buffer_size 67108864

sort_buffer_size 2097144

 

The sort is being done on a varchar (99) field or by date.

[mezise]

Bigger sort_buffer_size should prevent using filesort. But remember that size of all memory variables should depends on RAM size available in your server for MySQL engine. If you increase one variable to speed up one feature it may slow down performance of other feature. You'll find more after googling eg. "optimizing mysql variables".

 

To speed up filesort (not to prevent) increase read_rnd_buffer_size variable or use faster hard disk or use two hard disks and use tmpdir variable to define two paths to temporary directories on these disks.

 

Finally you may create indexes on sorted columns.

 

Michal

[phpknight]

Okay, I will look into those things.