MySQL Performance: MySQL => Out of memory when query big table

Out of memory when query big table [message #607]

Thu, 18 January 2007 21:47

bc19148
Messages: 2
Registered: January 2007

Junior Member

Hi Peter,

I am new to MySQL. I installed MySQL 5.0 and used the InnoDB engine. It currently have just one table with 39 columns, combination of varchar, datetime, double, and text data-types. At the DOS prompt, I ran this simple query (“select * from main where name like ‘%abc%’ or description like ‘%abc%’ order by cost;) with 20k rows, it ran pretty fast (2 seconds); but when the table grows to 800k rows, the query took over a minute. I tried the EXPLAIN method, but not sure what those values meant.

Another thing, while the SQL is executing, I monitored the Windows Task Manger and saw the mysqld-nt.exe went from 27K Memory Usage to 306K, and ran out of memory on 512MB RAM. I also added a primary key field and index it, but it doesn’t help. I read through your “Why MySQL could be slow with large tables?” blog, but still not sure what to do. There must be a way to execute the sql faster because the table will grows to 2M rows or more. Please help and let me know what I need to do.

Thanks,
Bryan

Report message to a moderator