Managing Indexes

Defining Indexes

To improve the query performance, you will probably need to define indexes. Maghead provides a bunch of query helpers to help you create cross-platform queries, to define your indexes, please open your schema file, and then add few lines in the schema method:

function schema() {
    ....
    $this->index('idx_book_title', ['title']);
}

You can also create compound index:

$this->index('idx_general_order_query', ['org_id', 'store_id']);

If you're using Postgresql, then you can use some special features like concurrently:

$this->index('idx_salary', ['last_name', 'salary'])->concurrently();

You can also create the index query object first, and then define the properties later:

$this->index('idx_paid_orders')->on('orders', ['status', 'paid_amount']);

It's also possible to define a spatial index or fulltext index:

$this->index('idx_location', 'location')->spatial();

$this->index('idx_content', 'content')->fulltext();

Note that spatial index is a MySQL-only feature.

Defining Unique Index

And of course you can define an unique index:

$this->index('idx_order_uuid', ['order_uuid'])->unique();

Defining HASH/BTREE Index

The third parameter of the index method is the index method, to define a btree index:

$this->index('idx_general_order_query', ['org_id', 'store_id'], 'btree');

To define a hash index (you can also use the using method call to alter the index method):

$this->index('idx_account', ['account'])->using('hash');

The index method returns a CreateIndexQuery object. For more details, please see https://github.com/c9s/SQLBuilder/blob/master/src/Universal/Query/CreateIndexQuery.php

Listing Indexes

To list the indexes created in the database, maghead provides a useful command for you to list the indexes: (It's currently MySQL only feature), by default, it lists master node indexes:

php bin/maghead index

To list the indexes in the other database:

php bin/maghead index node3

To list the indexes of some tables:

php bin/maghead index -t orders -t order_items