Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re^2: Slow script: DBI, MySQL, or just too much data?

by menolly (Hermit)
on Apr 15, 2005 at 17:57 UTC ( #448279=note: print w/replies, xml ) Need Help??


in reply to Re: Slow script: DBI, MySQL, or just too much data?
in thread Slow script: DBI, MySQL, or just too much data?

OPTIMIZE before is the problem -- one reason we want to shrink the database is that we're having problems with routine OPTIMIZEs failing to complete in a reasonable time.
  • Comment on Re^2: Slow script: DBI, MySQL, or just too much data?

Replies are listed 'Best First'.
Re^3: Slow script: DBI, MySQL, or just too much data?
by dragonchild (Archbishop) on Apr 15, 2005 at 18:02 UTC
    How big are these tables?? I've never had an optimize take more than 10 minutes, even on 5 million rows of 20+ columns each with several multi-column indices. Also, are they InnoDB or MyISAM? That's going to make a difference (especially in the time for DELETEs if you have a lot of FK's ...)
      testDBToMCQDB, which is where we've had the most optimize problems, has 9 columns, a 2-colum primary key, and ~8.5 million rows. It's a MyISAM table, and has four indexes -- the primary key, 2 single-column indices, and one with the same cols as the key, but in opposite order -- these were all created to speed up select statements which were causing problems.
      I expect this script to delete something like 40% of the data from each table.
        drop the indices, delete the columns, optimize the table, create the indices. You'll find a roughly 70% speedup.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://448279]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (4)
As of 2020-10-31 05:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favourite web site is:












    Results (286 votes). Check out past polls.

    Notices?