时间戳

首页 > 解决方案 > Laravel quick integer lookup?

php - Laravel quick integer lookup?

问题描述

We have a table with millions of rows. Recently we introduced an integer to act as a secondary key, which is just a unique integer column new_identifier = 1100327 and not yet defined as a secondary primary key.

This lookup query takes about 6 seconds in tinker. $this->id_lookup is also an integer. and up to 50 seconds on horizon!

DB::table('my_big_table')->where('new_identifier', $this->id_lookup)->get();

How can we achieve the same performance as DB::table('my_big_table')->find($this->id_lookup); which is almost instant?

Can we introduce a second primary key with something like $table->key(['id', 'new_identifier']);?

How would this effect other lookups like MyBigTable::find($id); that is performed elsewhere?

Update - changing column from int to index reduced lookup time from 50s to <0.01s

标签： phpmysqllaraveleloquent

解决方案

您可以创建索引，在迁移中执行以下操作。

Schema::table('my_big_table', function (Blueprint $table) {
    $table->index('new_identifier');
});

索引可以帮助您，但主键索引很特殊，它们是聚集的。这意味着它包含以下键的所有数据。虽然二级索引是非聚集的，但只会包含索引列和主键列。它仍然会提高性能，但在这个领域你需要大量研究不同索引对查询的影响，而且完全不是非黑即白。

推荐阅读