Joining an Update and a Select Statment in to one query

[Markbad311]

I am concerned with the time it may take to UPDATE and also SELECT a field in the same table. Is there a way to both select and Update a field in one fowl swoop?


Example Querys:

<?

$sql1 = "SELECT id
FROM `users`
WHERE `u_name` = '$Auth_User' AND `auth_id` = '$Auth_ID'";
$sql2 = "UPDATE `users`
SET
`auth` = 0,
`auth_id` = $auth_id
WHERE `e_mail` = $_POST[e_mail]'";
$result = mysql_query($sql1)
or die ('Line 72 Sql'. mysql_error());
$result2 = mysql_query($sql2)
or die ('Line 74 Sql'. mysql_error());
?>

[chazzy]

you could if you used stored procedures.

[Markbad311]

can you point me to a a good resource for using stored procedures?

[chazzy]

it all depends on what dbms you use. each system has its own language (slightly different). you should start off by consulting the vendor websites (mysql, oracle, ms, etc) first.

[matt.chatterley]

Note though, that this won't decrease the query time vastly - you'll still be running a SELECT and UPDATE, just they'll be batched together within the procedure (which is neater, and a bit more efficient on some systems, due to stored/cached procedure plans).

The best way to make sure the queries above are fast is to ensure good indexing - if the queries above describe most of the activity on this table, and assuming none of these columns is a primary key, you probably want two indexes, one on: u_name, auth_id and the other on e_mail - both nonclustered (in MSSQL terms).

This sort of strategy is called "covering indexes", where you create an index covering the core columns used by specific queries in order to accelerate them - however it can lead to the creation of many indexes on table X, and hence, slow performancing from INSERT or DELETE commands.

Cheers,

Matt

[chazzy]

actually using a stored procedure will cut down on overall processing time, as the amount of data sent through the network over TCP/IP is cut down significantly.

also since you invoke them like a function, it feels more native to a developer, rather than the semantics of a select statement.

[Markbad311]

I am running MySql 4.1.21-standard, and PHP 4.4.2.

Chazzy by function do you mean a function on the application level or DB?

Matt the only index which is Auto Increment Primary Key on that table is `id`.

On a semi-related topic is it possible to associate two table's primary keys together to speed up query time? I am attempting to "Clean House" persay on my coding I did in earlier stages of programming with PHP and MySql. (let me tell you I found some holes).

[chazzy]

you can use foreign keys to associate two tables together.

based on your db and version, you cannot use a stored procedure.

i say that they're more native to the developer because you call them like this

statement s = "BOB(param1,param2)";
which is a lot more like
function bob(param1,param2){}
than
SELECT param1,param2 FROM table WHERE...

[mattyblah]

you can also group the queries together, i believe you need to seperate them by ";"
so you could have something like:

$sql = "update table set field=1 where id=2;select * from table where id=2";

When you execute the query you will get back the results from the select.

[Markbad311]

thanks everyone. I appreciate all the reply's and you helped me greatly!