Nested Queries Optimization

[Matrixkid]

Hi there,

 

Im looking at optimizing some queries I have going simply because they take way too much time the first time I run them, and I was looking to drop the query times.

 

I have a table that I need to constantly update, every 2 hours the cron runs, with information from another table.

 

I'll layout the tables then explain what I am doing.

 

Table: tblFirst

ID | firstname

1      JOE

2      BRETT

3      BOB

4      MIKE

 

Table: tblInput

ID    firstname  Date(timestamp)  FirstnameID(default of 0)

1        MIKE    0000 0001              4

2        BOB      0001 0002              3

3        JOE      0001  0003            1

4        BRETT      0004  0002            2

5        CRAIG      0009 0001              0

6        CRAIG      2001 2323            0

 

So, this is what I am want: I need to take all the firstnames from "tblInput" and throw them into "tblFirst" if they do not exist already.

 

Secondly, I need to go back into "tblInput" once I have updated "tblFirst" and update the FirstnameID field with the matching ID from "tblFirst"

 

Here is what I am using right now:

$table = "tblInput"; 
$query = mysql_query("SELECT ltrim(firstname) FROM $table WHERE FirstNameID = '0' group by ltrim(firstname) ORDER BY ltrim(firstname) ASC ");

$num_rows = mysql_num_rows($query);

if ($num_rows > 0) {
	while($row = mysql_fetch_array($query)) {

		$fname = strtoupper($row['ltrim(firstname)']);
		$query2 = mysql_query("INSERT INTO tblFirst (firstname) SELECT '$fname' FROM DUAL WHERE NOT EXISTS(SELECT firstname FROM tblFirst WHERE firstname = '$fname' LIMIT 1)") or die('Invalid query: ' . mysql_error());

	}


$query = mysql_query("SELECT ltrim(firstname) FROM $table WHERE FirstNameID = '0' group by ltrim(firstname) ORDER BY ltrim(firstname) ASC");
	while($row = mysql_fetch_array($query)) {

		$fname = strtoupper($row['ltrim(firstname)']);
		$query4 = mysql_query("SELECT id,firstname FROM tblFirst WHERE firstname = '$fname'");

		while($row = mysql_fetch_array($query4)) {
			$fname = ltrim($row['firstname']);
			$fnameid =  $row['id'];
			$query5 = mysql_query("UPDATE $table SET FirstNameID = '$fnameid' WHERE ltrim(firstname) = '$fname' AND FirstNameID = '0'");
		}

	}

}

 

The main issue I am having is the first time I run this on a table. It takes upwards of 200 seconds for a table of 30,000 entries. Once the table is populated with the IDs, it can make use of the ID = '0' and so then it only takes a matter of seconds to update it each time.

 

It works. But im not happy with the initial run, so I thought I would ask to see if anyone can point me in a direction to improve it.

Oh, the ltrim is on there because sometimes a whitespace appears because some of the tables have a leading blank space.

 

thanks!

 

 

[fenway]

you should  be able to write multi-table versions of both the insert and the updat.

[Matrixkid]

you should  be able to write multi-table versions of both the insert and the updat.

 

Can you further elaborate on that?

 

Thanks!

[fenway]

Well, in the first set of queries, you're just inserting the results of a left join.. is null -- so that should be easy enough.

 

In the second set, it's essentially a 3 table join.