mysql - Stored Procedure Increases load time -


the below stored procedure using now. doubt slows down site load larger data. can 1 suggest me ways fix issue...

begin          declare limitstart int(10);         declare limitend int(10);           set @login_user_id=(select login_user_id);          set @friend_fb_id=(select friend_fb_id);            drop temporary table  if exists temp_tab;         create temporary table temp_tab(row_id integer not null auto_increment primary key,         fb_id    bigint(20),user_fb_id    bigint(20),name varchar(500),gender varchar(50)         ,likes int(10),movies int(10),interests int(10),books int(10),total_count int(10))  charset=utf8;           if @friend_fb_id=0 or @friend_fb_id=''             begin                  set @gender=(select gender users user_fb_id=@login_user_id);                  set @insert_sql=concat('insert temp_tab(name,user_fb_id,gender)                 select name,user_fb_id,gender users relationship_id not in(2,3,4) , user_fb_id<>',@login_user_id,' , gender<>',''',@gender,''','                 , user_fb_id not in(select friend_fb_id friends is_ignored>0)                 , user_fb_id not in(select friend_fb_id friends user_fb_id=',@login_user_id,' , is_app_friend=1)');                  prepare stmt1 @insert_sql;                 execute stmt1;                   set @update_sql=concat('update temp_tab inner join data on temp_tab.user_fb_id=data.user_fb_id set temp_tab.likes=(select count(data.type_id) data data.user_fb_id not in(',@login_user_id,') ,                 data.fb_id in(select data.fb_id data data.user_fb_id=',@login_user_id,' , data.type_id=1)                 , temp_tab.user_fb_id=data.user_fb_id group user_fb_id),                 temp_tab.movies=(select count(data.type_id) data data.user_fb_id not in(',@login_user_id,') ,                 data.fb_id in(select data.user_fb_id data data.user_fb_id=',@login_user_id,' , data.type_id=2)                 , temp_tab.user_fb_id=data.user_fb_id group user_fb_id),                 temp_tab.interests=(select count(data.type_id) data data.user_fb_id not in(',@login_user_id,') ,                 data.fb_id in(select data.fb_id data data.user_fb_id=',@login_user_id,' , data.type_id=3)                 , temp_tab.user_fb_id=data.user_fb_id group user_fb_id),                 temp_tab.books=(select count(data.type_id) data data.user_fb_id not in(',@login_user_id,') ,                 data.fb_id in(select data.fb_id data data.user_fb_id=',@login_user_id,' , data.type_id=4)                 , temp_tab.user_fb_id=data.user_fb_id group user_fb_id)');                  prepare stmt1 @update_sql;                 execute stmt1;             end;         else             begin                  set @gender=(select gender users user_fb_id=@friend_fb_id);                  set @insert_sql=concat('insert temp_tab(name,user_fb_id,gender)                 select name,user_fb_id,gender users relationship_id not in(2,3,4) , user_fb_id<>',@friend_fb_id,' , gender<>',''',@gender,''',' , user_fb_id<>',@login_user_id,'                 , user_fb_id not in(select friend_fb_id friends is_ignored>0)                 , user_fb_id not in(select friend_fb_id friends user_fb_id=',@friend_fb_id,' , is_app_friend=1)                 , user_fb_id not in(select friend_fb_id friends user_fb_id=',@login_user_id,' , is_app_friend=1)');                  prepare stmt1 @insert_sql;                 execute stmt1;                   set @update_sql=concat('update temp_tab inner join data on temp_tab.user_fb_id=data.user_fb_id set temp_tab.likes=(select count(data.type_id) data data.user_fb_id not in(',@friend_fb_id,') ,                 data.fb_id in(select data.fb_id data data.user_fb_id=',@friend_fb_id,' , data.type_id=1)                 , temp_tab.user_fb_id=data.user_fb_id group user_fb_id),                 temp_tab.movies=(select count(data.type_id) data data.user_fb_id not in(',@friend_fb_id,') ,                 data.fb_id in(select data.user_fb_id data data.user_fb_id=',@friend_fb_id,' , data.type_id=2)                 , temp_tab.user_fb_id=data.user_fb_id group user_fb_id),                 temp_tab.interests=(select count(data.type_id) data data.user_fb_id not in(',@friend_fb_id,') ,                 data.fb_id in(select data.fb_id data data.user_fb_id=',@friend_fb_id,' , data.type_id=3)                 , temp_tab.user_fb_id=data.user_fb_id group user_fb_id),                 temp_tab.books=(select count(data.type_id) data data.user_fb_id not in(',@friend_fb_id,') ,                 data.fb_id in(select data.fb_id data data.user_fb_id=',@friend_fb_id,' , data.type_id=4)                 , temp_tab.user_fb_id=data.user_fb_id group user_fb_id)');                  prepare stmt1 @update_sql;                 execute stmt1;              end;         end if;           drop temporary table  if exists match_tab;         create temporary table match_tab(s_id integer not null auto_increment primary key,         fb_id    bigint(20),user_fb_id    bigint(20),name varchar(500),gender varchar(50)         ,likes int(10),movies int(10),interests int(10),books int(10),total_count int(10),is_in_app tinyint(4) default 0)   charset=utf8;           insert match_tab(user_fb_id,name,gender,likes,movies,interests,books,total_count)         select user_fb_id,name,gender,ifnull(likes,0),ifnull(movies,0),ifnull(interests,0),ifnull(books,0),         sum(ifnull(books,0)+ifnull(movies,0)+ifnull(interests,0)+ifnull(likes,0))as total_count temp_tab         group user_fb_id;          update match_tab  set is_in_app=1 user_fb_id in         (select user_fb_id users access_token not null);           set limitstart =  ((page*page_size)-page_size) ;         set limitend =  page_size ;         set @sno = ((page - 1)*page_size);           select  @a:=@a+1 sno,user_fb_id,name,gender,likes,movies,interests,books,total_count,is_in_app         match_tab ,(select @a:= @sno) total_count<>0 order total_count desc         limit limitstart,limitend;          select count(s_id)as total_count match_tab total_count<>0;      end


Comments

Post a Comment

Popular posts from this blog

asp.net mvc - SSO between MVCForum and Umbraco7 -

i need integrate sso between mvcforum (forum of company) , umbraco7 (manages cms company). however struggling shared authentication work. tried several ways suggested on other threads still not running expected. here did: 1. in mvcforum (forum.mywebsite.com) 1.1. machine key in web.xml: <machinekey validationkey="the same key umbraco7" decryptionkey="the same key umbraco7" validation="sha1" decryption="aes" /> 1.2. authenticate forms in web.xml <forms name="my-sso-auth" protection="all" path="/" timeout="43200" domain="forum.mywebsite.com" loginurl="/members/logon" enablecrossappredirects="true" /> 2. in umbraco7 (news.mywebsite.com) 2.1. machine key in web.xml: <machinekey validationkey="the same key mvcforum" decryptionkey="the same key mvcforum" validation="sha1" decryption="aes" /> 2.2. a
Read more

Python Tkinter keyboard using bind -

i have problem using bind option can click button in keyboard, , call function once key pressed. tried taking other codes have similar purpose , saw i'm doing pretty same, though still have problem must've missed. thankful if me this. here's code: # -*- coding: utf-8 -*- #imports tkinter import * pil import imagetk, image import time import tkmessagebox #===========================================================================================================================================# #tkinter class class app(frame): def __init__(self, master=none): frame.__init__(self, master) self.pack() #===========================================================================================================================================# #game pieces classes class board: def __init__(self, rows = 7, columns = 6, picture_height = none, picture_width = none): self.rows = rows self.columns = columns self.picture
Read more

ubuntu - Selenium Node Not Connecting to Hub, Not Opening Port -

i'm trying set demo selenium grid. have digital ocean ubuntu 14.02 droplet. (i have 2 , tried set using 2 different machines got nowhere.) i'm trying run hub , node both locally. here input both hub, node, , nmap results. https://gist.github.com/mekhami/d5bbecca5ff0de9d2dc9 so, node starting tcp connection reset , port it's supposed operate on closed. i have had no luck in number of different options , configurations work. but... of course, worked randomly once. don't know why, don't know did, know 1 brief moment, node connected , saw on grid console. tried run test , went down again , i've never seen come up. please let me know more information can provide. update: randomly came online again. don't know happened, might related timeout or time_wait or going on. this because vm/server not have enough entropy available. to resolve problem, install haveged (or other similar things). the instructions of how install haveged on ubu
Read more