At 11:00 -0600 1/15/04, Eamon Daly wrote:
I have a table that's used for logging. We do a ton of
SELECTs and INSERTs on this table, but no UPDATEs. In order
to keep the SELECT speed high, we archive most items older
than 30 days by moving them out from this table to another.
This, of course, creates a ton of holes which we optimize
away nightly, but the optimization process takes an awfully
long time (~200s).
Is there a way to eliminate this optimization by specifying
that inserts should /always/ go at the end of the table,
regardless of any holes? Is this nightly archive the Right
Way To Do It, or is there an alternative I'm unaware of?
Would another table type outperform MyISAM under this
scenario?
If you never delete anything from a MyISAM table after creating
it, inserts will always happen at the end. In that situation,
selects from the table can happen concurrently with inserts that
are inserting new records on the end.
In your situation, you may have actually made the situation worse
by attempting to move records out of the table for efficiency's
sake. :-)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
