I am attempting to create a new index on an existing table with 44,485 records in it.
I am using this query to create my new index
CREATE INDEX index_name ON table_name (field_name); Whenever I run this I get this timeout error:
1205 - Lock wait timeout exceeded; try restarting transaction This is only on my local dev machine so there arent any other processes taking up the DB.
Is there a preferred method of adding new indexes to existing tables?
Thanks
You can increase the lock timeout with an InnoDB:
Edit /etc/my.cnf or /etc/mysql/my.cnf (assuming your dev machine is Linux/Unix)
Uncomment (or add) the line:
innodb_lock_wait_timeout = 50 And change the setting to a larger number, say 300. It's in seconds.
Restart mysql and rerun your query.
Alternatively, you can dump the data, drop the table, recreate it with the index and bring the data back in. Or you could create a new table with the index and select the old table's data into the new table. In both these cases you can break the data import/copy into smaller pieces (read: transactions) which won't exceed the timeout.
