Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2012
    Posts
    4

    Unanswered: mysql copy table issues with query retrieval

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when you dropped the old table, you destroyed its indexes, and you didn't re-create them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2012
    Posts
    4
    Quote Originally Posted by r937 View Post
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm gonna wait longer than 9 minutes before replying this time, as i'm sure you will get the answer from another site
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2012
    Posts
    4
    Quote Originally Posted by r937 View Post
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Rudy that was 34 mins!!! You still breathing?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Feb 2012
    Posts
    4
    Quote Originally Posted by r937 View Post
    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
    Thank you!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •