A
A
Alexey Solomakha2014-05-28 13:22:24
css
Alexey Solomakha, 2014-05-28 13:22:24

How to change MySQL field types in production?

Hello.
I was faced with the task of optimizing the work of mysql, which was rather crookedly designed and now looks like a pretty monstrous monster (2.5GB), the first thing I decided to pay attention to is indexes.
Compiled a list of queries that I want to run at night (see below).
Please tell me what are the risks of losing data or possible pitfalls in this procedure, when used in production and uptime criticality?
What else is worth paying attention to (reading) when optimizing / speeding up mysql

#Удаляю дубликат внешнего ключа (было два одинаковых).
ALTER TABLE `clients_ph` DROP FOREIGN KEY `clients_ph_ibfk_3`;
#Изменяю поля, относительно выборок.
ALTER TABLE `statistic_not_active` MODIFY COLUMN `client_id` INT(11) NOT NULL;
ALTER TABLE `statistic_not_active` ADD INDEX `idx_statistic_not_active_client_id` ( `client_id` );
ALTER TABLE `statistic_not_active` MODIFY COLUMN `call_id` BIGINT(20) NOT NULL;
ALTER TABLE `statistic_not_active` ADD INDEX `idx_statistic_not_active_call_id` ( `call_id` );
ALTER TABLE `internet_tar_plan_clients_ph` ADD INDEX (`billing_tar_id`);
ALTER TABLE `list_tar_plan` ADD INDEX (`billing_tar_id`);
ALTER TABLE `internet_tar_plan_clients_ph` ADD INDEX (`billing_tar_id`, `last_tar_id`);
ALTER TABLE `internet_tar_plan_clients_ph` MODIFY COLUMN `billing_tar_id` int(11) NOT NULL AFTER `tar_id`, MODIFY COLUMN `last_tar_id`  int(11) NOT NULL AFTER `billing_tar_id`;
ALTER TABLE `internet_tar_plan_clients_ph` ADD FOREIGN KEY (`billing_tar_id`) REFERENCES `list_tar_plan` (`billing_tar_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `clients_ph` ADD INDEX `agrm_id` USING BTREE (`agrm_id`) ;

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Michael, 2015-12-17
@Makarych90

There is another option with the wget command .
But there, too, manual labor may be required.
You can read more here:
All the Wget Commands You Should Know

A
AxisPod, 2014-05-28
@KamaZz

Damn, 2.5GB is a monster, but what then is a 120GB database (we now have one, on one of the projects, there are much more)?
In general, first drag the database structure somewhere and run queries. Check functionality. Then make a temporary table with a string and write some text to this table between queries, in case of a fall, you will need to check the last table, repair and run the queries further.
It's like an option. Well, first check with samples to see if the current data will fit into the new types. Ideally, you would pull off the backup locally and run it all on it first.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question