MySQL queue

[NLCJ]

Hello,

I've created a little script, that takes infromation from a CSV file and then imports it into the database. Sadly, this can be up to 5 query's per line. Some files are big (then I mean more than 4000 lines) and require full attention of the server, which makes all other sites slow. So I was wondering if it's possible to limit it to for example 5 or 10 query's per second? Like putting all the queries into a queue and then spread it over the time.

 

A CSV file with 200 lines takes about 20 seconds now, what I'm afraid of is that the user thinks that the site is stuck so he refreshes it, what resends it and then gives inserts it again. Ofcourse I've got a protection for that, but it definetly can make the server freeze.

 

Regards,

P.S.:

I'm sorry if it's placed in the wrong forum. I was not sure where to place it!

 

EDIT:

Okay, I just saw that I placed it in PHP Applications. It was meant to be in Coding Help...

[PFMaBiSmAd]

To get any help, you would need to post an example of the data and the code you are using now.

 

The first goal would be to make what you are currently doing as efficient as possible and 5 queries per line does not sound like an efficient design.

 

[NLCJ]

Thank you for your reply. This is the part of the code:

for($number = 0; $number <= $lines - 1; $number++) {
                  if($error = 1) {
                     break;
                  }else {
                     if($setinactive != 1) {
                        mysql_query("UPDATE `".$d_userinformation['username']."-".mysql_real_escape_string($_GET['type'])."s` SET status='inactive'");
                        $setinactive = 1;
                     }
                     $values = explode(",", $postedinfo[$number]);
                     $since = date("Y-m-d", strtotime($values[1]));
                     $lastclick = date("Y-m-d", strtotime($values[2]));
                     $differencedate = (strtotime($since) - strtotime(date("Y-m-d")))/(24*60*60);
                     if($differencedate == 0) {
                        $differencedate = 1;
                     }
                     $average = $values[3] / $differencedate * -1;
                     $d_checkexistence = mysql_query("SELECT * FROM `".mysql_real_escape_string($values[0])."` LIMIT 0,1");
                     if($d_checkexistence == false) {
                        mysql_query("INSERT INTO `".$d_userinformation['username']."-".mysql_real_escape_string($_GET['type'])."s` (name,since,importedtime,lastimport) VALUES ('".mysql_real_escape_string($values[0])."','".mysql_real_escape_string(date("Y-m-d"))."','".mysql_real_escape_string(date("H:i:s"))."','".mysql_real_escape_string(date("Y-m-d"))."')");
                        mysql_query("
                           CREATE TABLE  `".mysql_real_escape_string($values[0])."` (
                           `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
                           `since` DATE NOT NULL ,
                           `lastimport` DATE NOT NULL ,
                           `imported` DATE NOT NULL ,
                           `username` VARCHAR( 255 ) NOT NULL
                           ) ENGINE = MYISAM ;
                           ");
                        mysql_query("INSERT INTO `".mysql_real_escape_string($values[0])."` (since,lastimport,imported,username,status) VALUES ('".mysql_real_escape_string($since)."','".mysql_real_escape_string($lastimport)."','".mysql_real_escape_string(date("Y-m-d"))."','".mysql_real_escape_string($d_userinformation['username'])."','active')");
                     }else {
                        mysql_query("UPDATE `".$d_userinformation['username']."-".mysql_real_escape_string($_GET['type'])."s` SET since='".mysql_real_escape_string($since)."',lastclick='".mysql_real_escape_string($lastclick)."',clicks='".mysql_real_escape_string($values[3])."',average='".mysql_real_escape_string($average)."',lastimport='".mysql_real_escape_string(date("Y-m-d"))."',status='active' WHERE name='".mysql_real_escape_string($values[0])."'");
                        mysql_query("UPDATE `".mysql_real_escape_string($values[0])."` SET since='".mysql_real_escape_string($since)."', lastclick='".mysql_real_escape_string($lastclick)."', clicks='".mysql_real_escape_string($values[3])."', average='".mysql_real_escape_string($average)."', username='".mysql_real_escape_string($d_userinformation['username'])."' WHERE imported='".date("Y-m-d")."'");
                     }
                  }
               }

They all are different actions / tables, so it doesn't seem to get faster in my opinion.

 

MOD EDIT:

 . . . 

tags added.

[gizmola]

Why are you creating tables all the time?

[NLCJ]

Lets say that every line got a specific ID. If that ID doesn't exist, it creates a table for that ID. Then it will insert the rest of the information into that table.

Why did I do it this way?

I thought this would be less heavy for the server when requesting, because when all ID's are in the same table it would have more than 50.000 rows.

[PFMaBiSmAd]

LOL, no wonder it takes your code so long to execute. My first reply was incorrect, your first goal here would not be to make what you are currently doing as efficient as possible, your first goal would be to have an efficient database design. What you showed is not.

[PFMaBiSmAd]

You can probably make your existing code 2x faster by eliminating all the unnecessary and redundant mysql_real_escape_string() function calls.

 

Using mysql_real_escape_string() on values that your script produces, such as data('Y-m-d') or $average is not necessary because those values can never have characters in them that need escaping.

 

You are also using the same value several different times, such as $values[0] and $_GET['type']. You should escape them once and then just use the escaped value every place.

 

 

[NLCJ]

You can probably make your existing code 2x faster by eliminating all the unnecessary and redundant mysql_real_escape_string() function calls.

 

Using mysql_real_escape_string() on values that your script produces, such as data('Y-m-d') or $average is not necessary because those values can never have characters in them that need escaping.

 

You are also using the same value several different times, such as $values[0] and $_GET['type']. You should escape them once and then just use the escaped value every place.

Thank you, a while ago I read that this way was as fast as without mysql_real_escape_string. I will do that. Also I wasn't aware that $_GET['type']  slows it down. I will make it better, and see how it goes. Thanks!

 

And I created this database structure because I thought it was easier for the server. Apparently a table with more than 50.000 rows is faster?

[PFMaBiSmAd]

What is killing your code is the use of individual tables AND the select query to find if a table exists and waiting for the query to produce an error when the table does not exist.

 

You also have redundant data. $values[0] is apparently a name and $d_userinformation['username'] is apparently a username. You are creating a name and a username table for each member and you are storing many of the same data values in each table and you are storing the username in the name table and the name in the username table.

 

50K rows is nothing. I use 5Million rows for testing database applications.

 

Once you get a single row for each user in a COMMON user table, you can then do things like use an INSERT ... ON DUPLICATE KEY UPDATE ... query to either INSERT a new row or UPDATE an existing row for a user, all using just one query. After your $average = line of code, you can probably reduce everything you have shown into just a single query.

[NLCJ]

Okay, I will convert the current database to a (apparently better) database structure. Thanks for your suggestions!

[gizmola]

PFMaBiSmAd gave you great advice.  The one thing you need to keep in mind is that if you are going to be doing a query using a WHERE column that specifies a column, like in your example 'name' then you need to have an index on that column for performance reasons, or the query will need to tablescan through looking at every row for matches of name.

 

Also when you do inserts, you can batch them by repeating the values as he alluded to.  This is a lot more efficient for the database when compared to individual inserts. 

 

INSERT INTO foo (name) VALUES ('apple'), ('banana'), ('pear') etc

 

[NLCJ]

PFMaBiSmAd gave you great advice.  The one thing you need to keep in mind is that if you are going to be doing a query using a WHERE column that specifies a column, like in your example 'name' then you need to have an index on that column for performance reasons, or the query will need to tablescan through looking at every row for matches of name.

 

Also when you do inserts, you can batch them by repeating the values as he alluded to.  This is a lot more efficient for the database when compared to individual inserts. 

 

INSERT INTO foo (name) VALUES ('apple'), ('banana'), ('pear') etc

 

Currently I'm converting the database, and I already figured that INSERT INTO 'trick' out, and successfully modified that part (I have to admit it looks way better now).

 

But I don't understand the first part of your post, can you give an example for that?

 

By the way, I won't be here until about monday. Thanks for all your help though!

[gizmola]

I'm not sure your code will still require any selects, but you did have this:

 

"UPDATE `".$d_userinformation['username']."-".mysql_real_escape_string($_GET['type'])."s` SET since='".mysql_real_escape_string($since)."',lastclick='".mysql_real_escape_string($lastclick)."',clicks='".mysql_real_escape_string($values[3])."',average='".mysql_real_escape_string($average)."',lastimport='".mysql_real_escape_string(date("Y-m-d"))."',status='active' WHERE name='".mysql_real_escape_string($values[0])."'"

 

So in that case your WHERE name='some name' query, you want the "name" column of that table to have an index on it.  Indexes can be setup either using the KEY statement when you create the table or with a seperate CREATE INDEX statement.   

[NLCJ]

Okay, I've been away for a while but started yesterday again. I'm working on inserting with multiple values, but what if it already exists? Then it got to update it... So I was looking for 'ON DUPLICATE UPDATE', but have no clue... How would I do it if I have multiple values?

INSERT INTO foo (name,variable) VALUES ('apple','".$variable."'), ('banana','".$variable."'), ('pear','".$variable."') ON DUPLICATE KEY UPDATE variable='".$variable."'

Would this work / will it update the right row?

[gizmola]

Yes, you have the right syntax.  Should work fine as long as name is actually the primary key of the table.

[NLCJ]

Okay, but what if ID is the primary key? This is also auto_increment. How do I fix this? Is there another way besides setting name as primary key?

 

Thanks