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;
Best Answer
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:
to this kind of query.
Let me know if that helps.
reference: link1, link2