CODEWITHSUNDEEP
phpmysqlcron
Näbil Y
Näbil Y

Reputation: 1660

lots of queries - slow?

To begin with, I apologize if this has been asked already, I could not find anything at least.

Anyway, I'm going to run a cron task each 5 minutes. The script loads 79 external pages, whereas each page contain ~200 values I need to check in database (in total, say 15000 values). 100% of the values will be checked if they exist in database, and if they do (say 10% does) I will use an UPDATE query.

Both queries are very basic, no INNER etc.. It's the first time I use cron and I'm already assuming I will get the response "don't use cron for that" but my host doesn't allow daemons.

The query goes as:

SELECT `id`, `date` FROM `users` WHERE `name` = xxx

And if there was a match, it will use an UPDATE query (sometimes with additional values).

The question is, will this overload my mysql server? If yes, what are the suggested methods? I'm using PHP if that matters.

Upvotes: 0

Views: 127

Answers (2)

Kamil
Kamil

Reputation: 13941

About MySQL and lot of queries

If you have enough rights on this server - you may try to increase Query Cache.

You can do it in SQL or in mysql config file.

http://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html

 mysql> SET GLOBAL query_cache_size = 1000000;
 Query OK, 0 rows affected (0.04 sec)

 mysql> SHOW VARIABLES LIKE 'query_cache_size';
 +------------------+--------+
 | Variable_name    | Value  |
 +------------------+--------+
 | query_cache_size | 999424 |
 +------------------+--------+
 1 row in set (0.00 sec)

Task sheduler in MySQL

If your updates may work only on data stored in database (there are no PHP variables) - consider using EVENT in MySQL instead of running SQL scripts from PHP.

Upvotes: 0

Jonathan Kuhn
Jonathan Kuhn

Reputation: 15311

If you are just checking the same query over and over, there are a few options. Off the top of my head, you can use WHERE name IN ('xxx','yyy','zzz','aaa','bbb'...etc). Other than that, you could possibly do a file import into another table and probably run one query to do an insert/update.

Update:

//This is what I'm assuming your data looks like after loading/parsing all the pages.
//if not, it should be similar.
$data = array(
    'server 1'=>array('aaa','bbb','ccc'),
    'server 2'=>array('xxx','yyy','zzz'),
    'server 3'=>array('111','222', '333'));
//where the key is the name of the server and the value is an array of names.

//I suggest using a transaction for this.
mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");
//update online to 0 for all. This is why you need transactions. You will set online=1
//for all online below.
mysql_query("UPDATE `table` SET `online`=0");
foreach($data as $serverName=>$names){
    $sql = "UPDATE `table` SET `online`=1,`server`='{$serverName}' WHERE `name` IN ('".implode("','", $names)."')";
    $result = mysql_query($sql);
    //if the query failed, rollback all changes
    if(!$result){
        mysql_query("ROLLBACK");
        die("Mysql error with query: $sql");
    }
}
mysql_query("COMMIT");

Upvotes: 2

Related Questions