如何优化显示数千个数据的长查询

I have almost thousands of data to display for my reports and it makes my browser lags due to the heavy data. I think that my query is the real problem. How can I optimized my query? is there something that I should add in my query?

I am using Xampp which supports PHP7.

 SELECT 
`payroll_billed_units`.`allotment_code`,
`payroll_billed_units`.`category_name`,
`payroll_billed_units`.`ntp_number`,
`payroll_billed_units`.`activity`,
`payroll_billed_units`.`regular_labor`,
`payroll_sub`.`block_number`,
(SELECT 
    GROUP_CONCAT(DISTINCT `lot_number` SEPARATOR ', ')
    FROM
        `payroll_billed_units` `lot_numbers`
    WHERE
        `lot_numbers`.`allotment_code` =  `payroll_billed_units`.`allotment_code`
            AND `lot_numbers`.`category_name` = `payroll_billed_units`.`category_name`
            AND `lot_numbers`.`ntp_number` = `payroll_billed_units`.`ntp_number`
            AND `lot_numbers`.`activity` = `payroll_billed_units`.`activity`) AS `lot_numbers`,
(SELECT 
        COUNT(`billed`.`ntp_id`)
    FROM
        `regular_ntp` `billed`
    WHERE
        `billed`.`allotment_code` = `payroll_billed_units`.`allotment_code`
            AND `billed`.`category_name` = `payroll_billed_units`.`category_name`
            AND `billed`.`ntp_number` = `payroll_billed_units`.`ntp_number`
            AND `billed`.`activity` = `payroll_billed_units`.`activity`) AS `billed`,
(SELECT 
        COUNT(`approved`.`id`)
    FROM
        `payroll_billed_units` `approved`
    WHERE
        `approved`.`allotment_code` = `payroll_billed_units`.`allotment_code`
            AND `approved`.`category_name` = `payroll_billed_units`.`category_name`
            AND `approved`.`ntp_number` = `payroll_billed_units`.`ntp_number`
            AND `approved`.`activity` = `payroll_billed_units`.`activity`) AS `approved`
 FROM
`payroll_billed_units` 
 JOIN payroll_transaction ON payroll_billed_units.billing_number = 
payroll_transaction.billing_number 
 JOIN payroll_sub ON payroll_transaction.billing_number = 
payroll_sub.billing_number 
WHERE payroll_billed_units.billing_date = '2019-02-13' 
AND payroll_transaction.contractor_name = 'Roy Codal' GROUP BY allotment_code, category_name, activity

I was expecting that it will load or display all my data.

The biggest problem are the dependendt sub-selects, they are responsible for a bad performance. A sub-select will be executed for EVERY ROW of the outer query. And if you cascade subs-selects, you'll quickly have a query run forever.

If any of the parts would yield only 5 resultsets, 3 sub-select would mean that the database has to run 625 queries (5^4)!

Use JOINs.

Several of your tables need this 'composite' index:

INDEX(allotment_code, category_name, ntp_number, activity)  -- in any order

payroll_transaction needs INDEX(contractor_name), though it may not get used.

payroll_billed_units needs INDEX(billing_date), though it may not get used.

For further discussion, please provide SHOW CREATE TABLE for each table and EXPLAIN SELECT ...

Use simply COUNT(*) instead of COUNT(foo). The latter checks the column for being not-NULL before including it. This is usually not needed. The reader is confused by thinking that there might be NULLs.

Your GROUP BY is improper because it is missing ntp_number. Read about the sql_mode of ONLY_FULL_GROUP_BY. I bring this up because you can almost get rid of some of those subqueries.

Another issue... Because of the "inflate-deflate" nature of JOIN with GROUP BY, the numbers may be inflated. I recommend you manually check the values of the COUNTs.