`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '', ![]() It is the newly created table from csv file using load data infile. The only thing that matters is quick update and without locking. I could modify the csv format in a preprocess step.It does not matter if the steps take a longer duration as long as the database is available for other tasks. I am looking to get it done in 1 hour without locking the whole table, as later I have to update the sphinx index which is dependent on this table. It can take time but not more than 24 hours. It is not necessary to do the update in one transaction.There are 12 indexes on the table, and 8 indexes include the update fields. There are indexes on the fields being updated. ![]() About 6% of the rows in the table will be updated by the file, but sometimes it can be as much as 25%.Same update with join query UPDATE table1 a JOIN table2 bĬlarifications from questions in comments: Query OK, 434914 rows affected (22 hours 14 min 47.55 sec) In MyISAM it took 38.93 sec while in InnoDB it took 7 min 5.21 sec. Lock wait timeout exceeded try restarting transaction Update 1ĭata loading in new table using LOAD DATA LOCAL INFILE. What's the best way to achieve something like this ?Ībove operations takes around 30-40 minutes to complete and while doing this, there are other updates going on which gives me I need to take only 3 values per line out of 10-12 from the file and update it in the database. ![]() I tried inserting data to a MyISAM table row by row and it took 35 minutes. I get an updated dump file from a remote server every 24 hours. I have one big table which contains around 10 millions + records. Server has 32GB RAM and is running Cent OS 7 圆4. InnoDB buffer pool size is 15 GB and Innodb DB + indexes are around 10 GB. Using MySQL 5.6 with InnoDB storage engine for most of the tables.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |