1

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 ?

Improve this question
4
  • 3
    I'd add a 'LIMIT 1' to the query to speed it up a bit. Really you don't care how many there are, you just care if there's 0 or 1. Commented Jan 19, 2013 at 10:46
  • yes 0 or 1 is the only result Commented Jan 19, 2013 at 10:51
  • 1
    Also, why not change * to id, or even count(id) and check that? Commented Jan 19, 2013 at 10:52
  • Rich Bradshaw, Im beginner on php/mysql, i will check this Commented Jan 19, 2013 at 10:54

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Yep, I just tested the extreme case for this, table, 35,000 rows, selected what I know is first row. Without LIMIT 1 = around 30ms, with LIMIT 1 = 0.5ms. However, choosing the last row means they both are around 30ms. Either way, it's going to help!
@RichBradshaw, the same ip excist only 1 time in database, so the result is just 0 or 1, any difference on this?
@AlbiHoti, also take a look at your indexes if you are worried, that is likely what will bring you the most performance gain.
Check it and see – measuring is the only way to know for sure. Execution time is often a good indicator for this sort of thing.
1

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.

Improve this answer

3 Comments

tested the load time and in average its taking about 0.003 seconds to load but if I add 'limit 1' on average it takes about 0.004 seconds (tested with 1500 rows)
You can ignore the difference then. 3-4 miliseconds means in this case,that it shouldn't be that much of a difference. Also, with 4ms everything is okay. Just watch out for the rush hours. MySQL's query cache can also affect the performance, depends on the characteristics of there queries.
Also important: you have a simple (single select, no joins) query on a small table (~30k rows), invoked many times (x millions). This is the perfect example of data which should be cached. So if it turns out, you NEED better performance, then cache this to memory. (But I think you should be fine the way it is now (again check if you have the appropriate indexes))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.