Question 200110 in Laravel Question posted in
The official Laravel Documentation can be found here.

laravel/mysql – how to perform search query with 3k list values

I’m receiving from an external API a list with 3.000 values (car part codes).

Now I’m trying to get from my database all entries that contains one of these codes.

$q = Product::query();
$q->where('codigo', '');
foreach($keys as $v){
  $q->orWhere('codfabrica', $v);                 // varchar 191
  $q->orWhere('gtin', $v);                       // varchar 191
  $q->orWhere('codoriginal', $v);                // varchar 191
  $q->orWhere('codreferencia', $v);              // varchar 191
  $q->orWhere('aplicacao', 'LIKE', '%'.$v.'%');  // medium text
}
$found = $q->get();

This is taking 10 minutes but it works.

How can I speed up or optimize this query?

Tags:

2

Answers


  1. 0

    Try chunking the list into smaller subsets and perform multiple queries.

    $chunkSize = 100;
$keysChunks = array_chunk($keys, $chunkSize);

$found = collect();

foreach ($keysChunks as $chunk) {
    $q = Product::query();
    $q->where('codigo', '');

    $q->whereIn('codfabrica', $chunk)
        ->orWhereIn('gtin', $chunk)
        ->orWhereIn('codoriginal', $chunk)
        ->orWhereIn('codreferencia', $chunk)
        ->orWhere(function ($query) use ($chunk) {
            foreach ($chunk as $v) {
                $query->orWhere('aplicacao', 'LIKE', '%' . $v . '%');
            }
        });

    $found = $found->concat($q->get());
}

$found = $found->unique();
    Login or Signup to reply.
  2. 0

    To speed up this I guess you can make an array of the values you are getting from API and check with the whereIn query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search