Page 1 of 1

TEMP table usage

Posted: Thu Feb 25, 2010 11:41 am
by Phix
I'm working with another developers databases, and I gotta say it's a little complex. It's a contacts database, and many of the values are correlated with parent_id's in other tables, which in turn point to another, etc.

Instead of getting the perfect JOIN or SUBSELECT to get everything out, I'm thinking that temporary tables would be the way to go. I'm just curious as to how often they're used, or if they create too much overhead and a more complex single query is preferrable.

It's SQLite, if that matters.

Re: TEMP table usage

Posted: Thu Feb 25, 2010 12:18 pm
by califdon
Others here have more knowledge and experience with this than I do, but as a fairly experienced developer, I would advise you to avoid temp tables as much as you can, under most circumstances. I think, even with SQLite (although I have almost no experience with it), the query engine will probably be more efficient, and surely consume less resources than temp tables. That's just one opinion and I'm sure others will contribute more informed opinions.

Re: TEMP table usage

Posted: Thu Feb 25, 2010 12:41 pm
by Phix
Yea that's kinda what I was thinking too. Essentially the loop goes as follows-

Code: Select all

 
connect to db
grab people info
   foreach person
       $t1 = select data thats "easily" accessible
       create temp table
       select data not so easily accessible (~4 sep queries)
            place into temp table
       $t2 = extract as rows
       populate XML
       delete temp table
   end loop
end person
 
 
For about an average of 120 times. The TEMP table has maybe 3 columns.

It's just that I've been beating myself senseless over getting some of the data out and TEMP tables would make life a lot easier. It's a personal project I was just curious to hear what the seasoned folks around here think about temporary tables, even small ones... a couple hundred times.

Re: TEMP table usage

Posted: Thu Feb 25, 2010 2:17 pm
by VladSun
I'd "blindly" say that your problem is either your DB design, either your queries... Post your DB design and queries.
Also, I think you want VIEWs, not TEMPORARY TABLES.

And yes - avoid using TEMPORARY TABLES.

Re: TEMP table usage

Posted: Thu Feb 25, 2010 2:21 pm
by califdon
Vladsun makes an important point. It has been my experience, also, that when someone gets all tied up in complex queries, it's very often that their data structure doesn't conform to relational database principles to begin with. Not always, but in most cases.

Re: TEMP table usage

Posted: Fri Feb 26, 2010 10:28 am
by Kurby
A very helpful thing when jumping into someone else's database would be to reverse engineer it into a diagram. Depending on the table engines and the foreign key setup you can get a nice picture of how things are related. Pretty sure Visio can reverse engineer (just not sure with what databases) and MySQL Workbench can do it with MySQL.

I would agree with VladSun, you probably want to construct some views to automate some of the more complicated multi-table joins.

Re: TEMP table usage

Posted: Fri Feb 26, 2010 1:10 pm
by Weirdan
califdon wrote:I think, even with SQLite (although I have almost no experience with it), the query engine will probably be more efficient, and surely consume less resources than temp tables.
On the other hand there are cases when temp table + some 'create key's on that table yields astonishingly better performance than sql optimizer is able to offer. At least if we're talking about MySQL.

Re: TEMP table usage

Posted: Fri Feb 26, 2010 6:18 pm
by califdon
Weirdan wrote:
califdon wrote:I think, even with SQLite (although I have almost no experience with it), the query engine will probably be more efficient, and surely consume less resources than temp tables.
On the other hand there are cases when temp table + some 'create key's on that table yields astonishingly better performance than sql optimizer is able to offer. At least if we're talking about MySQL.
I'm surprised to learn that, but I'm pretty sure you have a deeper knowledge of MySQL internals than I do. I'd be interested in learning more about the conditions under which that is the case.

Re: TEMP table usage

Posted: Sat Feb 27, 2010 6:56 pm
by Weirdan
califdon wrote:I'd be interested in learning more about the conditions under which that is the case.
Basically it's more efficient to use temp table explicitly when MySQL would have to use it implicitly (showing up as 'using temporary table' in explain output), especially if this temp table is later used in some joins. Then you can create the same temp table explicitly, add some indexes to it and do the rest of the query.
Consider this example:
You run an ecommerce website where you sell something. Purchases are tracked via orders table:
[sql] CREATE TABLE orders (  id int AUTO_INCREMENT PRIMARY KEY,  timepoint timestamp NOT NULL DEFAULT current_timestamp,  amount decimal(5,2) UNSIGNED NOT NULL,  customer_id int(11) UNSIGNED NOT NULL,  -- .....); [/sql]
and you want to know total sales figures on public holidays for particular customer. Since holidays are arbitrary defined by government (at least they are where I live), you store them into another table:
[sql] CREATE TABLE holidays (   id int AUTO_INCREMENT PRIMARY KEY,   date date NOT NULL,   name varchar(50) NOT NULL,   -- ....); [/sql]
Now you can use two queries to get the info you want:
[sql] SELECT holidays.day, count(orders.id), sum(orders.amount)FROM holidays INNER JOIN orders ON date(orders.timePoint) = holidays.day-- or-- from holidays inner join orders on orders.timePoint between cast(holidays.day as datetime) and cast(holidays.day + interval 1 day as datetime)WHERE customer_id=$customer_idGROUP BY holidays.day [/sql]
Neither would use indexes for join. But if you first queried the orders table, added index over resulting date column and then joined temp table to holidays table - then they would be used.
NB: the example is completely made up. I could be wrong and indexes would be used here even without temp table - but the principle still applies to other cases.

Another example would be some infrequent maintenance / data analysis tasks, where adding indexes to the original table would be performance / disk space waste.

Re: TEMP table usage

Posted: Sat Feb 27, 2010 10:06 pm
by califdon
Thanks, Weirdan! I'm going to give that a lot more thought. It's an area I don't often get into, but I really should, so I should know more than I do about indexing and how MySQL optimizes queries.