我需要优化搜索的性能与许多字符串喜欢Z1719121
.
我的表 struct :
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
LALAVEL查询代码:
$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
有什么方法可以优化这个搜索吗?
我没有经验与索引,所以我创建索引正确的方式?
该表当前有42.000 rows
个和20mb total size
个,托管在AWS db.t3.small
个RDS服务器(2个vCPU、2 GiB内存)上.