Using count(*) slows down the server

[allex01]

I have about 14,000 rows in the users table. When i use select count(*), the query keeps executing for a long time which slows down the server and opens many connections. Is there any work around for this or an alternative faster way to use.

[corbin]

What is your table schema?  And what are your indexes?

[allex01]

Check attached screenshot. The users table contains around 100 colums and 14,000 rows. I'm using the following function to find users. Count is what causing the issue. When the function excutes it keeps running for over 12 minutes to find the users. this is putting a huge load on the server.

 

public function Total_referrals($opt,$user_id)
         {
            
            switch($opt)
            {
                                    case 1:
                                           $where=" where ";
                                           break; 
                                    case 2: 
                                           $doj_start = sprintf("%04d-%02d-%02d", date('Y'),date('m'),date('d'));
                       $dojStart  = $doj_start;
                                            $where=' where doj>= \''.addslashes($dojStart).'\''.' and';
                                            break; 
                                    case 3: $where=" where trans_id=0 and ow_id!=0 and ";
                                            break;        
               
                                    case 4: $where=" where trans_id!=0 and ow_id!=0 and ";
                                            break;        
               
            }
            $where.=' ow_id ='.$user_id;//$this->CFG['user']['user_id'];   
            $sql = 'SELECT count(*) as cnt FROM '.$this->CFG['db']['tbl']['users'];
            $sql .=$where; 
            $stmt = $this->dbObj->Prepare($sql);
            $rs = $this->dbObj->Execute($stmt);
            if (!$rs)
               trigger_error($this->dbObj->ErrorNo().' '.$this->dbObj->ErrorMsg(), E_USER_ERROR);
            if ($rs->PO_RecordCount())
            {
                if($row = $rs->FetchRow())
               {
                          $cnt=$row['cnt'];
                    } // while
            }
            return $cnt;
         }

 

.

[corbin]

What does an example query look like?  Just print out one on a page if you have to.

 

I would imagine you have WHERE criteria in there that's not indexed, which would cause a 14,000 row table scan, which is bad.

[allex01]

Query: SELECT user_id, user_name, sex, first_name, allow_article, last_name, email, age, total_videos, total_musics, total_photos, total_friends, 1 AS total_groups, DATE_FORMAT(doj, '%D %b %y') AS doj, DATE_FORMAT(last_logged, '%D %b %y') AS last_logged, profile_tags, 1 AS tag_match, icon_id, icon_type, usr_status, DATE_FORMAT(last_logged, '%D %b %y') AS last_logged, city, postal_code, country, featured, is_affiliate_type FROM users WHERE usr_status!='Deleted' AND user_id in (select ow_id from users) ORDER BY user_id ASC LIMIT 0,16

Query: SELECT COUNT(*) AS count FROM users WHERE usr_status!='Deleted' AND user_id in (select ow_id from users)

[allex01]

user_id in (select ow_id from users)

 

need alternative option for the above line...

 

 

[corbin]

Two things are probably killing you....

 

 

The subquery.

You should put an index on usr_status.

 

 

What exactly is ow_id?