Large Comparison - MySQL AND PHP

Question

I've developed a user rating system that takes analyzes a users and saves their information with a score in a db.

I'm getting close to 1 Million users rated and stored.

I'm having issues with taking a certain set of users from the table (score < 50) and then comparing their ids against another set of ids without the whole thing crashing down.

The result of the (score < 50) query is around 65k rows and the comparison is against probably 1,000 user ids so the whole thing is running 65k * 1,000.

Is my bottleneck at the db? Or is it at the comparison of ids? Is there a better way to split this up?

Query -> "select username, userscore from users where userscore < 50"

then

Foreach compares values

Answer 1

seems easy enough to answer if it is the db or not. Just prior to your query, do an fopen of a log file in /tmp. Then fwrite the results of a microtime() into the file. Just after your query, fwrite the results of another microtime(). Run your script once. you will be able to see the following:

1) are you even getting to the pre-query spot

2) Is the script failing in the middle of the query

3) how long does the query take if it doesn't crash the script

Answer 2

Since you haven't provided any table or index information, here's what I'm going to suggest.

• Make sure there's an index on userscore. If you have more than a million rows in your table and you're doing a query with "WHERE userscore > 50", that column needs an index.
• Make sure your query is using that index. Run your query manually with EXPLAIN at the front, ie. EXPLAIN SELECT username, userscore from users where userscore < 50. Optimize the results.
• You haven't mentioned how you're doing the id comparison, so I'll assume it's in a loop that checks each one against the array. You might be better off putting all 1000 ids into the query and limiting your SELECT query to users with score < 50 AND with their id in that set.

If you post more information about your tables, indexes, and comparisons, I can probably be more specific.