question

afik162534 avatar image
afik162534 asked

Can anyone tell me why this query takes 8 seconds to load?

I have a CRM app that i've build and this is my biggest query in there. this tables have about 50k-100k rows each and to join all of them and order by all the results takes about 8 seconds (8.0282 sec to be exact). does anyone have a better idea for me? I don't know what to do... > SELECT * FROM `leads` > INNER JOIN `customers` ON `customers`.`lead_id` = `leads`.`id` > INNER JOIN `quotes` ON `quotes`.`lead_id` = `leads`.`id` > INNER JOIN `lead_sources` ON `lead_sources`.`id` = `leads`.`source_id` > WHERE `leads`.`is_customer` =0 > AND `leads`.`archived` =0 > AND `customers`.`company_id` = 75005 > ORDER BY `customers`.`created_at` DESC
sqljoinsmysqlorder-byinner join
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This MySQL or MSSQL? For MySQL you need to use the EXPLAIN key word ( https://dev.mysql.com/doc/refman/5.1/en/execution-plan-information.html) Or MSSQL press CTRL+M in the query window in SSMS before you run the query
2 Likes 2 ·
Take a look at the actual execution plan and see where the biggest cost is. I'm going to guess its the ORDER BY, if you don't need the order by remove it, or do it in the presentation layer instead of in the T-SQL
0 Likes 0 ·
you think that ordering my results with php will work faster?
0 Likes 0 ·
Potentially yes, if its something that can be done in the presentation layer I would generally recommend doing it in the presentation layer. But it might not be the sort that's your problem, hence the need to check the execution plan to see where the biggest cost operators are.
0 Likes 0 ·
To be honest, i'm new to sql planning and joins and all this stuff, i have no idea how to check the execution plan. is there a way to do it with phpMyAdmin on my server? is it something i have to write code for? (sorry for the ignorance)
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
rinson avatar image
rinson answered
Add Index for customers.created_at field Regards Rinson DBA
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.