My site is loading so slow because of my query

Question

I needed to add on my website some roles from database - table role_users, columns user_id and role_id. I'm using it with this code :

$career_solutions_data = DB::select(" 
SELECT 
career_solutions.id,
career_solutions.user_id,  
career_solutions.subject, 
career_solutions.date, 
career_solutions.public, 
career_solutions.views, 
career_solutions.optional, 
career_solutions.on_offer, 
users.username, 
users.profile_picture, 
categories.category, 
categories.category_url, 
categories.color, 
career_solutions_categories.category as sub_category,
career_solutions_format.category as event_format,
career_solutions_certification.category as certification

FROM career_solutions 

INNER JOIN categories 
ON categories.id = career_solutions.topic_category_id 

INNER JOIN career_solutions_format
ON career_solutions_format.id = career_solutions.topic_format_id

INNER JOIN career_solutions_certification
ON career_solutions_certification.id = career_solutions.topic_certification_id

INNER JOIN career_solutions_categories 
ON career_solutions_categories.id = career_solutions.topic_subcategory_id 

INNER JOIN users 
ON users.id = career_solutions.user_id 


INNER JOIN privacy_settings 
ON privacy_settings.user_id = users.id 

WHERE users.deleted_at IS NULL 
AND ( 
(privacy_settings.career_solutions = 0 AND public = 1 ) 
OR (users.id IN ( 

SELECT contacts.contact_id 
FROM contacts 
WHERE contacts.user_id = $id 
) 
) 
) 

OR users.id = $id 

ORDER BY date desc limit 5000 
");

And my view :

@if($carer_solution_data['role'][0]['pivot']['role_id'] == 1 )
                                            <i style="font-size: 11px" class="icon-user"></i> 
                                            @else <i style="font-size: 11px" class="icon-hotel-restaurant-172 u-line-icon-pro fa- fa-lg"></i>

                                            @endif

Here is the line with problems:

$temp_soluation['role'] = \App\User::select('id')->where('id', '=', $career_solution->user_id)->first()->role;

Without it, my website is loading fast and correctly. My site is loading so so slow right now.I have over 9000+ posts on my Career Solutions and I'm thinking that is the problem. This variable $carer_solution_data['role'] is loading all the posts, and that's the problem of my website. How can I avoid this problem?

Ensure you've indexed and paginate. Don't pull ~5000 rows and expect it to go smoothly. — Script47, Aug 28 '19 at 08:55
I already have paginate on my site, on first page I have like 6 career solutions...But I dont know why this is loading so slow. — Andrei Nagy, Aug 28 '19 at 08:56
Can you give me an example of an index please?I don't know what are you thinking about. — Andrei Nagy, Aug 28 '19 at 08:57
@AndreiNagy https://stackoverflow.com/questions/2955459/what-is-an-index-in-sql — Script47, Aug 28 '19 at 08:58
Is your view only outputting lines where `role_id == 1`? Then you should add this condition to your query to avoid pulling unneccessary rows — Nico Haase, Aug 28 '19 at 09:58
I'm very confused. You shared a query. Then some view code then the "line with the problem" which appears to have come out of nowhere. First of all. How are you using the first query, why would the line you shared cause this problem? — apokryfos, Aug 28 '19 at 10:05

score 2 · Accepted Answer · answered Aug 28 '19 at 09:54

Your problem is not necessarily your query, your not doing anything too complex, the problem is that your browser is being flooded.

There are a few things you can do to improve your query on the other hand. - Firstly you can Use the 'Explain' statement to give you more information on how your query is generated and how too improve it - Secondly, you can Use 'Indexes' to improve the relationships and the efficiency of your table

This URL gives more information about using the 'Explain' statement: http://dev.mysql.com/doc/refman/5.0/en/explain.html

As I said earlier your browser is being flooded because it is trying to load 9000 (rows) elements all together at the same time. Look at implementing features such as pagination to break down your data into smaller chunks.

Example of Pagination: (9000 Rows)

Chunk: 0-99 - Page 1
Chunk: 100-199 - Page 2
Chunk: 200-299 - Page 3
...

Your system should be able send a request to the server based on an event such as scrolling to the bottom of the page (infinite scrolling) of selecting the next page button, provide the next chunk.

You can also enable the profiler for laravel to see any bottlenecks in your code: https://packagist.org/packages/jkocik/laravel-profiler

My site is loading so slow because of my query

1 Answers1