CODEWITHSUNDEEP
phpmysqlnginxfastcgiredbean
Eren Yagdiran
Eren Yagdiran

Reputation: 338

How to optimize this mysql Query? on nginx and fastcgi

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.

Upvotes: 1

Views: 489

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53870

Convert the Prefix column from TEXT to a VARCHAR column with the minimum required length, and add an index on the Prefix column.

Then, instead of using a regular expression, use LIKE with the % wildcard:

SELECT Destination, PerMinuteCost
FROM `payout` WHERE `Prefix` LIKE '$sub%'
LIMIT 0 , 30

Upvotes: 1

arc
arc

Reputation: 584

You could create another table that stores prefixes for your prefixes. For example, if an entry in your payout table has prefix = 12345, your prefixTable will have 5 associated rows: 1, 12, 123, 1234, and 12345. Each entry will be linked to the original record by a foreign key. To search, you would find an exact match in prefixTable and then join back to your payout table to get the payout information.

This will of course use up more space on the server but should offer you a significant speed boost.

Upvotes: 3

tristanbailey
tristanbailey

Reputation: 4605

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.

Upvotes: 3

Related Questions