SQL_CALC_FOUND_ROWS on UNION to get total row but with LIMIT on sub query

Question

I'm using MySQL and have two separate tables, PRE_REGISTERED and REGISTERED, and I want to query them using UNION to get the whole combine result. I also use limit for pagination. And at the end I want to get the total row using SQL_CALC_FOUND_ROWS for the pagination. However, my data is pretty large about 160,000 rows on PRE_REGISTERED. If I UNION ALL eventhough I limit the data to 10 row each pages, still the query takes pretty long (about 6 seconds). The query like this

SELECT SQL_CALC_FOUND_ROWS U.* (
SELECT R.id, R.first_name, R.last_name, R.status
FROM REGISTERED R
WHERE R.status = 2)
UNION ALL
(SELECT PR.id, PR.first_name, PR.last_name, PR.status
FROM PRE_REGISTERED PR
WHERE PR.status = 3)
as U ORDER BY id LIMIT 0, 10

I then try to put the LIMIT on each sub query, the result is fascinating (about 0.5 seconds), but the pagination become messed up.

The query is like this:

SELECT (
SELECT R.id, R.first_name, R.last_name, R.status
FROM REGISTERED R
WHERE R.status = 2 LIMIT 0, 10)
UNION ALL
(SELECT PR.id, PR.first_name, PR.last_name, PR.status
FROM PRE_REGISTERED PR
WHERE PR.status = 3 LIMIT 0, 10)
as U ORDER BY id LIMIT 0, 10

Is there any way to still get the total row with using LIMIT at the sub queries?

I decided to put the whole query, but it's pretty long. I hope someone can spot the error and help me out. I'm totally stuck.

MY ACTUAL SQL QUERY

SELECT SQL_CALC_FOUND_ROWS U.*, S.name as spc_name, S.short_desc as 
spc_short_desc, GROUP_CONCAT(DISTINCT(S2.designation) SEPARATOR ', ') as 
spc_dsg, GROUP_CONCAT(DISTINCT(EM.designation) SEPARATOR ', ') as em_dsg, 
GROUP_CONCAT(DISTINCT(EF.designation) SEPARATOR ', ') as ef_dsg FROM (

(
 /** REGISTERED **/
SELECT D.id, D.doc_title, D.user_email, D.first_name, D.last_name, 
D.gender, D.dob, D.rating, D.no_of_rating, D.photo_file, 
D.verification_status, ((D.rating * 8) + D.no_of_rating) as rating_score, 
GROUP_CONCAT(DISTINCT(PP.id) SEPARATOR '|') as pp_id, 
GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, PP.name)) SEPARATOR '|') as 
pp_name, GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, PP.address)) SEPARATOR 
'|') as pp_address, GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, PP.phone)) 
SEPARATOR '|') as pp_phone, GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, 
V.name)) SEPARATOR '|') as pp_vil, GROUP_CONCAT(DISTINCT(CONCAT_WS('#', 
PP.id, SD.name)) SEPARATOR '|') as pp_sub_d, 
GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, C.name)) SEPARATOR '|') as 
pp_city, GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, P.name)) SEPARATOR '|') 
as pp_province, GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, PP.zipcode)) 
SEPARATOR '|') as pp_zip, GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, 
RDPP.is_primary)) SEPARATOR '|') as pp_is_primary, 
GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, PP.latitude)) SEPARATOR '|') as 
pp_lat, GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, PP.longitude)) SEPARATOR 
'|') as pp_lng, GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, PP.type)) 
SEPARATOR '|') as pp_type, GROUP_CONCAT(DISTINCT(CONCAT_WS('#', PP.id, ( 
6371 * acos( cos( radians(-6.2087634) ) * cos( radians( PP.latitude ) ) * 
cos( radians( PP.longitude ) - radians(106.845599) ) + sin( 
radians(-6.2087634) ) * sin( radians( PP.latitude ) ) ) ))) SEPARATOR '|') 
as pp_distance, MIN(( 6371 * acos( cos( radians(-6.2087634) ) * cos( 
radians( PP.latitude ) ) * cos( radians( PP.longitude ) - 
radians(106.845599) ) + sin( radians(-6.2087634) ) * sin( radians( 
PP.latitude ) ) ) )) as min_pp_distance, 
GROUP_CONCAT(DISTINCT(CONCAT_WS('#', D.id, DPW.day, DPW.start_time)) 
SEPARATOR '|') as dpweek_stime , (SELECT comment FROM ref_doctor_review RDR 
WHERE RDR.doctor_id = D.id ORDER BY RDR.date DESC LIMIT 1) as comment, 1 as 
reg_status FROM doctor D LEFT JOIN ref_doctor_practice_place RDPP ON 
RDPP.doctor_id = D.id LEFT JOIN practice_place PP ON RDPP.practice_place_id 
= PP.id LEFT JOIN duty_period DPW ON RDPP.id = DPW.ref_doctor_pplace_id 
INNER JOIN village V ON PP.village_id = V.id INNER JOIN sub_district SD ON 
V.sub_district_id = SD.id INNER JOIN city C ON SD.city_id = C.id INNER JOIN 
province P ON P.id = C.province_id LEFT JOIN city DC ON D.city_id = DC.id 
INNER JOIN province DP ON DP.id = DC.province_id AND ( DP.name LIKE 'DKI 
Jakarta%' OR DP.long_name LIKE 'DKI Jakarta%' ) WHERE D.active_form >= 5 AND 
D.verification_status = 1 GROUP BY D.id )

UNION ALL (

/** PRE REGISTERED **/
SELECT RD.id * -1 as id, RD.doc_title as doc_title, NULL as user_email, 
RD.first_name, RD.last_name, RD.gender, 0 as dob, 0 as rating, 0 as 
no_of_rating, null as photo_file, null as verification_status, 0 as 
rating_score, PP.id as pp_id, CONCAT_WS('#', PP.id, PP.name) as pp_name, 
CONCAT_WS('#', PP.id, PP.address) as pp_address, CONCAT_WS('#', PP.id, 
PP.phone) as pp_phone, CONCAT_WS('#', PP.id, V.name)as pp_vil, 
CONCAT_WS('#', PP.id, SD.name) as pp_sub_d, CONCAT_WS('#', PP.id, C.name) as 
pp_city, CONCAT_WS('#', PP.id, P.name) as pp_province, CONCAT_WS('#', PP.id, 
PP.zipcode) as pp_zip, 1 pp_is_primary, CONCAT_WS('#', PP.id, PP.latitude) 
as pp_lat, CONCAT_WS('#', PP.id, PP.longitude) as pp_lng, CONCAT_WS('#', 
PP.id, PP.type) as pp_type, CONCAT_WS('#', PP.id, IF(PP.latitude <> 0 AND 
PP.longitude <> 0, ( 6371 * acos( cos( radians(-6.2087634) ) * cos( radians( 
PP.latitude ) ) * cos( radians( PP.longitude ) - radians(106.845599) ) + 
sin( radians(-6.2087634) ) * sin( radians( PP.latitude ) ) ) ), 0)) as 
pp_distance, IF(PP.latitude <> 0 AND PP.longitude <> 0, ( 6371 * acos( cos( 
radians(-6.2087634) ) * cos( radians( PP.latitude ) ) * cos( radians( 
PP.longitude ) - radians(106.845599) ) + sin( radians(-6.2087634) ) * sin( 
radians( PP.latitude ) ) ) ) , 9999999) as min_pp_distance, null as dp_week, 
null as comment, RD.status as reg_status FROM register_doctor RD LEFT JOIN 
ref_doctor_practice_place RDPP ON RDPP.doctor_id = RD.id * -1 LEFT JOIN 
practice_place PP ON PP.id = RDPP.practice_place_id LEFT JOIN village V ON 
V.id = PP.village_id LEFT JOIN sub_district SD ON SD.id = PP.sub_district_id 
LEFT JOIN city C ON C.id = SD.city_id INNER JOIN province P ON P.id = 
PP.province_id LEFT JOIN city RDC ON RDC.id = RD.city_id INNER JOIN province 
RDP ON RDP.id = RD.province_id AND ( RDP.name LIKE 'DKI Jakarta%' OR 
RDP.long_name LIKE 'DKI Jakarta%' ) WHERE RD.status = 2 GROUP BY RD.id )

) AS U INNER JOIN ref_doctor_specialty RDS ON RDS.doctor_id = U.id AND 
RDS.is_primary = 1 INNER JOIN specialty S ON S.id = RDS.specialty_id INNER 
JOIN ref_doctor_specialty RDS2 ON RDS2.doctor_id = U.id INNER JOIN specialty 
S2 ON S2.id = RDS2.specialty_id LEFT JOIN ref_doctor_education RDE ON 
RDE.doctor_id = U.id LEFT JOIN edu_magister EM ON RDE.type = 4 AND EM.id = 
RDE.ref_edu_id LEFT JOIN edu_fellowship EF ON RDE.type = 5 AND EF.id = 
RDE.ref_edu_id WHERE U.min_pp_distance < 100 OR U.min_pp_distance = 9999999 
GROUP BY U.id ORDER BY U.reg_status ASC, U.min_pp_distance ASC, RAND() LIMIT 
0,10

PS. I have put almost all necessary columns for indexes.

I will show my Schema Here

CREATE TABLE `doctor` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `doc_title` varchar(15) DEFAULT NULL,
 `user_email` varchar(50) NOT NULL,
 `first_name` varchar(35) NOT NULL,
 `last_name` varchar(35) DEFAULT NULL,
 `gender` int(11) NOT NULL DEFAULT '0',
 `dob` bigint(11) NOT NULL DEFAULT '0',
 `rating` float NOT NULL DEFAULT '0',
 `no_of_rating` int(11) NOT NULL DEFAULT '0',
 `statement` text,
 `password` char(64) NOT NULL,
 `city_id` int(11) NOT NULL,
 `active_form` int(11) NOT NULL DEFAULT '0',
 `photo_file` varchar(40) NOT NULL,
 `str_number` char(6) DEFAULT NULL,
 `verify_file` varchar(40) DEFAULT NULL,
 `verification_status` int(11) NOT NULL COMMENT '0 = Not verified, 1 = Verified, 2 = Pending Verification',
 `reg_id` int(11) NOT NULL,
 `reg_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `city_id` (`city_id`),
 KEY `verification_status` (`verification_status`),
 KEY `active_form` (`active_form`),
 CONSTRAINT `doctor_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=latin1

CREATE TABLE `register_doctor` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `doc_title` int(11) NOT NULL,
 `first_name` varchar(35) NOT NULL,
 `last_name` varchar(35) DEFAULT NULL,
 `gender` int(11) NOT NULL,
 `city_id` int(11) NOT NULL,
 `province_id` int(11) NOT NULL,
 `status` int(11) NOT NULL COMMENT '0 = Pending; 1 = Verified, 2 = Not Reg Yet, 3 = Pending Approval',
 `str_number` char(6) DEFAULT NULL,
 `editted_by` int(11) DEFAULT NULL,
 `editted_date` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `city_id` (`city_id`),
 KEY `province_id` (`province_id`),
 KEY `status` (`status`),
 CONSTRAINT `register_doctor_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=179327 DEFAULT CHARSET=latin1

CREATE TABLE `practice_place` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(75) NOT NULL,
 `statement` text,
 `address` varchar(200) NOT NULL,
 `phone` varchar(15) NOT NULL,
 `fax` varchar(15) NOT NULL,
 `email` varchar(50) NOT NULL,
 `village_id` varchar(50) NOT NULL,
 `sub_district_id` varchar(50) NOT NULL,
 `province_id` varchar(50) NOT NULL,
 `zipcode` varchar(10) NOT NULL,
 `website` varchar(50) NOT NULL,
 `latitude` double NOT NULL,
 `longitude` double NOT NULL,
 `type` int(11) NOT NULL,
 `managed_by` int(11) DEFAULT '0',
 `doctor_group_id` int(11) NOT NULL,
 `category` varchar(50) NOT NULL,
 `photo_file` char(36) NOT NULL,
 `is_branch` int(11) NOT NULL,
 `parent_id` int(11) NOT NULL,
 `editted_by` int(11) NOT NULL,
 `editted_date` bigint(20) NOT NULL,
 `status` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `village_id` (`village_id`),
 KEY `doctor_group_id` (`doctor_group_id`),
 KEY `province_id` (`province_id`),
 KEY `type` (`type`),
 KEY `parent_id` (`parent_id`),
 KEY `longitude` (`longitude`),
 KEY `latitude` (`latitude`)
) ENGINE=InnoDB AUTO_INCREMENT=25554 DEFAULT CHARSET=latin1

CREATE TABLE `ref_doctor_practice_place` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `doctor_id` int(11) NOT NULL,
 `practice_place_id` int(11) NOT NULL,
 `is_primary` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `doctor_id_2` (`doctor_id`,`practice_place_id`),
 KEY `doctor_id` (`doctor_id`),
 KEY `practice_place_id` (`practice_place_id`),
 CONSTRAINT `ref_doctor_practice_place_ibfk_1` FOREIGN KEY (`practice_place_id`) REFERENCES `practice_place` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38772 DEFAULT CHARSET=latin1

CREATE TABLE `ref_doctor_specialty` (
 `doctor_id` int(11) NOT NULL,
 `specialty_id` int(11) NOT NULL,
 `is_primary` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`doctor_id`,`specialty_id`),
 UNIQUE KEY `doctor_id_2` (`doctor_id`,`specialty_id`),
 KEY `doctor_id` (`doctor_id`),
 KEY `specialty_id` (`specialty_id`),
 KEY `is_primary` (`is_primary`),
 CONSTRAINT `ref_doctor_specialty_ibfk_1` FOREIGN KEY (`specialty_id`) REFERENCES `specialty` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `ref_doctor_education` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `doctor_id` int(11) NOT NULL,
 `type` int(11) NOT NULL,
 `institution` varchar(150) NOT NULL,
 `ref_edu_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `doctor_id` (`doctor_id`),
 KEY `ref_edu_id` (`ref_edu_id`),
 KEY `type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=187556 DEFAULT CHARSET=latin1

CREATE TABLE `edu_fellowship` (
 `id` int(11) NOT NULL,
 `name` varchar(150) NOT NULL,
 `designation` varchar(10) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `edu_magister` (
 `id` int(11) NOT NULL,
 `name` varchar(55) NOT NULL,
 `designation` varchar(15) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `village` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(35) NOT NULL,
 `sub_district_id` int(11) NOT NULL,
 `zipcode` char(5) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `sub_district_id` (`sub_district_id`),
 CONSTRAINT `village_ibfk_1` FOREIGN KEY (`sub_district_id`) REFERENCES `sub_district` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=81360 DEFAULT CHARSET=latin1

CREATE TABLE `sub_district` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(35) NOT NULL,
 `city_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `city_id` (`city_id`),
 CONSTRAINT `sub_district_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7000 DEFAULT CHARSET=latin1

CREATE TABLE `province` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(40) NOT NULL,
 `long_name` varchar(40) NOT NULL,
 `abbreviation` char(2) NOT NULL,
 `area_group` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=latin1

CREATE TABLE `duty_period` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `day` int(11) NOT NULL,
 `start_time` bigint(11) NOT NULL,
 `end_time` bigint(11) NOT NULL,
 `ref_doctor_pplace_id` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `day` (`day`,`start_time`,`ref_doctor_pplace_id`),
 KEY `ref_doctor_pplace_id` (`ref_doctor_pplace_id`),
 CONSTRAINT `duty_period_ibfk_1` FOREIGN KEY (`ref_doctor_pplace_id`) REFERENCES `ref_doctor_practice_place` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=766 DEFAULT CHARSET=latin1

    CREATE TABLE `ref_doctor_review` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date` bigint(20) NOT NULL,
 `doctor_id` int(11) NOT NULL,
 `helpfulness_rating` double NOT NULL,
 `punctuality_rating` double NOT NULL,
 `knowledge_rating` double NOT NULL,
 `staff_rating` double NOT NULL,
 `user_id` int(11) NOT NULL,
 `comment` text,
 `patient_id` int(11) NOT NULL DEFAULT '0',
 `is_anonymous` int(11) NOT NULL DEFAULT '0',
 `date_visit` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `doctor_id` (`doctor_id`),
 KEY `patient_id` (`user_id`),
 CONSTRAINT `ref_doctor_review_ibfk_1` FOREIGN KEY (`doctor_id`) REFERENCES `doctor` (`id`),
 CONSTRAINT `ref_doctor_review_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

CREATE TABLE `city` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(40) NOT NULL,
 `city_type` int(11) NOT NULL,
 `province_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `state_id` (`province_id`),
 CONSTRAINT `city_ibfk_1` FOREIGN KEY (`province_id`) REFERENCES `province` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=515 DEFAULT CHARSET=latin1

CREATE TABLE `specialty` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(80) NOT NULL,
 `short_desc` varchar(50) NOT NULL,
 `name_en` varchar(80) NOT NULL,
 `short_desc_en` varchar(50) NOT NULL,
 `designation` varchar(15) NOT NULL,
 `is_popular` int(11) NOT NULL DEFAULT '0',
 `spc_group` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `spc_group` (`spc_group`)
) ENGINE=InnoDB AUTO_INCREMENT=307 DEFAULT CHARSET=latin1

That's All! The schema... pheww. Help me out Drew. :)

EXPLAIN Table

| id | select_type | table | type   | possible_keys | key | key_len | ref | rows  | Extra |
    1   PRIMARY RDS ALL PRIMARY,doctor_id_2,doctor_id,specialty_id              30  Using where; Using temporary; Using filesort
    1   PRIMARY S   eq_ref  PRIMARY PRIMARY 4   lokadok.RDS.specialty_id    1   
    1   PRIMARY <derived2>  ref <auto_key0> <auto_key0> 8   lokadok.RDS.doctor_id   76  Using index condition
    1   PRIMARY RDS2    ref PRIMARY,doctor_id_2,doctor_id,specialty_id  PRIMARY 4   U.id    1   Using where; Using index
    1   PRIMARY S2  eq_ref  PRIMARY PRIMARY 4   lokadok.RDS2.specialty_id   1   
    1   PRIMARY RDE ref doctor_id   doctor_id   4   U.id    1   Using where
    1   PRIMARY EM  eq_ref  PRIMARY PRIMARY 4   lokadok.RDE.ref_edu_id  1   Using where
    1   PRIMARY EF  eq_ref  PRIMARY PRIMARY 4   lokadok.RDE.ref_edu_id  1   Using where
    2   DERIVED D   index   PRIMARY,city_id,full_name   PRIMARY 4       37  Using where
    2   DERIVED DC  eq_ref  PRIMARY,state_id    PRIMARY 4   lokadok.D.city_id   1   Using where
    2   DERIVED DP  eq_ref  PRIMARY PRIMARY 4   lokadok.DC.province_id  1   Using where
    2   DERIVED RDPP    ref practice_place_id,doctor_id doctor_id   4   lokadok.D.id    1   
    2   DERIVED DPW ref ref_doctor_pplace_id    ref_doctor_pplace_id    4   lokadok.RDPP.id 1   
    2   DERIVED PP  eq_ref  PRIMARY,village_id  PRIMARY 4   lokadok.RDPP.practice_place_id  1   Using where
    2   DERIVED V   eq_ref  PRIMARY,sub_district_id PRIMARY 4   lokadok.PP.village_id   1   Using where
    2   DERIVED SD  eq_ref  PRIMARY,city_id PRIMARY 4   lokadok.V.sub_district_id   1   
    2   DERIVED C   eq_ref  PRIMARY,state_id    PRIMARY 4   lokadok.SD.city_id  1   
    2   DERIVED P   eq_ref  PRIMARY PRIMARY 4   lokadok.C.province_id   1   
    3   DEPENDENT SUBQUERY  RDR ref doctor_id   doctor_id   4   func    1   Using where; Using filesort
    4   UNION   RDP ALL PRIMARY             36  Using where; Using temporary; Using filesort
    4   UNION   RD  ref PRIMARY,city_id,province_id,full_name   province_id 4   lokadok.RDP.id  210 Using where
    4   UNION   RDC eq_ref  PRIMARY PRIMARY 4   lokadok.RD.city_id  1   Using index
    4   UNION   RDPP    ref practice_place_id,doctor_id doctor_id   4   func    1   Using index condition
    4   UNION   PP  eq_ref  PRIMARY PRIMARY 4   lokadok.RDPP.practice_place_id  1   Using where
    4   UNION   P   eq_ref  PRIMARY PRIMARY 4   lokadok.PP.province_id  1   Using where
    4   UNION   V   eq_ref  PRIMARY PRIMARY 4   lokadok.PP.village_id   1   Using where
    4   UNION   SD  eq_ref  PRIMARY PRIMARY 4   lokadok.PP.sub_district_id  1   Using where
    4   UNION   C   eq_ref  PRIMARY PRIMARY 4   lokadok.SD.city_id  1   
        UNION RESULT    <union2,4>  ALL                     Using temporary

The Statistic Results:

    +------------+----------------------------+------------+-----------------------+--------------+-----------------------+------------+-------------+-------------+-------------+----------+------------+
    | table_rows | table_name                 | non_unique | index_name            | seq_in_index | column_name           | collation  | cardinality | sub_part    | packed      | nullable | index_type |
    +------------+----------------------------+------------+-----------------------+--------------+-----------------------+------------+-------------+-------------+-------------+----------+------------+
    "514"        | "city"                     | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "514"       | NULL        | NULL        |          | "BTREE"
    "514"        | "city"                     | "1"        | "state_id"            | "1"          | "province_id"         | "A"        | "73"        | NULL        | NULL        |          | "BTREE"
    "259"        | "doctor"                   | "1"        | "active_form"         | "1"          | "active_form"         | "A"        | "12"        | NULL        | NULL        |          | "BTREE"
    "259"        | "doctor"                   | "1"        | "city_id"             | "1"          | "city_id"             | "A"        | "259"       | NULL        | NULL        |          | "BTREE"
    "259"        | "doctor"                   | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "259"       | NULL        | NULL        |          | "BTREE"
    "259"        | "doctor"                   | "1"        | "verification_status" | "1"          | "verification_status" | "A"        | "6"         | NULL        | NULL        |          | "BTREE"
    "690"        | "duty_period"              | "0"        | "day"                 | "2"          | "start_time"          | "A"        | "345"       | NULL        | NULL        |          | "BTREE"
    "690"        | "duty_period"              | "0"        | "day"                 | "1"          | "day"                 | "A"        | "14"        | NULL        | NULL        |          | "BTREE"
    "690"        | "duty_period"              | "0"        | "day"                 | "3"          | "ref_doctor_pplace_id"| "A"        | "690"       | NULL        | NULL        |          | "BTREE"
    "690"        | "duty_period"              | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "690"       | NULL        | NULL        |          | "BTREE"
    "690"        | "duty_period"              | "1"        | "ref_doctor_pplace_id"| "1"          | "ref_doctor_pplace_id"| "A"        | "345"       | NULL        | NULL        |          | "BTREE"
    "15"         | "edu_fellowship"           | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "15"        | NULL        | NULL        |          | "BTREE"
    "19"         | "edu_magister"             | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "19"        | NULL        | NULL        |          | "BTREE"
    "5766"       | "practice_place"           | "1"        | "doctor_group_id"     | "1"          | "doctor_group_id"     | "A"        | "411"       | NULL        | NULL        |          | "BTREE"
    "5766"       | "practice_place"           | "1"        | "latitude"            | "1"          | "latitude"            | "A"        | "5766"      | NULL        | NULL        |          | "BTREE"
    "5766"       | "practice_place"           | "1"        | "longitude"           | "1"          | "longitude"           | "A"        | "5766"      | NULL        | NULL        |          | "BTREE"
    "5766"       | "practice_place"           | "1"        | "parent_id"           | "1"          | "parent_id"           | "A"        | "5766"      | NULL        | NULL        |          | "BTREE"
    "5766"       | "practice_place"           | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "5766"      | NULL        | NULL        |          | "BTREE"
    "5766"       | "practice_place"           | "1"        | "province_id"         | "1"          | "province_id"         | "A"        | "74"        | NULL        | NULL        |          | "BTREE"
    "5766"       | "practice_place"           | "1"        | "type"                | "1"          | "type"                | "A"        | "4"         | NULL        | NULL        |          | "BTREE"
    "5766"       | "practice_place"           | "1"        | "village_id"          | "1"          | "village_id"          | "A"        | "5766"      | NULL        | NULL        |          | "BTREE"
    "36"         | "province"                 | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "36"        | NULL        | NULL        |          | "BTREE"
    "160406"     | "ref_doctor_education"     | "1"        | "doctor_id"           | "1"          | "doctor_id"           | "A"        | "160406"    | NULL        | NULL        |          | "BTREE"
    "160406"     | "ref_doctor_education"     | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "160406"    | NULL        | NULL        |          | "BTREE"
    "160406"     | "ref_doctor_education"     | "1"        | "ref_edu_id"          | "1"          | "ref_edu_id"          | "A"        | "128"       | NULL        | NULL        |          | "BTREE"
    "160406"     | "ref_doctor_education"     | "1"        | "type"                | "1"          | "type"                | "A"        | "8"         | NULL        | NULL        |          | "BTREE"
    "15871"      | "ref_doctor_practice_place"| "1"        | "doctor_id"           | "1"          | "doctor_id"           | "A"        | "15871"     | NULL        | NULL        |          | "BTREE"
    "15871"      | "ref_doctor_practice_place"| "0"        | "doctor_id_2"         | "2"          | "practice_place_id"   | "A"        | "15871"     | NULL        | NULL        |          | "BTREE"
    "15871"      | "ref_doctor_practice_place"| "0"        | "doctor_id_2"         | "1"          | "doctor_id"           | "A"        | "15871"     | NULL        | NULL        |          | "BTREE"
    "15871"      | "ref_doctor_practice_place"| "1"        | "practice_place_id"   | "1"          | "practice_place_id"   | "A"        | "3174"      | NULL        | NULL        |          | "BTREE"
    "15871"      | "ref_doctor_practice_place"| "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "15871"     | NULL        | NULL        |          | "BTREE"
    "2"          | "ref_doctor_review"        | "1"        | "doctor_id"           | "1"          | "doctor_id"           | "A"        | "2"         | NULL        | NULL        |          | "BTREE"
"2"          | "ref_doctor_review"        | "1"        | "patient_id"          | "1"          | "user_id"             | "A"        | "2"         | NULL        | NULL        |         | "BTREE"
"2"          | "ref_doctor_review"        | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "2"         | NULL        | NULL        |         | "BTREE"
"166830"     | "ref_doctor_specialty"     | "1"        | "is_primary"          | "1"          | "is_primary"          | "A"        | "2"         | NULL        | NULL        |         | "BTREE"
"166830"     | "ref_doctor_specialty"     | "0"        | "PRIMARY"             | "2"          | "specialty_id"        | "A"        | "166830"    | NULL        | NULL        |         | "BTREE"
"166830"     | "ref_doctor_specialty"     | "0"        | "PRIMARY"             | "1"          | "doctor_id"           | "A"        | "166830"    | NULL        | NULL        |         | "BTREE"
"166830"     | "ref_doctor_specialty"     | "1"        | "specialty_id"        | "1"          | "specialty_id"        | "A"        | "166"       | NULL        | NULL        |         | "BTREE"
"165958"     | "register_doctor"          | "1"        | "city_id"             | "1"          | "city_id"             | "A"        | "1152"      | NULL        | NULL        |         | "BTREE"
"165958"     | "register_doctor"          | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "165958"    | NULL        | NULL        |         | "BTREE"
"165958"     | "register_doctor"          | "1"        | "province_id"         | "1"          | "province_id"         | "A"        | "66"        | NULL        | NULL        |         | "BTREE"
"165958"     | "register_doctor"          | "1"        | "status"              | "1"          | "status"              | "A"        | "4"         | NULL        | NULL        |         | "BTREE"
"101"        | "specialty"                | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "101"       | NULL        | NULL        |         | "BTREE"
"101"        | "specialty"                | "1"        | "spc_group"           | "1"          | "spc_group"           | "A"        | "16"        | NULL        | NULL        |         | "BTREE"
"6999"       | "sub_district"             | "1"        | "city_id"             | "1"          | "city_id"             | "A"        | "1166"      | NULL        | NULL        |         | "BTREE"
"6999"       | "sub_district"             | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "6999"      | NULL        | NULL        |         | "BTREE"
"80700"      | "village"                  | "0"        | "PRIMARY"             | "1"          | "id"                  | "A"        | "80700"     | NULL        | NULL        |         | "BTREE"
"80700"      | "village"                  | "1"        | "sub_district_id"     | "1"          | "sub_district_id"     | "A"        | "13450"     | NULL        | NULL        |         | "BTREE"

you are referring to these two (...,1 as reg_status FROM doctor D ... **union**...,RD.status as reg_status) — Drew, Sep 14 '15 at 21:01
the doctor having 1 as status (1 as reg_status FROM doctor D) and register_doctor is gotten from RD.statius field. — yodann, Sep 14 '15 at 21:02
this probably has little to do with status, I was going off of your (highly) abbreviated first posting — Drew, Sep 14 '15 at 21:03
haha, yea. i thought the problem was so simple. but it turn out not. — yodann, Sep 14 '15 at 21:38
that wasn't much hope there anyway, unless you have jillions of provinces — Drew, Sep 14 '15 at 21:39
can you show the city table. I am off building this thing. plus another busy work assignment for you in a second — Drew, Sep 14 '15 at 21:41
`UNION` and `SQL_CALC_FOUND_ROWS` do not play together well; see http://dev.mysql.com/doc/refman/5.6/en/information-functions.html and search for 'union'. Also, 5.7 fixes "A UNION ALL query with SQL_CALC_FOUND_ROWS and a LIMIT with an offset for one query block reported an incorrect number of found rows. (Bug #17833261)" — Rick James, Sep 15 '15 at 02:24
if appears that with `UNION ALL` the `SQL_CALC_FOUND_ROWS` will be exact, unlike with just `UNION`. What performance results do you get with and without the SQL_CALC_FOUND_ROWS as the first column ? — Drew, Sep 15 '15 at 02:32
can you run the first chunk and time it, second chunk and time it, of the union (but not the union) — Drew, Sep 15 '15 at 02:54
so then how fast is `select * from (blah union blah) as u` take? Not including the very top, not the very bottom after `) as u` ... in other words, just lines 7 thru 123 of [pastie](http://pastie.org/10420517) — Drew, Sep 15 '15 at 03:43
it took pretty long. about 3.9022 secs. i guess the culprit is still in the middle. — yodann, Sep 15 '15 at 03:59
yeah, it's called the union stmt, looking it up, because it should be at best, say, 1.2 — Drew, Sep 15 '15 at 04:01
first chunk 0.3460 secs return 17 rows, 2nd chunck 0.1120 secs, faster with 3846 rows. what do you think drew? — yodann, Sep 15 '15 at 04:03

Rick James · Answer 1 · 2015-09-15T02:55:19.160

When using OFFSET and LIMIT with UNION, the patter goes something like this:

( SELECT ...
    ORDER BY x  -- required
    LIMIT 130   -- outer OFFSET + LIMIT
) UNION ALL     -- DISTINCT might work, too
( SELECT ...
    ORDER BY x  -- required
    LIMIT 130   -- outer OFFSET + LIMIT
)
ORDER BY x      -- again
LIMIT 120, 10   -- outer OFFSET and LIMIT

If all (or just some) of one of the subqueries has the desired rows, then 120+10 is sufficient.

The same ORDER BY is required everywhere so that you pick off the right rows.

Meanwhile, I would not trust SQL_CALC_FOUND_ROWS to give you the right answer. See http://dev.mysql.com/doc/refman/5.6/en/information-functions.html and search for 'union'. Also, 5.7.5 fixes "A UNION ALL query with SQL_CALC_FOUND_ROWS and a LIMIT with an offset for one query block reported an incorrect number of found rows. (Bug #17833261)"

Addenda

Add SQL_CALC_FOUND_ROWS to the first SELECT, use ALL. See if FOUND_ROWS() gives you the right answer. If you can't use ALL, or if the LIMITs screw up the CALC, then I suggest that you cannot get the answer at the same time you are building the page.

The query you have must look through all the data, if for no reason other than ORDER BY RAND(). That is, even if some index(es) could speed it up, they cannot short circuit the fetching of lots of data.

score 0 · Answer 2 · answered Sep 15 '15 at 02:48

I would run two queries, one with a count(*) and no limit, and another without the sub-select and without SQL_CALC_FOUND_ROWS. I had to query about 90k rows in batches and also know the total and that's what I ended up doing.

The count:

SELECT count(*) from (
(SELECT R.id, R.first_name, R.last_name, R.status
FROM REGISTERED R
WHERE R.status = 2)
UNION ALL
(SELECT PR.id, PR.first_name, PR.last_name, PR.status
FROM PRE_REGISTERED PR
WHERE PR.status = 3)) t;

The actual data:

(SELECT R.id, R.first_name, R.last_name, R.status
FROM REGISTERED R
WHERE R.status = 2)
UNION ALL
(SELECT PR.id, PR.first_name, PR.last_name, PR.status
FROM PRE_REGISTERED PR
WHERE PR.status = 3)
ORDER BY id LIMIT 0, 10

You could also break it down into 3 queries, 2 counts and 1 data, and add the two counts together.

score 0 · Answer 3 · edited May 23 '17 at 10:30

0

I think I found the slow factor. It is this part that makes the whole query become slow. This variable is an alias and it is a formula.

ORDER BY .... U.min_pp_distance

I haven't found the work around though, but this confirm the issue: MySQL-Performance when ordering on calculated column

edited May 23 '17 at 10:30

Community

1
1

answered Sep 15 '15 at 15:09

yodann

355
5
20

SQL_CALC_FOUND_ROWS on UNION to get total row but with LIMIT on sub query

I will show my Schema Here

3 Answers3