table full - how to set SQL_BIG_TABLE

[Willow]

Since my query is enormous, I get the error that the table is full. The mysql documentation tells me to set
SQL_BIG_TABLES = 1 before the query so that it uses the hard disk instead of memory. But I can't figure out how to do this in php. Anyone know how to do this or perhaps there's another way to fix this?

Thanks,

Willow

[freakysid]

I'm guesing here - but perhaps just send it as a mysql_query(); ie,

mysql_query("SQL_BIG_TABLES = 1");

The command should then hold for the life of the current connection ?

[arpith]

I believe this option can be set only when starting the mysql daemon. I don't think you can do this via a query... atleast thats how it worked for me. So, you'll probably have to contact your host to do that.

--big-tables 
Allow big result sets by saving all temporary sets on file. 
It solves most 'table full' errors, but also slows down the 
queries where in-memory tables would suffice. Since version 
3.23.2 MySQL is able to solve it automaticaly by using 
memory for small temporary tables and switching to disk 
tables where necessary.

[Willow]

It *does* work! Unfortunately, if one uses too many (like ALL) search criteria it freezes up and the results just never come :-(

So even though it might work for a "slightly" large query, it doesn't work for a HUGE query! I believe this is the only way to do it unless someone else has any other ideas?
I'll probably end up reducing the search criteria!

Thanks anyway.

Willow

[ibeblunt]

I posted this same issue on another site a few weeks ago, but I am still having this problem. What ticks me off is that I have a limit set in my query and I'm still getting that error.

SELECT tbl_content.contentID, tbl_content.contentheadline, tbl_content.contentlive, tbl_content.contentapproved, tbl_content.contentgolive, tbl_subsection.subsection FROM tbl_content, tbl_subsection WHERE tbl_content.contentsubsectionID = tbl_subsection.subsectionID ORDER BY contentgolive DESC LIMIT 0, 20

Weird...