I have an application which will be inserting and then deleting many
thousands of rows per hour within a single table. It essentially queues
and then handles requests from a series of processes, deleting the
requests after they've been dealt with.

Our MySQL 5.0.45 server is set up to use InnoDB tables by default, in a
single tablespace. Would MyISAM tables be a better fit for this type of
application? The database server is used for other applications so the
impact of this application on the others is a concern we have.

Also, in terms of speed or server load, would it be better to mark records
deleted and then periodically (say once an hour) run a delete query, or
would this approach not make a difference?

Jim, MyISAM tables tend to work best in situations where the proportions of
reads to writes is either very high or very low.

That is to say, either the data doesn't change much but it's being accessed
a lot, or the data changes a lot but it's rarely accessed.

MyISAM is quite a bit faster than InnoDB in some cases, so it could be that
if the size of this table will remain small, it would be the faster choice.

InnoDB will allow concurrent access, though, so depending on the level of
concurrency you expect, things may move faster using it.

Bottom line, no concrete answer for you - I'd test it each way if I were
you. Also keep in mind you can switch back and forth without too much
trouble, though of course if your table gets large it could take some time
to switch.

As for impact on your other applications - my knowledge of single tablespace
InnoDB performance is limited; I've been using individual tablespaces for
InnoDB tables for some time now.

-Dan


On 10/24/07, Jim <jj@zolx.com> wrote:
>
> I have an application which will be inserting and then deleting many
> thousands of rows per hour within a single table. It essentially queues
> and then handles requests from a series of processes, deleting the
> requests after they've been dealt with.
>
> Our MySQL 5.0.45 server is set up to use InnoDB tables by default, in a
> single tablespace. Would MyISAM tables be a better fit for this type of
> application? The database server is used for other applications so the
> impact of this application on the others is a concern we have.
>
> Also, in terms of speed or server load, would it be better to mark records
> deleted and then periodically (say once an hour) run a delete query, or
> would this approach not make a difference?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>