gretschduojet's avatar

Limit with pagination on 5.6 million record table - slow search

Hello,

I'm having an issue with a table that has roughly 5.6 million records. I updated mysql to use full text search on InnoDB. Is it possible to limit your query results and use pagination? Some searches are returning approximately 60k results and it's taking about 10 seconds. Also, I'm fairly new to full text searching and I'm not sure how to search on an exact email. Right now it seems to match each side of the @ sign, but doesn't look for an exact match.

I resolved the problem by removing the

$query->whereRaw("MATCH(email) AGAINST (?)", array($userEmail));

and going back to

$query->where('email', 'like', "%{$userEmail}%");

I'm not quite sure why this fixed it though. Any insight on the difference between the two would be appreciated.

0 likes
2 replies
JarekTkaczyk's avatar

@gretschduojet are you using fulltext search on single column and exact match? Do you store multiple emails there?

Or is it only a bit inaccurate example?

gretschduojet's avatar

Thank you for your reply. On my email column I am using fulltext. Each row has one email address.

This is what it currently looks like

$map = [
            'product_number' => 'product_number',
            'user_id' => 'user_id',
            'email' => 'email',
            'ip_address' => 'ip_address',
            'result_code' => 'result_code',
        ];

        foreach($map as $field => $input)
        {

            if ($value = trim(array_get($params, $input)))
             {
                 // assuming target and invoker are relations
                 if ($userEmail = trim(array_get($params, 'email')))
                 {

                     $query->where('email', 'like', "%{$userEmail}%");

                 }

                 else{

                     $query->orWhere($field, 'like', "%{$value}%");
                 }
             }
        }

It's fast on large queries, but if I search for something with limited results it takes about 6 seconds. I did EXPLAIN on the query and it said that my type is ALL. Please let me know if that helped clear things up.

Please or to participate in this conversation.