Unanswered: mysql copy table issues with query retrieval
I recently made a mistake with my database and as a result had to restore from backup. However, I only needed to restore 1 table, so I created a new db and imported the backup, then went:
DROP TABLE liveDB.old_table;
CREATE TABLE liveDB.old_table SELECT * FROM tempDB.old_table;
Here is my problem. If I run a query such as: SELECT * FROM liveDB.some_other_table
LEFT JOIN liveDB.old_table
Before the restore of old_table it ran very quickly and smooth. Now with the restore it runs terribly slow. It often takes atleast half a minute to pull that 1 query. Can anyone point me in the right direction as to what to do to fix this? Thanks.
when you dropped the old table, you destroyed its indexes, and you didn't re-create them
I appreciate the response, I saw that solution on another site right after I made the post..
Fixed with: CREATE TABLE liveDB.old_table (ID INT(3) auto_increment primary key) SELECT * FROM tempDB.old_table;
Curious though, why does destroying the indexes cause the runtime to sky rocket? What is causing the delay? If I didn't include the auto_increment portion, the values still held from old to new so the join should still be the same, no? Thanks in advance.