![]() The reason why InnoDB takes so long to DELETE on such a massive table as yours is because of the cluster index. If that's not enough states, you could use TINYINT with 256 possible values.Īdding this new column will probably take a long time, but once it's over, your updates should be much faster as long as you do it off the PRIMARY as you do with your deletes and don't index this new column. This solution can provide better performance once completed, but the process may take some time to implement.Ī new BIT column can be added and defaulted to TRUE for "active" and FALSE for "inactive". Basically, it's a logging and reference table that needs to drop parts of its content from time to time.Ĭould I improve my indexes on this table by limiting their length? Would switching to MyISAM help, which supports DISABLE KEYS during transactions? What else could I try to improve DELETE performance?Įdit: One such deletion would be in the order of about one million of rows. This is a table that may see frequent INSERTs and SELECTs in the future, but no UPDATEs. to-keep shifts towards to-keep, this is no longer a useful solution. SELECT those rows that I don't want to delete into a temporary table and just dropping the rest, but as the ratio of to-delete vs. I most likely have no control over server variables unless I make changes to them mandatory (which I would prefer not to), so I'm afraid suggestions that change those are of little value. I am developing an application that will run on pre-existing databases. I can imagine that the index rewriting overhead makes these queries very slow. ![]() Queries that delete large chunks from the table are simply like this: DELETE FROM problematic_table WHERE problematic_table.taxid = '57' Ī query like this just took almost an hour to finish. ) ENGINE=InnoDB AUTO_INCREMENT=7888676 DEFAULT CHARSET=latin1 `log_evalue` double NOT NULL DEFAULT '-999', `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, This is the table structure: CREATE TABLE `problematic_table` ( Each row is not that large (131 B on average), but from time to time I have to delete a chunk of them, and that is taking ages. The first_name column will be modified to a varchar(30) NOT NULL column (and will not change position in the contacts table definition, as there is no FIRST | AFTER specified).I have a fairly large InnoDB table which contains about 10 million rows (and counting, it is expected to become 20 times that size). The last_name field will be changed to a varchar(55) NULL column and will appear after the contact_type column in the table. This ALTER TABLE example will modify two columns to the contacts table - last_name and first_name. Let's look at an example that shows how to modify multiple columns in a MySQL table using the ALTER TABLE statement. It tells MySQL where in the table to position the column, if you wish to change its position. column_definition The modified datatype and definition of the column (NULL or NOT NULL, etc). ![]() column_name The name of the column to modify in the table. table_name The name of the table to modify. The syntax to modify multiple columns in a table in MySQL (using the ALTER TABLE statement) is: ALTER TABLE table_name The first_name column will be created as a varchar(35) NULL column and will appear after the last_name column in the table. The last_name field will be created as a varchar(40) NOT NULL column and will appear after the contact_id column in the table. This ALTER TABLE example will add two columns to the contacts table - last_name and first_name. Let's look at an example that shows how to add multiple columns in a MySQL table using the ALTER TABLE statement. If this parameter is not specified, the new column will be added to the end of the table. It tells MySQL where in the table to create the column. ![]() column_definition The datatype and definition of the column (NULL or NOT NULL, etc). new_column_name The name of the new column to add to the table. ![]() The syntax to add multiple columns in a table in MySQL (using the ALTER TABLE statement) is: ALTER TABLE table_name ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |