mercredi 5 septembre 2018

Is there a way in Laravel to add/remove indexes with a single query?

I have a bit of code in a Laravel migration (MySQL database) that looks like this:

Schema::table('users', function (Blueprint $table) {
    $table->index('column1');
    $table->index('column2');
    $table->index('column3');
});

The end result of running this migration is fine. The problem is that it's very slow, because when I watch MySQL's SHOW PROCESSLIST, Laravel is doing this:

ALTER TABLE `users` ADD INDEX `column1_index` (`column1`);
ALTER TABLE `users` ADD INDEX `column2_index` (`column2`);
ALTER TABLE `users` ADD INDEX `column3_index` (`column3`); 

I am interested in the speed benefits of having indexes added to a table using this style of query:

ALTER TABLE `users`
    ADD INDEX `column1_index` (`column1`),
    ADD INDEX `column2_index` (`column2`),
    ADD INDEX `column3_index` (`column3`);

If I manually run the latter, it is about 3x faster than running the former. For running these queries on production, this is extremely valuable.

Is there a way to have Laravel add/remove multiple indexes in a single query? If not, I may just need to run a "raw" query.

Thank you for your time!



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire