optimizing multiple table inserts

[Anon]

I have a multiple table database accepting input from a form. The basic code works OK, but I'm wondering if there is a better/faster way to do it.

Currently, I do a check if the input already exists, insert it if it doesn't, or use the existing id if it does. I do this select and insert step individually for each table.

Such as:
// see if city is already in table, add if not

$cityresult = mysql_query("SELECT cityId, city FROM city WHERE city =
'$city'") or die("city select failed");

if (mysql_num_rows($cityresult)==0) {
//empty result set, so enter city data
$sql5 = mysql_query("INSERT INTO city (city) VALUES ('$city')") or
die("city insert error");
$cityId = mysql_insert_id();
if ($sql5) {
echo("city insert worked\n");
}
}
else {
// city already in table
while ($cityN = mysql_fetch_array($cityresult)) {
$cityId = $cityN["cityId"];
$city = $cityN["city"];
}
}

This works fine, but seems to take FOREVER. (There are 11 tables to check).
IS there a better way to do this?

[Anon]

You say this takes ages (11 tables) but you have only told us about 1 table (city). The other tables aren't mentioned in the code so will not be slowing down performance, unless you have modified the code above to make it simplier for us to read. If so, email me the full listing.

One of the ways to speed this up is to modify it to this:

$cityresult = mysql_query("SELECT count(*)FROM city WHERE city =
'$city'") or die("city select failed");
$row = mysql_fetch_array($cityresult);

if (!$row[0]) {
//empty result set, so enter city data
$sql5 = mysql_query("INSERT INTO city (city) VALUES ('$city')") or
die("city insert error");
echo("city insert worked\n");
}
else{

$cityresult = mysql_query("SELECT cityId, city FROM city WHERE city =
'$city'") or die("city select failed");

while ($cityN = mysql_fetch_array($cityresult)) {
$cityId = $cityN["cityId"];
$city = $cityN["city"];

}

[Anon]

Yes, I omitted the other tables for clarity. The code sections for them do exactly the same operation as above for each table.

I'll try this, but I don't understand why it will be faster. You've suggested another select call, I thought I should minimize the number of calls to the database? Is the speed gained by eliminating the mysql_num_rows & mysql_insert_id() calls?

Thanks,
trying to understand a new language...

[Anon]

The reason it is faster is because the first thing you want to know is does this city exist in the database.

My method uses the incredibly quick count function, whereas your method selects basically ALL the data in the table, and in large tables that is what is taking the time.

See:

cityresult = mysql_query("SELECT cityId, city FROM city WHERE city =
'$city'") or die("city select failed");

is selecting data from all the entries in the table. Count(*) just works out how many entries exist and thus doesn't store any data. It's much quicker. It's optimised for doing calls like this. Also, another way to speed this up is to use the LIMIT.

SELECT Count(*) FROM city LIMIT 1

which will stop as soon as it finds 1 entry, but as each entry is unique (I presume) as soon as its found one, there will be no more to find.

The LIMIT command doesn't work on M$ SQL 7.

[Anon]

My method uses the incredibly quick count function, whereas your method
selects basically ALL the data in the table,
...
See:

cityresult = mysql_query("SELECT cityId, city FROM city WHERE city =
'$city'") or die("city select failed");

is selecting data from all the entries in the table.

The heck it is--it's selecting what could be a quite small subset, and if
(as should be) there is an index defined on city, and if the dbms has
even a barely functional query optimizer, this query should be quite fast.
Mind you, I'm not sure what should happen if their are multiple cities
with the same name--perhaps he hasn't even handled that yet. :-)

[Anon]

Its still selecting data that isn't needed.

He isn't interested in the data, he is interested in whether a city exists or not.

Count will be much faster, try it and then come back and argue.

[Anon]

Sam, calm down.
Kirk is right, a query with a where statement only returns the data that matches the where statement. The rest is left alone.
And unless your data is huge, I doubt the difference between a count and a fetch will be noticable for just one query.

[Anon]

Let me add some clarification:

The table structure (i'll omit some tables for clarity, but the structure follows the same format) is:

Table: shows
showId, showName, showDesc

Table: cityLookup
relshowId, relcityId

Table: city
cityId, city

I ended up with this structure by following a tutorial at www.webmasterbase.com/article.php?aid=228&pid=262

This allows me to store information about a show, and what city its in, without having duplicate names in the city table. There are additional tables (and their corresponding lookup tables) for the other information i'm trying to store.

Thank you everyone for the advice.

[Anon]

Can you show the rest of the tables, as that cityLookup looks pointless.

[Anon]

I'm not trying to be a narc 🙂 but honestly it is quicker.

Do an experiment with 20,000 rows and you'll see the difference big style.

[Anon]

Maybe it'll help if I fill in some data.

Table: shows
showId, showName, showDesc
1, stones tour, big wing dig tour
2, who, live at leeds
3, rush, permanent waves blah blah

Table: cityLookup
relshowId, relcityId
1, 1
2, 2
3, 1

Table: city
cityId, city
1, boston
2, leeds

The first show, the stones tour, is a big wing dig in the city of Boston. The second show is the Who playing at Leeds. The third show, Rush, also appears at Boston. I'm trying to minimize the number of entries in the different tables, while still associating a city with a show.

[Anon]

cough ok, I like experiments, expecially when they prove me right! :-)

$db_connection_id=mysql_connect("db","user","pw");
// first query to get the database's attention
$a=microtime();
mysql_query("select id from table where id=45000",$db_connection_id);
mysql_query("select count(*) from table where id=45000",$db_connection_id);
$b=microtime();

$aa=split(" ",$a);
$bb=split(" ",$b);
echo (($bb[1]+$bb[0])-($aa[1]+$aa[0]));
echo "\n";

$a=microtime();
for ($t=0;$t<1000;$t++)
{
mysql_query("select count(*) from table where id=45000",$db_connection_id);
}
$b=microtime();

$aa=split(" ",$a);
$bb=split(" ",$b);
echo "count: ".(($bb[1]+$bb[0])-($aa[1]+$aa[0]));
echo "\n";
$a=microtime();
for ($t=0;$t<1000;$t++)
{
mysql_query("select id from table where id=45000",$db_connection_id);
};
$b=microtime();

$aa=split(" ",$a);
$bb=split(" ",$b);
echo "select:".(($bb[1]+$bb[0])-($aa[1]+$aa[0]));
echo "\n";

and the outcome:
count: 0.31748104095459
select:0.30682599544525

so in my case, using count is actually slower than selecting the data.

So poo to you with knobs on :-)

[Anon]

Looks like a nicely normalised setup to me.

[Anon]

Since when has Boston been a city? I'm in Sheffield BTW.

Ok, define your tables like this

table: shows
ShowID Int(10) PRIMARY KEY AUTO_INCREMENT, BandID Int(10),
ShowDesc Text,
CityID INT(10)

table: bands
BandID Int(10) PRIMARY KEY AUTO_INCREMENT, BandName Varchar(20) or whatever you want

table: citys
CityID Int(10) PRIMARY KEY AUTO_INCREMENT,
CityName

You could scrap the bands table if you dont have much duplication in bands names, but the normalised table is above.

[Anon]

I've done an experiment too and I'm getting count on top 7/10 times. It's close though.

<?php

$link = mysql_connect ("", "", "") or die ("Could not connect");
mysql_select_db ("") or die ("could not connect to db");

$mtime = microtime();
$mtime1 = explode(" ",$mtime);
$mtime1 = $mtime1[1] + $mtime1[0];

for ($s=0;$s<100;$s++)
{
mysql_query("SELECT InvoiceName FROM customers ");
};

$mtime = microtime();
$mtime2 = explode(" ",$mtime);
$mtime2 = $mtime2[1] + $mtime2[0];
$mtime = $mtime2 - $mtime1;

echo "Standard Select took $mtime <br><br>";

$mtime = microtime();
$mtime1 = explode(" ",$mtime);
$mtime1 = $mtime1[1] + $mtime1[0];

for ($s=0;$s<100;$s++)
{
mysql_query("SELECT Count(*) FROM customers ");
};

$mtime = microtime();
$mtime2 = explode(" ",$mtime);
$mtime2 = $mtime2[1] + $mtime2[0];
$mtime = $mtime2 - $mtime1;

echo "Count Select took $mtime <br><br>";

?>

This is on a real life database.

I worked out why I thought count was much much faster. Count is optimised for select's where there is no where clause. If you see the above example here
www.theoverclockingstore.co.uk/forums/test.php I think I can be forgiven for talking crap 🙂

[Anon]

Getting back to the point:

You code for the cities seems ok, so you need to find out where the bottleneck is.
Make your script do

echo microtime();

at key points, say right before you connect to the database and right after the connection is make,
and right before and after you execute a query.

This will print the current time (with microseconds).
Using this informcation you can track how long each of your statements take.
It could be that you are complaining about a slow query when actually your database connection takes forever to be created.

[Anon]

That's ok, it's X-mas time!