Hi gurus,

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
ON some_other_table.ID=old_table.ID;

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.

i'm gonna wait longer than 9 minutes before replying this time, as i'm sure you will get the answer from another site

I didn't mean to offend, I'm appreciative of any help I can get.

This is the site I was referring to that provided me with the fix I was looking for:

SQL COPY MySQL TABLE with structure and records

It however doesn't mention anywhere the consequences of not re-creating indexes, or why the delay at runtime. Thanks much

when you declared the auto_increment, you also specified PRIMARY KEY

this creates an index by default

in your query, you join the (new) old table to some_other_table using the id

this allows for an indexed search, rather than a table scan

without the index, every row from some_other_table requires a table scan of the (new) old table to see if there's a matching id

Rudy that was 34 mins!!! You still breathing?

Thank you!!