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
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
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
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.
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)
If you really want some help here please read this article and post the suggestions you find in there.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Add Index for customers.created_at field Regards Rinson DBA
20 People are following this question.
