0

I have writed this mysql function, it's worked perfekt but very slowly, what can I do for better performance, have you any idea? note:i need all created virtual columns

DROP FUNCTION IF EXISTS STATISTICS;
CREATE FUNCTION STATISTICS(uid INT)
RETURNS TEXT
BEGIN

  DECLARE area_number INT DEFAULT 1;
  DECLARE total_areas INT DEFAULT 0;
  DECLARE completed_areas INT DEFAULT 0;
  DECLARE price_ft DECIMAL(5,3) DEFAULT 0.00;
  DECLARE price_fm DECIMAL(5,3) DEFAULT 0.00;
  DECLARE report_json TEXT DEFAULT '{}';


  SELECT COUNT(id) FROM associations INTO total_areas;

  SET price_ft = (SELECT price FROM price WHERE name='tel');
  SET price_fm = (SELECT price FROM price WHERE name='mail');

  SELECT JSON_INSERT(report_json, '$.currently_work_area', (SELECT association_id FROM dfb_league_clubs WHERE ticket IS NOT NULL ORDER BY id DESC LIMIT 1)) INTO report_json;
  SELECT JSON_INSERT(report_json, '$.currently_work_area_name', (SELECT gebiet FROM dfb_leagues WHERE id=(SELECT association_id FROM dfb_league_clubs WHERE ticket IS NOT NULL ORDER BY id DESC LIMIT 1))) INTO report_json;

  SELECT JSON_INSERT(report_json, '$.my_paid', (SELECT SUM(paid) FROM user_paid WHERE for_user=uid)) INTO report_json;

  WHILE area_number <= total_areas DO

  SET @current_area_name = (SELECT name FROM associations WHERE id=area_number );
  SET @total_league_in_area = (SELECT COUNT(id) FROM dfb_leagues WHERE association_id=area_number);
  SET @completed_league_in_area = (SELECT COUNT(id) FROM dfb_leagues WHERE association_id=area_number AND (completed=1 OR error=1));

  SET @area_start_date    = (SELECT begin_update FROM dfb_league_clubs WHERE association_id=area_number ORDER BY id ASC LIMIT 1);
  SET @area_end_date      = (SELECT end_update FROM dfb_league_clubs WHERE association_id=area_number AND completed=1 ORDER BY id DESC LIMIT 1);
  SET @area_missing_page  = (SELECT COUNT(id) FROM dfb_league_clubs WHERE association_id=area_number AND (to_facebook=1 OR rating_no_page=1) AND completed=1);
  SET @area_start_date_f  = '';
  SET @area_end_date_f    = '';
  SET @area_total_day     = '';
  SET @area_total_work_time = '';
  IF( @area_start_date IS NOT NULL AND @area_end_date IS NOT NULL ) THEN
    SET @area_start_date_f  = (SELECT DATE_FORMAT(@area_start_date, '%d.%m.%Y'));
    SET @area_end_date_f    = (SELECT DATE_FORMAT(@area_end_date, '%d.%m.%Y'));
    SET @area_total_day     = (SELECT DATEDIFF( @area_end_date, @area_start_date ));
    SET @area_total_work_time = (SELECT DATEDIFF( @area_end_date, @area_start_date ));
  END IF ;

  SET @total_club_in_area     = (SELECT COUNT(id) FROM dfb_league_clubs WHERE association_id=area_number);
  SET @completed_club_in_area = (SELECT COUNT(id) FROM dfb_league_clubs WHERE completed=1 AND association_id=area_number);
  SET @completed_club_in_area_with_email = (SELECT COUNT(id) FROM dfb_league_clubs WHERE association_id=area_number AND completed=1 AND email IS NOT NULL);

  SET @added_contact_in_area  = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number);



  SET @added_contact_with_tel  = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND mobil IS NOT NULL);
  SET @added_contact_with_mail = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND email IS NOT NULL);
  SET @added_contact_with_combine = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND email IS NOT NULL AND mobil IS NOT NULL);


  SET @my_added_contact           = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid);
  SET @my_completed_clubs         = (SELECT COUNT(id) FROM dfb_league_clubs WHERE association_id=area_number AND user_id=uid AND completed=1);

  SET @my_added_clubs_with_mail   = (SELECT COUNT(id) FROM dfb_league_clubs WHERE association_id=area_number AND user_id=uid AND completed=1 AND email IS NOT NULL);
  SET @my_added_contact_with_tel  = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid AND mobil IS NOT NULL);
  SET @my_added_contact_with_mail = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid AND email IS NOT NULL);
  SET @my_added_contact_with_combine = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid AND email IS NOT NULL AND mobil IS NOT NULL);

  SET @json_string = CONCAT(
      '{
      "name":"', @current_area_name,'",
      "total_league":"', @total_league_in_area,'",
      "completed_league":"', @completed_league_in_area,'",
      "total_club":"', @total_club_in_area,'",
      "completed_club":"', @completed_club_in_area,'",
      "completed_club_in_area_with_email":"', @completed_club_in_area_with_email,'",
      "area_missing_page":"', @area_missing_page,'",
      "area_start_date":"', @area_start_date_f,'",
      "area_end_date":"', @area_end_date_f,'",
      "area_work_day":"', @area_total_day,'",
      "total_contact":"', @added_contact_in_area,'",
      "added_contact_with_tel":"', @added_contact_with_tel,'",
      "added_contact_with_mail":"', @added_contact_with_mail,'",
      "added_contact_with_combine":"', @added_contact_with_combine,'",
      "my_completed_clubs":"', @my_completed_clubs,'",
      "my_added_clubs_with_mail":"', @my_added_clubs_with_mail,'",
      "my_added_contact":"', @my_added_contact,'",
      "my_added_contact_with_tel":"', @my_added_contact_with_tel,'",
      "my_added_contact_with_mail":"', @my_added_contact_with_mail,'",
      "my_added_contact_with_combine":"', @my_added_contact_with_combine,'"
      }'
    );


  SELECT JSON_MERGE(report_json, @json_string) INTO report_json;

  SET area_number = area_number + 1;


  END WHILE;

  RETURN report_json;

END;
  • 1
    26 separate counting selects to different tables... What do you want? – Akina Apr 30 at 9:54
  • I know, but I need all results for output , what you see, all results into json string, which way or strategy can I follow for better performance, you can me help please? – creativedirektor Apr 30 at 10:00
  • Build one query per each table. Count all counts which you need using SELECT SUM(condition) FROM table instead of SELECT COUNT(id) FROM table WHERE (condition). – Akina Apr 30 at 10:06
  • 1
    JSON really is the worst – Philᵀᴹ Apr 30 at 10:08
1

I observed that you are getting multiple counts from a single table. You must consider making a single query for all counts if its from a single table.

example:

SET @added_contact_in_area  = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number);
  SET @added_contact_with_tel  = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND mobil IS NOT NULL);
  SET @added_contact_with_mail = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND email IS NOT NULL);
  SET @added_contact_with_combine = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND email IS NOT NULL AND mobil IS NOT NULL);
  SET @my_added_contact           = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid);
  SET @my_added_contact_with_tel  = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid AND mobil IS NOT NULL);
  SET @my_added_contact_with_mail = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid AND email IS NOT NULL);
  SET @my_added_contact_with_combine = (SELECT COUNT(id) FROM club_contacts WHERE association_id=area_number AND user_id=uid AND email IS NOT NULL AND mobil IS NOT NULL);

to this kind of query.


SELECT    @added_contact_in_area          := COUNT(id), 
            @added_contact_with_tel         := COUNT(case when mobil IS NOT NULL then 1 else 0 end),  
            @added_contact_with_mail        := COUNT(case when email IS NOT NULL then 1 else 0 end), 
            @my_added_contact               := COUNT(case when user_id=uid then 1 else 0 end), 
            @my_added_contact_with_tel      := COUNT(case when user_id=uid AND mobil IS NOT NULL then 1 else 0 end), 
            @my_added_contact_with_mail     := COUNT(case when user_id=uid AND email IS NOT NULL then 1 else 0 end), 
            @my_added_contact_with_combine  := COUNT(case when user_id=uid AND email IS NOT NULL AND mobil IS NOT NULL then 1 else 0 end), 
            @added_contact_with_combine     := COUNT(case when email IS NOT NULL AND mobil IS NOT NULL then 1 else 0 end)
            FROM club_contacts WHERE association_id=area_number;

Let me know if that helps.

reference: link1, link2

0

Start by throwing out all JSON action.

Then think about how to use SQL's power for doing everything at once. In particular, you should be able to eliminate the WHILE loop.

Think about how to select multiple things will one query, as Mithun describes.

Then see if you have a single SELECT that provides a bunch of rows and columns that provide the desired data.

Then think about how to combine that into a JSON string(s).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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