CODEWITHSUNDEEP
phpmysqlcpu
Albi Hoti
Albi Hoti

Reputation: 351

CPU Usage from a simple php/mysql

in one of my websites Ive a simple php/mysql function that looks if an ip is in my db with the below code:

$ip = $_SERVER['REMOTE_ADDR'];
$user = mysql_query("SELECT * FROM users WHERE ip = '$ip' ");
if(mysql_num_rows($user) == 0){
echo 'IP is not banned';
}

Yesterday this file had about 1,700,000 hits (pageviews), and Im on a shared hosting, in coming days this file may be loaded about 3,000,000 times, will be any issue with cpu usage, the db have about 30k rows with: id,ip ?

Upvotes: 1

Views: 603

Answers (2)

szegedi
szegedi

Reputation: 873

What @driis said is generally sums it up: 1. limit the query to one 2. index the ip column However if you'd need even faster result, you should look into caching the query/table to the memory (e.g. memcached), but that's definitely more work.

You mentioned 3 million queries a day. According to @Rick Bradshaw's numbers (30ms without limit, ~15ms average with limit) the DB load would be around 1 on average, due to this query only, which is significant.

30ms*3million / seconds in a day = 1,04 (on average, this can be bad in rush hours)

15ms*3million / seconds in a day = 0,52 (this isn't small either)

In this case, I would recommend doing some concrete measuring on your system. And without knowing any more specific info indexing the column sounds a good idea.

Upvotes: 1

driis
driis

Reputation: 164331

While I don't think you will have a problem with CPU usage, if you just need to know if the user exists add a limit 1 as suggested.

If this is the primary way the table is being queried; consider adding an index to the ipcolumn if you do not already have one. It will reduce CPU and read operations for doing that query.

Upvotes: 2

Related Questions