Query with million records very slow

To optimize the query, you can try the following approaches:

1. Indexing: Make sure that the adp_bucket and u_id columns are indexed in the database. This will speed up the search process.

2. Chunking: Instead of retrieving all the records at once, you can use the chunk method to retrieve the records in smaller chunks. This will reduce memory usage and improve performance. Here's an example:

Pix::query()
    ->where('adp_bucket', '=', 1)
    ->where('u_id', '!=', 0)
    ->chunk(1000, function ($pixes) {
        foreach ($pixes as $pix) {
            $advert = Advert::find($pix->ad_id);
            // do something with the pix...
        }
    });

In this example, the chunk method retrieves 1000 records at a time and processes them in the callback function.

1. Eager loading: If you need to access the Advert model for each Pix record, you can use eager loading to reduce the number of queries. Modify your query like this:

Pix::with('advert')
    ->where('adp_bucket', '=', 1)
    ->where('u_id', '!=', 0)
    ->chunk(1000, function ($pixes) {
        foreach ($pixes as $pix) {
            $advert = $pix->advert;
            // do something with the pix and advert...
        }
    });

Make sure you have defined the relationship between Pix and Advert models using the belongsTo method.

These optimizations should help improve the performance of your query.