| 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
|
|
|
|
| Re: Out of memory when query big table [message #609 is a reply to message #608 ] |
Fri, 19 January 2007 08:56  |
bc19148
Messages: 2
Registered: January 2007 |
Junior Member |
|
|
Hi,
Thanks for information. I read the Full-Text Search functions in MySQL 5.0 reference manual before, and I'm sure it said the Full-Text Search functions ONLY apply to MyISAM tables and not InnoDB tables. The table is currently in InnoDB engine. You also recommended to use external search engine, please provide a list of search engine so I can look for it.
I apologize if I asked the question to again, but after spent three days reading stuff through reference manual and web, and it still doesn't work, it can be painful.
Thanks again,
Bryan
|
|
|
|
Members
Search
Help
Register
Login
Home
