headers
George Gallen | 19 Jul 2012 17:55

[U2] MySQL query qestion....

This is specific to MySQL, but could apply to any SQL DB.

Is there a way to put the results of one query to be used by another, without having that second query run on each
Iteration of the sub query?

Example:

SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT UPDATE_PROCEDURE());

The function UPDATE_PROCEDURE() returns a timestamp, and then updates itself with a new timestamp.

My workaround is:

SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT GET_PROCEDURE());
SELECT UPDATE_PROCEDURE();

Here the GET_PROCEDURE() only retrieves a value, and doesn't update it.

BUT...if there were half a million records in the TABLE, I'd prefer not to run the GET procedure half a
million times
   To retrieve the same result which could add significant processing time (this would be run for each of the tables
   I'm mirroring on UV from MySQL .

George

_______________________________________________
U2-Users mailing list
U2-Users <at> listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
(Continue reading)

Permalink | Reply |
headers
Brian Leach | 19 Jul 2012 18:29
Picon

Re: [U2] MySQL query qestion....

George

It depends on the db type. In SQL Server, for example, you would create a
stored procedure that would capture the subquery result to a local variable
and use that.

e.g.

DECLARE  <at> lastUpdate DATETIME;
SELECT  <at> lastUpdate = some_expression ;
select * from table where last_update >  <at> lastUpdate

But I don't know with mySQL - it's a bit short on intellect - but I see it
has a SELECT .. INTO format for populating a variable. Might be worth a
shot.

Brian

-----Original Message-----
From: u2-users-bounces <at> listserver.u2ug.org
[mailto:u2-users-bounces <at> listserver.u2ug.org] On Behalf Of George Gallen
Sent: 19 July 2012 16:56
To: U2 Users
Subject: [U2] MySQL query qestion....

This is specific to MySQL, but could apply to any SQL DB.

Is there a way to put the results of one query to be used by another,
without having that second query run on each Iteration of the sub query?
(Continue reading)

Permalink | Reply |
headers
Wols Lists | 19 Jul 2012 18:47
Picon

Re: [U2] MySQL query qestion....

My SQL-fu isn't up to much, but my reaction was along the lines of

SELECT *, LAST_UPDATED=GET_PROCEDURE() FROM TABLE WHERE LAST_UPDATE >
LAST_UPDATED;

An optimiser may be able to optimise this for you, depending how
complicated GET_PROCEDURE() is. If it's just a select from yet another
table, the optimiser should spot it's constant and it'll fly. You might
be able to declare it constant, I don't know.

(And don't trust my example syntax! :-)

Cheers,
Wol

On 19/07/12 17:29, Brian Leach wrote:
> George
> 
> It depends on the db type. In SQL Server, for example, you would create a
> stored procedure that would capture the subquery result to a local variable
> and use that.
> 
> e.g.
> 
> DECLARE  <at> lastUpdate DATETIME;
> SELECT  <at> lastUpdate = some_expression ;
> select * from table where last_update >  <at> lastUpdate
> 
> But I don't know with mySQL - it's a bit short on intellect - but I see it
> has a SELECT .. INTO format for populating a variable. Might be worth a
(Continue reading)

Permalink | Reply |
headers
George Gallen | 19 Jul 2012 19:32

Re: [U2] MySQL query qestion....

Here's what I would up going with....

DELIMITER $$

CREATE PROCEDURE `GetTableUpdates`(p_key VARCHAR(45))
BEGIN
   DECLARE i_result timestamp;
   SELECT UpdateUTime(p_key) INTO i_result;
   SET  <at> t1 =CONCAT('SELECT * FROM ',p_key," WHERE LastUpdate > '",i_result,"'" ); 
   PREPARE stmt3 FROM  <at> t1; 
   EXECUTE stmt3; 
   DEALLOCATE PREPARE stmt3; 
END

And is called using  "CALL GetTableUpdates('tablename')"

George

-----Original Message-----
From: u2-users-bounces <at> listserver.u2ug.org [mailto:u2-users-bounces <at> listserver.u2ug.org] On
Behalf Of Wols Lists
Sent: Thursday, July 19, 2012 12:47 PM
To: u2-users <at> listserver.u2ug.org
Subject: Re: [U2] MySQL query qestion....

My SQL-fu isn't up to much, but my reaction was along the lines of

SELECT *, LAST_UPDATED=GET_PROCEDURE() FROM TABLE WHERE LAST_UPDATE >
LAST_UPDATED;
(Continue reading)

Permalink | Reply |
headers
Kevin King | 19 Jul 2012 19:06
Favicon

Re: [U2] MySQL query qestion....

I agree with Brian, a stored procedure or function seems to be the way to
go, where you get the value, store it in a variable, and then use the
variable in the query.

On Thu, Jul 19, 2012 at 10:29 AM, Brian Leach <brian <at> brianleach.co.uk>wrote:

> George
>
> It depends on the db type. In SQL Server, for example, you would create a
> stored procedure that would capture the subquery result to a local variable
> and use that.
>
> e.g.
>
> DECLARE  <at> lastUpdate DATETIME;
> SELECT  <at> lastUpdate = some_expression ;
> select * from table where last_update >  <at> lastUpdate
>
> But I don't know with mySQL - it's a bit short on intellect - but I see it
> has a SELECT .. INTO format for populating a variable. Might be worth a
> shot.
>
>
> Brian
>
> -----Original Message-----
> From: u2-users-bounces <at> listserver.u2ug.org
> [mailto:u2-users-bounces <at> listserver.u2ug.org] On Behalf Of George Gallen
> Sent: 19 July 2012 16:56
> To: U2 Users
(Continue reading)

Permalink | Reply |
headers
Brian Whitehorn | 20 Jul 2012 00:58

Re: [U2] MySQL query qestion....

Hi George,

An unconfirmed use per your query question:

SELECT * FROM
(
  SELECT UPDATE_PROCEDURE()
) AS `updateProcedure`,
(
SELECT * FROM theTablename
) AS theTable
WHERE theTable.`LAST_UPDATE` > updateProcedure.`lastUpdateDate` ;

-----Original Message-----
From: u2-users-bounces <at> listserver.u2ug.org [mailto:u2-users-bounces <at> listserver.u2ug.org] On
Behalf Of George Gallen
Sent: Friday, 20 July 2012 1:56 AM
To: U2 Users
Subject: [U2] MySQL query qestion....

This is specific to MySQL, but could apply to any SQL DB.

Is there a way to put the results of one query to be used by another, without having that second query run on
each Iteration of the sub query?

Example:

SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT UPDATE_PROCEDURE());

The function UPDATE_PROCEDURE() returns a timestamp, and then updates itself with a new timestamp.
(Continue reading)

Permalink | Reply |

Gmane