0

I've got a very large table in a mysql database (several million rows). Each row has a date it's been created as a field. I would like to extract rows from 2011 until today. the query I'm using looks something like this:

select data1, data1, data3, data4, data5, data6 
  from db.table 
 where date(data3) between '2011-10-01' and '2012-08-06'

I get to about 3 million rows fetched in mySQL Query Browser before the browser crashes and i get a Glib error (gmem.c:173 failed to allocate 50080000 bytes. aborting.)

Is it simply because I don't have enough memory? Are there any tricks or performance enhancing queries anyone can suggest?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
user1563414
  • 91
  • 1
  • 3
  • 9
  • First of all you dont want 3m records to be retrieved by the mysql browser. You usually want to decrease the number of rows retrieved as much as possible. First of all it's not a problem of lack of memory but the lack of primary thought of what is needed to be retrieved so basically you gotta think what you want and how you want it more carefully. – Sergey Benner Aug 06 '12 at 15:54

1 Answers1

0

The query browser you are using seems to be fetching the whole multi-million-row result set into RAM. If for some reason you must do this you're going to need more RAM.

The question is this: what are you doing with this result set? Are you saving it to a file? If so, then use the MySQL browser feature for doing that. It won't need nearly as much memory as downloading the whole result set to RAM.

Edit: The MySQL Query Browser is being retired by MySQL / Oracle. You probably should find some other program to extract your rows to a CSV (Excel) file. Some tools will also create XLS files if that's what you prefer.

Here is the correct question and answers:

How to output MySQL query results in CSV format?

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Yes, very good follow up. I would like to export the results to an Excel file. Could you please tell me how to "use the MySQL browser feature" – user1563414 Aug 06 '12 at 16:05