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

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?

CodePudding user response：

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

CodePudding user response：

laravel/scout may help you. Here is the part from Laracon Online Winter '22 where Taylor does explain full text index with Scout.

(I would have commented this but I do not have enough rep :'))