Out of memory when query big table

MySQL & MariaDB Other MySQL® Questions
1

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

2

You should not use LIKE search for large sizes. Use MySQL Full Text Search or some external search engine.

And buy some good MySQL book or read MySQL Manual Online.

You’re asking a question which is well documented.

3

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