Level 122
Something else is wrong. That is NOT a large amount of data.
Are you checking what queries actually get run (in your sql log or with debug bar)
Mar 13, 2017
11
Level 21
Query Large Amount of Data in MySQL
I have 3 tables.
- delivery_orders (id, name, etc...) ~ Has 23,000 rows
- accounts (id, do_id, etc) ~ has 22,000 rows
- printed (id, do_id, etc) ~ has 20,000 rows
The first table delivery_order has all my client data, the second table accounts has payment records for clients from the first table. The third table printed_jobs has records for printed jobs after a customer has paid for their order (meaning their records exists in accounts table).
I want to perform a query to fetch ALL orders that have been paid BUT NOT printed yet.
Both of these approaches take a lot of time (more than 3mins).
1. $dos = DB::table('delivery_orders')
->select('delivery_orders.*')
->leftJoin('accounts', 'accounts.do_id', '=', 'delivery_orders.id')
->leftJoin('printed_jobs', 'printed_jobs.do_id', '=', 'delivery_orders.id')
->whereNull('printed_jobs.do_id')
->get();
2. $dos = DeliveryOrder::has('accounts')->doesntHave('printed_job')->get();
What is the best way to load these data very fast, like up to 5-10 seconds?
Level 21
Let me install debug bar real quick because am pretty sure there is not any other queries running.
Level 21
@Snapey I have installed Barryvdh's Laravel Debugbar. How do I get that interface on that screenshot in that package on my Chrome?
Level 122
If you install the service provider it should add the debugger automatically to your views.
It only works if you return a view though and not if you just return data
Level 21
@Snapey I have a returned a view and now it displays.
I have queried a few rows for a start. To fetch 21 records it took 18 seconds. Something is totally not OK with it.
Here is the screenshot
Level 4
hello richard, my suggestion is to try the "slow query" directly into mysql, in order to check if the problem is the query itself.
Usually, when I deal with this kind of problems I use the command "explain", that is a command that show you what actually mysql does performing your query.
You can take a look here.
https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization-with-exists.html
Level 13
i suggest laravel relationship(elaquent) instead of DB(mysql)
Level 47
A lot depends also on your environment, homestead, wamp etc. newer MySQL uses a lot more resources then previous services. So with that in mind 18 seconds is still a lot.
Eloquent (active record) is designed to reduce the database requirements and instead transfer a lot to the web language (php in this case) by splitting up larger complex queries into smaller simpler queries. To that end it's very good and faster (generally).
So as with any query you need to look at your environment first. Optimize that then play around with the query. Can you manipulate a larger object from the db with php to output the desired results?
Level 2
Could be related to your development environment. I had this issue with the Homestead VM before enabling NFS for shared folders (see here: https://laracasts.com/discuss/channels/requests/nfs-on-homestead-20 )
18 seconds would still be a lot for such a small query, but in my case it reduced everything by at least 80%. A request like this on small tables should be under 1sec. 5-10 sec is not very fast at all, it should not be your goal ^^ If you get that low consider you still have something wrong happening.
Level 122
A join should be the way to go. What does the actual query look like if you use your first query?
Level 75
Is there any way to rework a table to have less joins? Read elena-kolevska reply about sometimes it's more efficient to actually duplicate data in a table and this is one smart woman https://laravel.io/forum/05-12-2015-has-many-through-relationship-depth
Oh, and all of those index fields are indexed, correct?
And change that null to tinyint so you have a zero or one making zero by default.
Change this
->select('delivery_orders.*')
To only get the fields needed, unless you really need all of the fields.
Also have you tried this query direct in MySQL only to see how long it takes.
Please or to participate in this conversation.