What is the most optimised way to search a string across all columns in a MySQL table using Laravel Eloquent?

Question

We have an orders table with more than 100,000 records in it. The scenario is to search a string from all tables' columns and find the matching results. We have this query in place but it takes more than 30 seconds to complete.

$searchkeyword = "Umair";

$orders = Orders::orderBy("order_number","DESC")

    ->where(
        [
            ['order_id','=',$searchkeyword],
        ]
    )
    ->orWhere(
        [
            ['shipping->first_name','LIKE','%'.$searchkeyword.'%'],
        ]
    )
    ->orWhere(
        [
            ['shipping->last_name','LIKE','%'.$searchkeyword.'%'],
        ]
    )
    ->orWhere(
        [
            ['billing->email','=',$searchkeyword],
        ]
    )
    ->orWhere(
        [
            ['billing->phone','=',$searchkeyword],
        ]
    )
    ->orWhere(
        [
            ['shipping->country','=',$searchkeyword],
        ]
    )
    ->orWhere(
        [
            ['woo_date_created','LIKE','%'.$searchkeyword.'%']
        ]
    )
    ->orWhere(
        [
            ['shipment_details->tracking_code','LIKE','%'.$searchkeyword.'%']
        ]
    )
    ->orWhere([
        ['shipment_details','LIKE','%'.$searchkeyword.'%']
    ])
    ->paginate(200)
    ->toJson();

Can you please tell me if there is a better and faster way in eloquent to perform such searches?

Among the best ways to optimise your search **CREATE INDEXES** Especially if you have more than 100k rows in your table. — Hicham AIT TALGHALIT, Feb 18 '22 at 15:29
@HichamAITTALGHALIT That wouldn't work when you perform queries with `like` in them. The only source that kinda implements this is https://stackoverflow.com/a/59523434/2595985 — Techno, Feb 18 '22 at 15:32
[laravel/scout](https://laravel.com/docs/9.x/scout) may help you. Here is the part from [Laracon Online Winter '22](https://www.youtube.com/watch?v=0Rq-yHAwYjQ&t=20013s) where Taylor does explain full text index with Scout. — medilies, Feb 20 '22 at 07:39
`OR` will ignore indexes. It simply scans all 100K rows, checking each of the OR clauses. — Rick James, Feb 21 '22 at 00:10

score 1 · Accepted Answer · answered Feb 18 '22 at 15:36

There is no way to use different Laravel functions to make the query you show optimized. The OR operations and the LIKE '%word%' predicates force the query to do a table-scan.

You could create a FULLTEXT index to help. Read about it here: https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

Laravel doesn't appear to have any specific helper function for running fulltext queries. You just have to use whereRaw('MATCH(...columns...) AGAINST (...pattern...)'). See https://laracasts.com/discuss/channels/eloquent/is-this-match-against-using-relevance-possible

What is the most optimised way to search a string across all columns in a MySQL table using Laravel Eloquent?

1 Answers1