Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

XerK's avatar
Level 1

Try to search in 200 Millions data

I'm trying to make an application export data from the table

I have a big table that has 200 million records and trying to find by ID 10K records and export these records in CSV file

Any solution to make it faster because it takes a lot of time while exporting data

# Controller

Validator::make($request->all(), [
            'text' => ['required', 'mimetypes:text/plain'],
        ])->validateWithBag('filter');

        $fileContent = file_get_contents($request->text);
        $clients = $this->endcode($fileContent);
        $this->file = "/exports/extract-data-" . Str::random(6) . '.csv';

        PrepareDataJob::dispatch($clients, $request->user(), Mail::to($request->user())->queue(new DataExported()))->afterResponse();


        return Inertia::render('Dashboard/Clients/Show', [
            'clients' => Export::where('user_id', auth()->user()->id)->latest()->paginate(10),
        ]);

# PrepareDataJob File

class PrepareDataJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    private $clients;
    private $file;
    private $result = [];
    private $user;

    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct($clients, $user)
    {
        $this->file = "/exports/extract-data-" . Str::random(6) . '.csv';

        $this->clients = $clients;

        $this->user = $user;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        DB::table('clients')->orderBy('id')->select('unique_id', 'mobile', 'nationality')->whereIn('unique_id', $this->clients)->chunk(1000, function ($clients) {
            foreach ($clients as $client) {
                $this->result[] = $client;
            }
        });
        
        if ($this->result > 0) {
            Excel::store(new ClientsExport($this->result), $this->file);

            ExportJob::dispatch($this->result, $this->file, $this->user->id);

            User::find($this->user->id)->decrement('point', ($this->result));

        }
    }
}

# Export File

class ExportJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected $path;
    protected $clients;
    protected $user_id;
    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct($clients, $path, $user_id)
    {
        $this->clients = $clients;
        $this->path = $path;
        $this->user_id = $user_id;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        Export::create([
            'user_id' => $this->user_id,
            'name' => 'Extract Data - '. Carbon::now()->toDateTimeString(),
            'file' => $this->path,
            'count' => count($this->clients),
        ]);
    }
}
0 likes
1 reply
sr57's avatar

BIg data is always challenging and you have to go step by step.

  1. chose to good db (postgresql probably better than mariadb)

  2. optimize your request and the quantity of memory you need (chunk method is ok to deal with the "out of memory pb" but the counter part is request duration). https://wiki.postgresql.org/wiki/Performance_Optimization#Critical_maintenance_for_performance and export to .cvs is probably better (easy to test)

  3. write your app having the focus to have little overhead versus the direct export fron the db

Please or to participate in this conversation.