TEMP table usage
Moderator: General Moderators
TEMP table usage
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.
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
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
Yea that's kinda what I was thinking too. Essentially the loop goes as follows-
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.
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
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
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.
Also, I think you want VIEWs, not TEMPORARY TABLES.
And yes - avoid using TEMPORARY TABLES.
There are 10 types of people in this world, those who understand binary and those who don't
Re: TEMP table usage
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
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.
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
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.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.
Re: TEMP table usage
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.Weirdan wrote: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.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.
Re: TEMP table usage
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.califdon wrote:I'd be interested in learning more about the conditions under which that is the case.
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
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.