MySQL Memory Problems When Sorting Multiple Columns

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 392,045 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,283 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MySQL advertiser:

Thread Tools Display Modes

Views: 329 | Replies: 1

•

Join Date: Apr 2008

Posts: 1

Reputation: WayCoolJr is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

WayCoolJr

Offline

Newbie Poster

MySQL Memory Problems When Sorting Multiple Columns

Apr 4th, 2008

I am running mySQL Server 5.0.37 on a Windows 2003 Server.

I have a table that I want sorted by three columns (column1, then column2, then column3).

I can sort one column just fine. But, if the recordset is large, sorting by two (or more) columns crashes the mySQL Server Service.

Through research it looks like I am having a memory problem as the following error message is written to the MySQL error log:

Help with Code Tags

(Toggle Plain Text)

080325 14:27:10 [ERROR] C:\SWsoft\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Out of memory; restart server and try again (needed 32776 bytes)

The my.ini file shows the following values:

Help with Code Tags

(Toggle Plain Text)

sort_buffer_size=256K
sort_buffer=256K

Oddly enough, phpMyAdmin shows the following:

Help with Code Tags

(Toggle Plain Text)

sort buffer size 32,776

I increased the sort_buffer_size and sort_buffer in my.ini to 512k, but this did not help.

I am also concerned that if I successfuly increase the buffer sizes too big, I will have problems with my Windows Server (512MB) if I have a large number of users running this query at the same time.

Any help on how I can resolve this would be greatly appreciated.