Skip to main content

Black Friday 2025! Only until December 1st: coupon FRIDAY25 for 40% off Yearly/Lifetime membership!

Read more here

Seeding Big(ger) Data with Optimizations

Premium
13 min read

After we introduced pagination in the last lesson, we shouldn't have performance problems with loading too much data. But what about Eloquent queries in the search? Time to test their loading time.

For that, first, we need to simulate a significant amount of data, in factories/seeders.

Simulating Data with Seeders/Factories

I will go for this scenario to seed:

  • 100 countries
  • 1000 cities
  • 1000 geoobjects
  • 1000 owners and 1000 regular users
  • 100 000 properties
  • 200 000 apartments
  • 200 000 bookings with ratings and 200 000 bookings without ratings

To implement that testing scenario, I decided to create a totally separate Seeder class, that could be launched on a separate testing DB, for example...

The Full Lesson is Only for Premium Members

Want to access all of our courses? (29 h 14 min)

You also get:

54 courses
Premium tutorials
Access to repositories
Private Discord
Get Premium for $129/year or $29/month

Already a member? Login here

Comments & Discussion

AA
Ali Al Qahtani ✓ Link copied!

Excuse me, You did copy and past ApartmentSeeder codes block in BookingSeeder

PK
Povilas Korop ✓ Link copied!

Great catch, need to be more careful next time! Fixed by pasting from the repository.

AV
Andrea Verrecchia ✓ Link copied!

I am getting this error while loading the count of facilities. How can I fix this?

SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders (Connection: mysql, SQL: select facilities., (select count() from properties inner join facility_property on properties.id = facility_property.property_id where

PK
Povilas Korop ✓ Link copied!

Hmm, interesting, I thought about it and wondered why this error didn't happen for me: the reason is too many property IDs loaded as parameters and MySQL didn't like it. How many properties do you have in the DB?

Maybe I should rewrite that query somehow to not contain so many IDs.

AV
Andrea Verrecchia ✓ Link copied!

The records I inserted into the database are the same as those in your example. Most likely, the problem stems from the fact that I accidentally called "search" without including the filter for country, capacity_children, and capacity_adults. So: booking.test/api/search. However, the error message could still occur if, for example, I apply the filter for country=90 and have numerous properties in that country.

AV
Andrea Verrecchia ✓ Link copied!

I replaced your query with this one, it should be fine. Maybe when you have time, try testing it with 2-3 facilities per business and check the results it produces to see if they are consistent and if it can still be optimized.

Screenshot: https://postimg.cc/Yv58dsb6

PK
Povilas Korop ✓ Link copied!

Wow interesting solution, definitely will check it out in upcoming days (I have 0 free time at the moment) and will get back to you.

PK
Povilas Korop ✓ Link copied!

Ok I've made some research and apparently the limit of parameters/placeholders is 65 536

I think with the filter of country/city there should be really smaller amount of properties than this limit. So I don't think this optimization is for a real-case scenario. But good catch, anyway!

NS
Ngozi Stephen Onyemauche ✓ Link copied!

Hello, i ran php artisan db:seed --class=Database\Seeders\Performance\PropertySeeder and i got this error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'role_id' in 'where clause' (Connection: mysql, SQL: select id from users where role_id = 2)
public function run(int $count = 100): void { $users = User::where('role_id', Role::ROLE_OWNER)->pluck('id'); Role::all()->pluck('name'); $cities = City::pluck('id');

    for ($i = 1; $i <= $count; $i++) {
        Property::factory()->create([
            'owner_id' => $users->random(),
            'city_id' => $cities->random(),
        ]);
    }
}
	I actually using spatie role and permission package 
	i changed the $users to $users = User::role('ROLE_OWNER')->pluck('id');  i got the error message
	There is no role named `ROLE_OWNER`.
	pls how do i go about it
PK
Povilas Korop ✓ Link copied!

Sorry it's hard for us to debug every individual situation especially if you're not following the exact lessons but decide to use Spatie package, then you need to make changes in the factories/seeders too, so they would work according to that package.

Also, yes there's no role ROLE_OWNER, it is a constant inside of Role model, please compare to the repository in the course.

N
nazzalra ✓ Link copied!

Looks like you're missing the Role Class. the $users variable should be $users = User::role(Role::ROLE_OWNER)->pluck('id')