0

I have a database with several tables in it. and my web dev. used a big mysql query for admin role home page to give statistics, I believe it is a wrong approach, but I need to learn how to fix it anyway if possible. Currently it takes like 30 seconds to load and the time increases day by day.

This is the query :

SELECT 
  applications.*,
  application_addr.*,
  companies.id,
  companies.manager_id,
  companies.companynameturkish,
  companies.balance,
  companies.companylimit,
  companies.balancetype,
  tbl_provider.provider_name 
FROM applications 
  LEFT JOIN application_addr ON(applications.serial=application_addr.App_id) 
  LEFT JOIN companies ON(applications.company_id=companies.id) 
  LEFT JOIN tbl_provider ON (applications.type=tbl_provider.id ) 
WHERE 1 
Order by applications.serial DESC

This is the Explain :

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra

1   SIMPLE  applications    NULL    ALL NULL    NULL    NULL    NULL    7654    100.00  Using temporary; Using filesort

1   SIMPLE  application_addr    NULL    ALL NULL    NULL    NULL    NULL    6914    100.00  Using where; Using join buffer (Block Nested Loop)

1   SIMPLE  companies   NULL    eq_ref  PRIMARY PRIMARY 4   mycompany.applications.company_id   1   100.00  Using where

1   SIMPLE  tbl_provider    NULL    eq_ref  PRIMARY PRIMARY 4   mycompany.applications.type 1   100.00  Using where

Tables structure :

Applications :

Column  Type    Comment
serial  int(11) Auto Increment   
policy_no   varchar(16) NULL     
company_id  varchar(200) NULL    
dataentry   varchar(255) NULL    
creator_id  int(11) NULL     
approved_by int(11) NULL     
type    varchar(100) NULL    
firstname   varchar(255) NULL    
fathername  varchar(255) NULL    
lastname    varchar(255) NULL    
birthdate   date NULL    
gender  tinyint(1) NULL  
country varchar(255) NULL    
passportno  varchar(200) NULL    
address varchar(255) NULL    
address_main    varchar(255) NULL    
address_sub varchar(255) NULL    
length  tinyint(1) NULL  
price   bigint(20) NULL  
paid_price  bigint(20) NULL  
netprice    bigint(20) NULL  
saved_price bigint(20) NULL  
confirmed   tinyint(1) NULL  
appstatus   tinyint(1) NULL  
startdate   date NULL    
enddate datetime NULL    
recordcreationdate  datetime NULL    
recordlastediteddate    varchar(200) NULL    
cancel_reason   text NULL    
image_name  varchar(255) NULL    
net_price_deduced   tinyint(1) [0]   
status_change_1_to_3    tinyint(1) [0]

Application_addr :

Column  Type    Comment
id  int(11) Auto Increment   
App_id  int(11)  
Province    varchar(200)     
Province_id varchar(200)     
Province_checkbox   enum('1','0') [0]    
District    varchar(200)     
District_id varchar(200)     
District_checkbox   enum('1','0') [0]    
DistrictVillages    varchar(200)     
DistrictVillages_id varchar(200)     
DistrictVillages_checkbox   enum('1','0') [0]    
MainRoad    varchar(200)     
MainRoad_id varchar(200)     
MainRoad_checkbox   enum('1','0') [0]    
Apartment   varchar(200)     
Apartment_id    varchar(200)     
Apartment_checkbox  enum('1','0') [0]    
IndependentSection  varchar(200) NULL    
IndependentSection_id   varchar(200)     
IndependentSection_checkbox enum('1','0') [0]    

Companies

Column  Type    Comment
id  int(11) Auto Increment   
manager_id  varchar(255) NULL    
username    varchar(255) NULL    
password    varchar(255) NULL    
companynamearabic   varchar(255)     
companynameturkish  varchar(255)     
address varchar(255)     
managername varchar(255)     
companyspeciality   varchar(255)     
percentage  int(3)   
companytelephone    varchar(255)     
companyemail    varchar(150)     
contactname varchar(255)     
contactphone    varchar(255)     
website varchar(150)     
status  tinyint(11)  
balancetype tinyint(11)  
companylimit    varchar(255)     
balance bigint(20)   
recordcreationdate  date     
recordlastediteddate    date NULL    
auto_approve    tinyint(4) NULL

tbl_provider :

Column  Type    Comment
id  int(11) Auto Increment   
provider_name   varchar(225) NULL    
no_of_slabs int(11) NULL     
age_from    int(11) NULL     
age_to  int(11) NULL     
provider_percentage double NULL  
provider_income double NULL  

closed as too broad by Philᵀᴹ, mustaccio, McNets, billinkc, Marco Mar 27 '17 at 11:55

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    You're not doing any aggregation - is all the data being pulled from the RDBMS and aggregated client-side? It'd be quicker to let the database do the work – Philᵀᴹ Mar 24 '17 at 16:15
  • ok Then how do we do that ?? – Fanar Web Mar 25 '17 at 6:27
  • What are the statistics you're trying to get? Normally, some count(something), sum(some_other_thing), min(yet_another_thing), max(yet_another_thing), avg(), stddev() should give you most needed statistics. You normally tend to want them GROUPed BY (several grouping criteria). MySQL has got all these aggregate functions already built-in. Standard ANSI SQL has even more... – joanolo Mar 25 '17 at 14:23
  • Please give us SHOW CREATE TABLE. – Rick James Mar 29 '17 at 17:02
  • Does application_addr have `INDEX(App_id)? Why load the entire table? – Rick James Mar 29 '17 at 17:03
0

If leave outside the reason for this query (I not understand which statistic it could give), still few common recommendations:

  1. Query return aprx 60 columns - did You really need all of them for statistics? Reduce number of column for strictly and absolutely necessary. Even if You will need drill down for some information - You always can do this by second request by ID (primary key).
  2. From Your structure not seen no one indexes other than Primary Key - each column from JOIN part must have index
  3. This query time grow and will be grow unless You not implement incremental strategy - any conditions in WHERE part would reduce total number of returned rows (not forget about indexes again). As variant - calculate and store daily or monthly statistics and store result, so new iteration could work with only fresh information.
  • adding indexes fixed the problem, from 35 seconds to 3.5 sec. however, I am looking for more. – Fanar Web Mar 29 '17 at 23:44
  • Hi Fanar, if looking for more - do other 2 recommendations, start from first, I sure for 100% You do not need all columns for any kind of statistics. Also think about incremental load data from live system to report system - this is most proper way. – a_vlad Mar 30 '17 at 0:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.