MySQLyog Timing Out. How can I change the settings?

I'm using MySQLyog as an interface. I need to add an index to a table that's very large. I understand that in order to do this, the system is making a temporary copy of the table and that' s going to take some time given the amount of data that I've got. But regardless of how much time it takes, I'm confident this is a good and necessary step.

So, I run this command: "ALTER TABLE myable ADD INDEX ( posted_day );"

...and I get this error: "Error Code : 1878 Temporary file write failure. (524429 ms taken)"

I'm thinking this is because of the 250,000,000 rows I've got in the table.

How can I change my connection times so it doesn't timeout?

Screenshot of the interface is attached.
brucegustPHP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I believe its possible that you are hiting a free space problem or a size limit.
Can you give more info on the table  like table size and number of rows ?
Oh sorry  250,000,000 rows .... definately a space problem.
Can you provide the size of the table and the space left on device ?
brucegustPHP DeveloperAuthor Commented:
Hey, Stampel!

Size of the table is 36538 and I'm not sure about the space left on the device, although I want to believe that it's pretty spacious. I'm not sure how to figure out how much space I have left on the device.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

36538 ? is that Megabytes ?
What is the Operating System ? Linux ? Windows ?
Do you have access to a shell or a console ?
brucegustPHP DeveloperAuthor Commented:
That is MB and the Operating System is Windows. As far as the shell is concerned, no. I'm a contractor and I might be able to access it, but I was hoping to be able to do what needed to be done with the tools that were immediately at my disposal.
I understand.
I believe you do not have the required 36 Gigabytes free that are required to perform this operation.
You may check with them if there is sufficient free space.
brucegustPHP DeveloperAuthor Commented:
I'll do it!

Let me ask you this: Let's say they come back and say you've got plenty of room. What are my options at that point? If this is a situation where it's simply timing out because of a setting that can be adjusted, what would that setting be and how do I access it?
Ok if asking this is difficult, i propose that we try to verify you do not have sufficient space to do this.
For exemple try to make a simple copy of this table.
Then we will try to findout how much temporary space you can use before it fails.
With this error, it is not a timeout problem :)
mysql> CREATE TEMPORARY TABLE foobar as (select * from liste);
Query OK, 1867635 rows affected (1 min 56.83 sec)
Records: 1867635  Duplicates: 0  Warnings: 0

Then play with "limit" to findout .. like this ....

mysql> CREATE TEMPORARY TABLE foobar2 as (select * from liste limit 1000);
Query OK, 1000 rows affected (0.42 sec)
Records: 1000  Duplicates: 0  Warnings: 0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
I'm going to make a slightly different suggestion.  Break the table up into several tables.  You may want to consult a DBA or Information Architect about the process, but the general strategy would go something like this.

1. Determine what data you will receive in the requests from your clients
2. Determine what data you will provide in response to the requests
3. Configure your table structure so that you will need to query the minimum number of tables to get from request to response.
4. Create a table organization that will let you know in advance (before starting any of the queries) which tables will need to be queried, based on the request data.

I would also add that any table with 250,000,000 rows is a different kind of beast from what we usually see in PHP + MySQL applications; this is into the scale of Facebook.  They get acceptable performance for data sets of this size by having rooms full of servers.  You might want to take this issue to the computer science department of a nearby college or university and get a grad student or teaching assistant to put this problem to their classes.  You'll probably get some innovative ideas about how to organize the data.  The reason that I think you need professional help with this is because of the data size.  If you break the big table into smaller tables with 250,000 rows each, you've got 1,000 tables to deal with.  If you break the big table into smaller tables with 1,000,000 rows each, you still have 250 tables to deal with.

So far, it seems we have been a little like the blind men with the elephant - looking at parts of the problem without seeing the big picture.  I've got a sense that the big picture may now need some serious attention in the form of creative thinking about information architecture!
brucegustPHP DeveloperAuthor Commented:
Was able to increase size of tmp directory and, although it took a while, I was able to install all my indexes.

Rock on!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.