152 reputation
7
bio website dba.stackexchange.com/users/…
location Portugal
age 33
visits member for 2 years, 6 months
seen Jan 17 '13 at 14:20

Just developing...


Oct
15
awarded  Popular Question
Sep
2
comment Optimizing table and choosing storage engine
@RickJames yes. They are in that order.
Aug
31
accepted Optimizing table and choosing storage engine
Aug
31
comment Optimizing table and choosing storage engine
I've updated the question with two common queries. I've completed copying all the data a couple of days ago. I have now two tables. I'm still inserting/updating on both, in case I decide to change back. But the live data are using the InnoDB table. Inserting, updating and selecting are now faster. I won't mess with the DATE+TIME for now. I will let it run for some time and see how it works out.
Aug
31
revised Optimizing table and choosing storage engine
added 1298 characters in body
Aug
27
awarded  Tumbleweed
Aug
20
awarded  Autobiographer
Aug
20
asked Optimizing table and choosing storage engine
Aug
19
revised Fastest way to copy data from MyISAM to InnoDB
added 574 characters in body
Aug
19
comment Fastest way to copy data from MyISAM to InnoDB
I ended up splitting into small files. But I used your tweaks, which helped. So, I'll accept this answer :)
Aug
19
accepted Fastest way to copy data from MyISAM to InnoDB
Aug
19
comment Fastest way to copy data from MyISAM to InnoDB
By the way, I think the main problem here is the unique keys. I managed to go around the timeout error and added the keys after loading 500,000 rows (only 3% of the total). It took 20 minutes to finish.
Aug
19
comment Fastest way to copy data from MyISAM to InnoDB
For now I just want to have a 2nd table with a different schema and test if it runs better/faster than the old one. But I need the data to test it. If it proves to be better, I will have to do the same on at least 6 other databases. Some tables are bigger, others smaller. On this particular table, whether I convert to InnoDB or not, I will have to add a new column, which will be a hell of ALTER table. I'll try the tweaks you suggested.
Aug
19
revised Fastest way to copy data from MyISAM to InnoDB
Let's split the into several files?
Aug
19
asked Fastest way to copy data from MyISAM to InnoDB
Jul
28
awarded  Supporter
Jul
14
comment mysql taking too long to send data
Nope. Same result.
Jul
8
awarded  Editor
Jul
8
revised mysql taking too long to send data
added 534 characters in body
Jul
8
comment mysql taking too long to send data
1591 rows in set (16.48 sec) I ran the query again, this is why the duration is different. It took now 16 seconds (!!)