Level 67
You'd probably have to do that manually using DB::raw() right after schema::create() (not in it)
1 like
May 9, 2018
9
Level 1
Can you specify a descending index in a migration?
I'm looking for a way to add a descending index in my up()method for a new migration. The Laravel documentation doesn't seem to mention this, and the index() method creates an ascending index.
Is it possible to explicitly create a descending index?
Here's a code example:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateStuffTable extends Migration {
public function up() {
Schema::create('stuff', function(Blueprint $table) {
// Adds table fields
$table->increments('id');
$table->string('contents')->default('');
$table->integer('priority')->unsigned()->default(0);
// Adds an ASCENDING index
$table->index('priority');
});
}
public function down() {
Schema::dropIfExists('stuff');
}
}
Level 88
Just curious but why do you need a descending index! I can't think of any problem that would need something like this!
Anyway! You can't do it with Laravel, since it's only supported by the InnoDB engine in MySQL and therefore it's not doable in the framework. Each engine has different features and supporting them all is just crazy.
However you can run a raw query and set the index yourself right? So intead of increments you simply create an integer and apply the index using a raw query.
Let me know if that worked!
Level 67
@bobbybouwmann descending indexes can speed things up, since most of the time you want results ordered newest to oldest so instead of having to search from the beginning index all the way to the end to get the most recent ones, the most recent ones are at the top of the index stack providing faster access to the indexes. It doesn't fit all circumstances.
Level 1
Thanks everyone!
To answer your questions, I have a table with over 1 million records which need to be queried in order of priority and date (which are fields). The values are changing periodically but the queries almost always need to be sorted by priority high-to-low, then date new-to-old. A compound descending index will improve performance by removing the slight penalty in MySQL for sorting through an index in reverse order.
For reference, here's a code example of how I dealt with this issue:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateStuffTable extends Migration {
public function up() {
Schema::create('stuff', function(Blueprint $table) {
// Adds table fields
$table->increments('id');
$table->string('contents')->default('');
$table->integer('priority')->unsigned()->default(0);
});
// Adds a descending index
DB::statement('ALTER TABLE `stuff` ADD INDEX `stuff_priority_index` (`priority` DESC)');
}
public function down() {
Schema::dropIfExists('stuff');
}
}
1 like
Level 75
If you have a table with a million records how is a migration even applicable? Would this not be an existing table.
Level 1
@jlrdw The table is currently only in the development environment. The migrations will create it in staging and production.
Level 35
I didn't even know there was an option to set the order of the index. I did some research and this is implemented since MySQL v8. In MySQL versions < v8 it was silently ignored. So adding an index for MySQL 5.7 does not harm your mysql setup but it won't have any effect on your queries.
v8.0 Documentation
MySQL now supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others. See Section 8.3.13, “Descending Indexes”.
Source: https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
v5.7 Documentation
An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.
Source: https://dev.mysql.com/doc/refman/5.7/en/create-index.html
Level 2
This seems to work: $table->index(['name', DB::raw('created_at desc')]);
3 likes
Level 5
Related PR which was closed for now by Taylor:
1 like
Please or to participate in this conversation.