php - Make Query More Efficent (Really slow query taking forever!) -

what trying of users right conditions i'm building foreach statment sub_queries make work.

the problem got 100,000+ records in database. , kind of query takes forever run.

i know i'm not doing in best way tried left joins, slow too..

this function i'm using:

    public function get_affected_users_by_conditions($conditions, $mobile_type)     {         // basic query          // selecting of users `enswitch_mobile users` table         // total , `users` conditions         $sql = "select count(*) `users`,                        (select count(*) `enswitch_mobile_users`) `total`                                      `enswitch_mobile_users` `musers`                                      `musers`.`phone_type` = :mobile_type";          $value_counter = 0;         $values = array();         // foreach loop talking         // looping conditons.         // , when theres value i'm adding subquery.         foreach($conditions $cnd) {             switch ($cnd['condition']) {                  // slow sub-query:                 case 'talked_atleast':                     $value_counter++;                     // here i'm trying cut query users talked atleast $value seconds                     $sql .= " , (select sum(time_to_sec(timediff(`finished_call`,`start_call`))) `enswitch_calls` `user_id` = `musers`.`id`) >= :value".$value_counter;                     $values[$value_counter] = $cnd['value'];                     break;  // slow sub-query:                 case 'purchase_atleast':                     // here trying cut users subquery check if users has bought @ least $value times                     $value_counter++;                     $sql .= " , (select count(*) (select user_id enswitch_new_iphone_purchases                                                     union                                                   select user_id enswitch_new_android_purchases) p `status` > 0 , user_id` = `musers`.`id`) >= :value".$value_counter;                     $values[$value_counter] = $cnd['value'];                     break; // slow sub-query:                 case 'never_purchase':                     // here trying cut users subquery users never made puchase.                     $sql .= ' , (select count(*) (select user_id enswitch_new_iphone_purchases                                                     union                                                   select user_id enswitch_new_android_purchases) p `status` = 0 , `user_id` = `musers`.`id`) = 0';                     break;             }         }           $query = db::query(database::select, $sql);         $query->bind(':mobile_type', $mobile_type);         // looping values , binding sql query!         foreach ($values $k => $v) {             $query->bind(':value'.$k, $values[$k]);         }         // executing query         $result = $query->execute();         return array('total_users' =>$result[0]['total'], 'affected_users'=>$result[0]['users']);     } 

edit: slowest query requested: (mysql)

select count(*) `users`,        ( select count(*)             `enswitch_mobile_users`        ) `total`   `enswitch_mobile_users` `musers`  `musers`.`phone_type` = 'iphone'    , ( select count(*)             ( select `status`,                          `user_id`                      `enswitch_new_iphone_purchases`                   union                   select `status`,                          `user_id`                      `enswitch_new_android_purchases`                 ) `p`            `p`.`status` > 0              , `p`.`user_id` = `musers`.`id`        ) >= 2 

the subquery in second select column execute every m_users row passes where condition:

select   count(*) users,   (select count(*) enswitch_mobile_users) total <-- here's problem enswitch_mobile_users musers musers.phone_type = whatever 

if i'm reading correctly, need one-row result following columns:

• users - number of enswitch_mobile_users rows specified phone_type
• total - count of enswitch_mobile_users rows

you can same result query:

select   count(case when musers.phone_type = whatever 1 end) users,   count(*) total enswitch_mobile_users 

the case checks phone type, , if matches 1 you're interested it yields 1, counted. if doesn't match, yields null, not counted.