List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 4 1999 8:50pm
Subject:Re: Sub-selects IN()
View as plain text  
At 3:32 PM -0500 6/4/99, David Wall wrote:
>> You can always reduce a sub-select to two queries , one
>> storing the results in a temporary table, the other
>> doing a join with the temporary table.
>> --
>> Sasha Pachev
>I understand this, I guess.  The problem is just that for all such
>subselects, I'll need to create, join, drop the tables.  I suppose the SQL
>engine is doing this anyway, but it's probably more solid than hand-crafted
>code, especially when it comes to creating temporary tables that must be
>uniquely named to ensure no two queries will bump into each other.
>Does MySql support building such temp-named tables easily?  For example, I'd
>hate to have scratch tables exist when programs crash or otherwise fail to
>get to the 'drop table' portion of the logic, and I'd like to ensure the
>table names are unique.

For this kind of stuff, I have a "tmp" database in which I create all my
temporary tables. (This makes use of MySQL's ability to allow queries to
access multiple databases.)  Also, you can make the tmp db privileges
permissive, so that "ordinary" users can have CREATE and DROP privileges --
which is not necessarily something you want to grant in your "real" database!

This also allows handling of problems due to servers crashing or scripts
terminating prematurely before they remove the temp table: just run a cron
job to
look through the tmp database directory every so often and drop tables that are
more than a certain number of days old.

Paul DuBois, paul@stripped
Northern League Chronicles:
Sub-selects IN()David Wall4 Jun
  • Re: Sub-selects IN()Sasha Pachev4 Jun
    • Re: Sub-selects IN()Paul DuBois5 Jun
  • Re: Sub-selects IN()David Wall5 Jun
    • Re: Sub-selects IN()Paul DuBois5 Jun