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 ...) My wife's blog	[reply]
Re^4: Slow script: DBI, MySQL, or just too much data? by menolly (Hermit) on Apr 15, 2005 at 18:09 UTC
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]
Re^5: Slow script: DBI, MySQL, or just too much data? by dragonchild (Archbishop) on Apr 15, 2005 at 18:11 UTC
drop the indices, delete the columns, optimize the table, create the indices. You'll find a roughly 70% speedup. My wife's blog	[reply]


laziness, impatience, and hubris
	PerlMonks