Migrations: When to use index()?

Published 1 month ago by DoeJohn

Should I addindex() for every field? Or just use it on foreign keys? Or?

For example, here's one migration:


    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');

            $table->unsignedInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

            $table->unsignedInteger('category_id');
            $table->foreign('category_id')->references('id')->on('categories')->onDelete('cascade');
         
            $table->string('title');
            $table->string('slug')->nullable();
            $table->string('image')->nullable();
            $table->text('body');
            $table->text('excerpt')->nullable();

            $table->boolean('approved')->default(0);

            $table->timestamps();

            $table->unique(['category_id', 'slug']);
        });
    }

Which fields in this migration should be indexed? For example, I always add them on foreign keys:

$table->index('user_id');
$table->index('category_id');

Is this wrong (to always use index() on foreign keys)?

Would you index some other fields in this migration? If yes, which one?

Best Answer (As Selected By DoeJohn)
Swaz

I would highly recommend you check out Chris Fidao's Performant Laravel course. It's free and has 3 videos specifically on MySQL Indexing. Lot's of other great information too.

https://serversforhackers.com/laravel-perf

Sergiu17
Sergiu17
1 month ago (150,510 XP)

Basically, if the column ends in _id it should be indexed.

I'd add index to slug column, because you search through it all the time

DoeJohn

What about timestamps (created_at & updated_at)? Like this:

$table->index('created_at');
$table->index('updated_at');

?

DoeJohn

I'm just looking how WordPress indexed columns in the :_wp_posts table:

https://codex.wordpress.org/Database_Description#Table:_wp_posts

Indexes:

|Keyname|Type|Field|

|PRIMARY|PRIMARY|ID|

|post_name|INDEX|post_name|

|type_status_date|INDEX|post_type post_status post_date ID|

|post_parent|INDEX|post_parent|

|post_author|INDEX|post_author|

martinbean

Should I add index() for every field?

@DoeJohn No.

Indexes are to aid in speeding up searches and commonly-executed queries. Foreign keys are a type of index.

You should only really be looking at indexes if you have heavy database load and looking to speed up queries. Blindly adding indexes to your tables can have a negative impact on your query times if you don’t know what you’re doing.

DoeJohn

@martinbean

Foreign keys are a type of index.

Does that mean that it's wrong to always use index() on foreign keys (as @Sergiu17 wrote "Basically, if the column ends in _id it should be indexed."?

You should only really be looking at indexes if you have heavy database load and looking to speed up queries.

How will I know if there will be have heavy database load?

As for the WordPress - there are indexes on some fields such as post_author and post_parent (which are the only foreign keys in _wp_posts table) as well as post_date which is equivalent to Laravel's created_at...

And I'm referring to WordPress as an example because it will alwas have these indexes regardless of whether there is heavy database load.

martinbean

Does that mean that it's wrong to always use index() on foreign keys (as @Sergiu17 wrote "Basically, if the column ends in _id it should be indexed."?

@DoeJohn You don’t need to add an index to foreign keys because foreign keys are indexes.

How will I know if there will be have heavy database load?

Your website will slow down and you’ll users reporting errors like “connection timeout” or “too many connections”.

I’m also not sure what you’re hoping to achieve by looking at WordPress’s database schema. WordPress’s database schema is optimised for the WordPress application. Just because WordPress has an index on its post_date column doesn’t mean you should be automatically adding indexes to every created_at column in your Laravel applications; it just means the WordPress developers have deemed that column worthy of being indexed based on the queries WordPress executes.

DoeJohn

@martinbean

Your website will slow down and you’ll users reporting errors like “connection timeout” or “too many connections”.

But shouldn't we prevent (in advance) something like that, if possible?

Snapey
Snapey
1 month ago (929,875 XP)

The more indexes you add, the slower you make adds and changes since the index has to be regenerated. So its a tradeoff.

Only you can know the types of accesses you will make to the database and if your application is read or write heavy

Your default position would be to not add them, but look out for any where queries such as looking for your slug field.

However, any field that is marked as unique will already be indexed

click
click
1 month ago (59,590 XP)

Also add an index on columns that you use to sort. If you sort a table based on the created_at date you probably want an index on that field.

Swaz
Swaz
1 month ago (57,325 XP)

I would highly recommend you check out Chris Fidao's Performant Laravel course. It's free and has 3 videos specifically on MySQL Indexing. Lot's of other great information too.

https://serversforhackers.com/laravel-perf

DoeJohn

@click

If you sort a table based on the created_at date you probably want an index on that field

Yeah, I do that almost everywhere (I'm using Laravel's latest and oldest methods which, by default, order by created_at column).

DoeJohn

@Swaz thanks for the link.

Cronix
Cronix
1 month ago (571,710 XP)

That serversforhackers series is really good and I recommend it often to people. I really like how he shows the queries without the indexing and shows step by step how it improves things as he adds the indexes, and tries different combinations. It demo's the benefit very well by actually showing it.

db Indexes, like everything else in coding, have good and bad points. It takes time and experience to know how/when/why to use them in different circumstances. I generally index columns that are 1) id's (including foreign keys) 2) things that are searched against (used in WHERE) 3) Things that are used in ORDER BY and GROUP BY

Please sign in or create an account to participate in this conversation.