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

Which $sth statement is taking the longest? What do your execution plans look like? The bottleneck is going to be the database, but the problem is almost always in your code. (Sometimes, indices can help with SELECT statements, but your DELETE statements will drag if you have too many.)

One possibility is to drop all indices that aren't related to your SELECT statements, do your DELETEs, then reindex the tables when you're done. Oh - OPTIMIZE and ANALYZE before and after can help out, too.

My wife's blog

  • Comment on Re: Slow script: DBI, MySQL, or just too much data?

Replies are listed 'Best First'.
Re^2: Slow script: DBI, MySQL, or just too much data?
by menolly (Hermit) on Apr 15, 2005 at 17:57 UTC
    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.
[reply]
      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 ...)

      My wife's blog

[reply]
        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.
[reply]

In Section Seekers of Perl Wisdom