Best Way to Handle 1M+ Records in Laravel & Livewire?

Hello Laravel experts,

I'm currently working on a Laravel + Livewire project that needs to display and manage over 9 million transaction records.

🔍 My Current Setup:

Laravel 10 with Livewire
MySQL (InnoDB)
Transactions Table (~9 million rows) with indexing on id, created_at , etc

⚡ Performance Issues:

Query execution is slow, even with pagination (->paginate(100))
Livewire updates are slow when interacting with filters/search
Server memory usage spikes when fetching data

🛠 Questions & Possible Solutions:

1️⃣ Would switching to Yajra DataTables (AJAX-based) significantly improve performance?
2️⃣ Is Livewire still a good choice for handling large datasets, or should I consider Vue/Alpine + AJAX instead?
3️⃣ Are there specific MySQL optimizations (e.g., indexing, partitioning) that can help?
4️⃣ How can I efficiently apply search and filters without causing performance issues?

I’d love to hear from those who have successfully managed millions of records in Laravel. Any insights, best practices, or alternative approaches would be greatly appreciated! 🙏

Thanks in advance! 😊

jlrdw

1 year ago

Level 75

Query only what's needed, have good indexes, perhaps try cursor pafination.

Also in a query only query the fields needed.

And do server side pagination.

Side note:

I write my own tables. If you go through the learning curve and learn well you will never need anything like "datatables".

JussiMannisto

1 year ago

Level 50

If the database queries are slow, then all UI considerations (Yajra, Vue vs. Livewire, etc.) are irrelevant.

You absolutely have to use pagination, otherwise you'll run out of memory — that's true for tables of any size. 9 million rows isn't so big that you'd have to worry about partitioning. Optimize the queries instead. The most important thing is to ensure that every query condition utilizes an index in order to avoid full table scans.

1. Determine which queries are slow, and why

If you have Telescope or Debugbar installed on your dev environment, you can see all database queries there. Or you can just add ->dd() to the end of your query builder, and that'll print out the compiled SQL.

Once you have the SQL, you can analyze it by running EXPLAIN, preferably in the production database. That might already tell you what's going wrong. Often it has to do with indexes not being utilized.

If you want help, you should post the query code here: both the PHP code and the resulting SQL. The table structure / migration files would also be very helpful. Without these, there's not much to go on. The PHP code is important as there may be an N+1 issue or something else that you've overlooked.

2. Check your MySQL configuration

The most important variable in MySQL is innodb_buffer_pool_size. If you have a dedicated DB server, you can set it to around 70% of available server RAM. So if your server has 64 GB of memory, it can be ~45 GB. If the DB is on the same server as the rest of the app, then less.

Also check that key_buffer_size is not using up memory needlessly since it's not used by InnoDB. It still has to be a non-zero value, such as 10M.

On the OS side, you should minimize swapping memory to disk, because that's a performance killer.

3. Full text indexes / search engines

Full text indexes can help if you need to search text. But it's pretty slow compared to dedicated search engines which don't have to worry about transactional integrity. There are many options: Elasticsearch, Solr, Sphinx, etc. But first figure out why the queries are slow.

Please or to participate in this conversation.