Laravel 5.5 optimize query

Question

I have question about reducing database connection in for every time retrieve data from DB. I assume I have 5 tables (Notifications, Orders, Users, Products, Transactions)

In dashboard, I have to show:

Unread notifications
Order statistics
Number of products
Transactions statistics

For the easiest ways (pseudo-code):

$notis = Notification::where('unread')->get();
$orderStat = Order::join('user')->where('count(order.id)', 'status')->groupby('status');
$product = Product::count();
$transactions = Transaction::join('user')->where('count(trans.id)', 'status')->groupby('status');

So I have to run 4 separate queries, as my mentor said that this solution would reduce the speech of server in case there are many many records in each tables or the dashboard needs more tables (not for joining) to query.
I already did:

Index on foreign key columns
Eager-loading for eloquent if it's available
(OR using ajax to load data after UI rendered)

I want to ask: Are there any else method to reduce processing time for the above case?.
And other question about connection pool, he said use it to increase speed. After researching a found Laravel already did connection pool, isn't it?
Edit:
User have many notification, orders, transaction.
I just want to ask which method to improve the performance that I not mention above.

what are you trying to achieve by selecting status = count(id) ? — fab2s
– fab2s, Commented Dec 30, 2017 at 14:33

marcus · Accepted Answer · 2017-12-31 18:52:31Z

12

+50

Firstly correct your pseudo code which should be like:

$notis = Notification::where('unread')->get();
$orderStat = Order::join('user')->select('count(order.id)', 'status')->groupBy('status');
$product = Product::count();
$transactions = Transaction::join('user')->select('count(trans.id)', 'status')->groupBy('status');

I have a few additional tips which you can take into consideration:

1) Do not use a query cache (The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.)

2) use InnoDB as Storage Engine for your tables and SQL Server buffer pool to improve a capacity - https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html

3) If it is possible do not get all records from query use LIMIT and OFFSET to narrow data (limit and skip method in Laravel or only paginate)

4) If you do not need user data do not use INNER JOIN with users table (if each transaction and order always has user)

5) test requests using ab http://httpd.apache.org/docs/2.2/programs/ab.html to set your server correctly

6) use php >= 7.0 with fpm and opcache it will increase the limit of requests

7) store results of your queries with Cache API https://laravel.com/docs/5.5/cache (update cache if data is changed)

8) Profile Your Queries using https://github.com/barryvdh/laravel-debugbar

answered Dec 31, 2017 at 18:52

marcus

6516 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Felix Over a year ago

Can procedure mysql help in any case?

marcus Over a year ago

I doubt, PROCEDURE let us only simplify query

Felix Over a year ago

i found this query for mysql OPTIMIZE TABLE is that good or just reorganizes physical data only, dev.mysql.com/doc/refman/5.7/en/optimize-table.html

marcus Over a year ago

This query has two functions: to defragment tables or to update the InnoDB full-text index. It is good for the health of your table and can be executed if your DB will need this e.g due to heavy load.

Zugor · Accepted Answer · 2017-12-30 11:32:35Z

1

At this situation, you better use Cache and doesn't need to hit the database for every time.

It can reduce the processing time and have better performance.

answered Dec 30, 2017 at 11:32

Zugor

8818 silver badges18 bronze badges

Comments

Rahul Hirve · Accepted Answer · 2017-12-26 04:25:11Z

0

You have to use Laravel Relationship go through below link for proper guidance

For many to many relationship:

https://laravel.com/docs/5.5/eloquent-relationships#many-to-many

According to your condition use appropriate one. Hope it will help you.

After setting relationship you can access table's data calling single object.

edited Dec 26, 2017 at 4:25

answered Dec 25, 2017 at 16:28

Rahul Hirve

1,16913 silver badges20 bronze badges

2 Comments

Felix Over a year ago

I want to ask if there are not relationship in these tables I want to retrieve data (many many records in each tables and I have many tables to query) or I have to use ajax to get data in my website

Rahul Hirve Over a year ago

As per my knowledge there is no such feature(As you looking for) in Laravel.

Collectives™ on Stack Overflow

Laravel 5.5 optimize query

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related