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

jquery - How do you format the date used in the popover widget title of FullCalendar? -

Image
in fullcalendar, how format date used in popover widget title when clicking '+n more'? code: <div class="fc-popover fc-more-popover"> <div class="fc-header fc-widget-header"> <span class="fc-close fc-icon fc-icon-x"></span> <span class="fc-title">tuesday, april 7</span> ... </div> ... </div> you use daypopoverformat that. determines date format of title of popover created eventlimitclick option. must date formatting string. default value "dddd, mmmm d" english , "ll" other languages.
Read more

Bubble Sort Manually a Linked List in Java -

Image
this first question here. trying manually sort linked list of integers in java , can not figure out wrong code. suggestions? don't error, still have output unordered. tried few different ways, nothing worked. appreciate if can me that. public class node { int data; node nextnode; public node(int data) { this.data = data; this.nextnode = null; } public int getdata() { return this.data; } } // node class public class datalinkedlist implements datainterface { private node head; private int size; public datalinkedlist(){ this.head = null; this.size = 0; } public void add(int data) { node node = new node(data); if (head == null) { head = node; } else { node currentnode = head; while(currentnode.nextnode != null) { currentnode = currentnode.nextnode; } currentnode.nextnode = node; ...
Read more

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