CODEWITHSUNDEEP
phpsqllaravel
sunergos
sunergos

Reputation: 128

Better performance searching SQL table with 170,000 rows

I have SQL table with 170,000 rows. Each row has column for string long approximately 600 characters.

I want to list all the rows, that contains searched keyword.

Using LIKE '% keyword %' takes about 1000ms. My app is build in Laravel using Eloquent.

Do you have any ideas what way would be the best for performane? I need to have options for searching case sensitive/insesitive, accent sensitive/insesitive, exact phrase or just multiple words in random order. So when I tried TNTSearch, the performance was excellent, but with not so much options.

Also, I tried to create index and Match Against function in my query, but there are also some limitations.

Upvotes: 2

Views: 932

Answers (3)

Mathew Tinsley
Mathew Tinsley

Reputation: 6976

Like queries that begin with a wildcard cannot take advantage of indexes. Performance will continue to degrade as your table size grows.

I would recommend one of the following options for improving performance:

  1. You can use Laravel Scout

Laravel Scout provides a simple, driver based solution for adding full-text search to your Eloquent models.

Out of the box Scout supports Algolia, but there are other drivers available as well, including TNTSearch

https://github.com/teamtnt/laravel-scout-tntsearch-driver

  1. You can use a fulltext index to improve search performance.

Eloquent does not support fulltext search out of the box, but there are a few third party packages that add support.

Ex:

Upvotes: 2

miknik
miknik

Reputation: 5941

Define FULLTEXT indexes on the columns which you want to be able to search to greatly increase search speed. Only works on MyISAM and InnoDB tables though.

Google it or have a look here

Upvotes: 2

vrijdenker
vrijdenker

Reputation: 1431

I'd recommend using SOLR in combination with Solarium: https://solarium.readthedocs.io/en/latest/

Upvotes: 0

Related Questions