There will always be new requests, but many times you can store data "vertically" in rows instead of "horizontally" in columns. You
would need a "label" column to indicate what type of data the row contains. It's more work on the front end, but a lot more
flexible. It's a technique I use often, but may not be applicable/possible in your situation.
You can't create indexes to avoid or reduce full table scans?
InnoDB is slower in certain respects, but you need to keep in mind that InnoDB will allow multiple things to happen at once. Massive
deletes can be a problem in InnoDB, taking a loooong time.
Depending on how your data is structured, you may want to look into merge tables (MyISAM). I've got a "table" with almost 250
million records in it, which is a rolling 6 months worth of data. Once a month we need to "delete" a months worth of data (millions
of records). We make sure that each of the underlying tables of the merge table are about 1 months worth of data. To "delete" we
drop the table and alter the UNION on the merge table. Takes less than a second.
Delay key write won't help much if you are not using indexes.
----- Original Message -----
From: "Stefan Hornburg" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, July 11, 2006 9:51 AM
Subject: Re: Speedup of database changes
Brent Baisley wrote:
Changes to the database structure should be few a far between. If you are finding you need to change the structure regularly, you
should probably look at a redesign.
This customer regularly request new things, so I cannot avoid changes
to the database structure.
MyISAM locks the table when an insert/delete is occurring, during which time other requests are queue up. You can switch it to
InnoDB, which does not use table locking. This can be done easy enough with an Alter table command, but make sure you read up on
what InnoDB doesn't support (full text searching). InnoDB does take up a lot more space than MyISAM, so make sure your file
system supports files over 2GB.
I found in the MySQL documentation that the row-level locking of InnoDB
is slower if you need to do frequent full table scans. Unfortunately, I can not
avoid them (3rd party application running there).
If you need to stick with MyISAM, you may want to change the way you do inserts/deletes, breaking them up into chunks. Then
you'll be "interleaving" you insert/deletes with other requests.
OK, thanks.
How about DELAY_KEY_WRITE=1 ? Does this speed up things substantially ?
Bye
Racke
--
LinuXia Systems => http://www.linuxia.de/
Expert Interchange Consulting and System Administration
ICDEVGROUP => http://www.icdevgroup.org/
Interchange Development Team
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
