Hi Jigal, Mathias,
Thanks the time you took to reply to my issue's!
I would like to clear out some things.
> It's a probably a case of not having the cardinality of indexes right and
thus making wrong decisions for queries.
- Currently there is not a single query in the application that does not use
the correct index. We only have key-reads. Wich would mean that MySQL is
creating these incorrect indexes?
> Depending on the size of the resulting record sets, your system must have
enough memory to handle it. Otherwise a lot of temporary tables will end up
on disk (slow) and also indexes cannot be loaded in memory (slow).
- The system runs with a key-buffer of 382 M, wich is most of the time not
filled 100 %. Created temp. tables is very low.
> Deleting a lot of records will have impact on the indexes, so it's quite a
job. The inserts/updates/deletes will also block the table for reading in
case of MyISAM.
- During deletion of records from the table there is no user interaction.
The only person manipulating the table/database is me. That's the reason why
i'm finding this 'strange'.
Changing to Innodb would be a great risk I think. Maybe we should think this
over again, but the way the system is configured right now should in my
opion be sufficient enough.
Mathias, what do you mean by:
> If you want to do a massive delete with a cron, it's better to :
> * select the rows to delete (using indexes)
> * delete indexes
> * delete rows (already marked)
> * recreate indexes
I don't really understand how you 'mark' the records for deletion before
deleting indexes. However I'm very interested.
Regards,
Almar van Pel
