如何优化这个mysql查询？ 在nginx和fastcgi上

Question

i have a web app built on code igniter framework on the top of nginx , fastcgi and mysql

i have a payout table . table structure is here.

in this table , country names , perminutecost vs are stored and nearly 56,373 records on it.

---

in main page , there is a form that requests user to type his cellphone number to retrieve perminute cost.Btw i am using auto-complete feature as user types in

here is my backend code:

$str holds user input(cellphone number)

$ret = true; $count = 3;

        while($ret){
            $sub = substr($str,0,$count); //9053
            $ret = R::getAll("SELECT Destination,PerMinuteCost FROM `payout` WHERE `Prefix` REGEXP '^$sub(.)*$' LIMIT 0 , 30");
            $count++;
        }

        $sub = substr($str,0,$count-2);

        $ret =  R::getAll("SELECT Destination,PerMinuteCost FROM `payout` WHERE `Prefix` REGEXP '^$sub(.)*$' LIMIT 0 , 30");

        return $ret[0];

this code lets me get perminutecost from a cellphone number.(the table holds just prefixes not all the cell phone numbers)

i did some modifications on nginx and fastcgi to extend timeout limits

but when too much people use the service at the same time , mysqld cpu usage is getting over 100% ,

how could i improve this algorithm ?

thanks.

Answer 1

I think just a LIKE '$sub%' would be faster than regex and it might be better for your db if they are not autocompleting until 3 numbers.

Post some example SQL output if you put "EXPLAIN" at the beginning of the sql outside of this script.