我需要优化具有大量字符串(例如Z1719121
.
我的表结构:
codfabrica => varchar 191 => nullable
codoriginal => varchar 191 => nullable
aplicacao => mediumText => nullable
表索引:
codfabrica => non_unique 1 | seq_in_index 1 | collation A | cardinality 42466
codoriginal => non_unique 1 | seq_in_index 1 | collation A | cardinality 7449
aplicacao => non_unique 1 | seq_in_index 1 | collation NULL | cardinality 42550
laravel 查询代码:
$allKeys = [...]; // array with lots of strings
$q = MyModel::query();
$q->whereIn('codfabrica', $allKeys);
$q->orWhereIn('codoriginal', $allKeys);
$q->orWhereRaw('MATCH (aplicacao) AGAINST (?)', array($allKeys));
结果:
array with 1000 strings => took 5 seconds
array with 2500 strings => took 35 seconds
有什么方法可以优化这个搜索吗?
我对 INDEX 没有经验,那么我是否以正确的方式创建了索引?
该表当前有42.000 rows
和20mb total size
,托管在AWS db.t3.small
RDS 服务器(2 个 vCPU、2 GiB 内存)上。