Hi! My recent project had some specific requirements regarding the work with database. Since DB server had some limitations (regarding number of queries), and fairly big number of queries that needed to be executed I created a solution that included temp tables. And now, I’d like to explain how to use them and why.
Suppose that in your application, you need to execute a calculation on a set of tables. The usual approach is to create a query that defines the joins between multiple tables, and selects the data from the joined tables. The problem with that approach is that every time you call that the query, the tables have to be rejoined in order to create the result. And that is usually fairly resource hungry. Instead, you can get around the problem by putting the results into temporary table, so the values are there while the database connection lasts.
And to conclude this short introduction, you will see the full benefit of this approach only if you have (over)complicated query to execute. If you are writing a simple CRUD (Create Read Update Delete) application, there’s no need for temp tables.
An example of reasonable usage would be batch import of data into Magento EAV system, which is fairly complicated. A fast way of import itself would be to save import file (CSV) into a temporary table, and work with different “SELECT” queries to populate the EAV.
So, here’s how it’s created:
CREATE TEMPORARY TABLE tmp_tbl_name (
id INT,
VALUE VARCHAR
) ENGINE MyISAM;And if you have other table structured like:
CREATE TABLE tbl_name (
id INT,
name VARCHAR,
description VARCHAR,
VALUE VARCHAR,
) ENGINE MyISAM;But you just wish to use value from that table for some reason you can just write:
INSERT INTO tmp_tbl_name
SELECT VALUE FROM tbl_name
WHERE id > 1;The intentionally put a “WHERE” clause in this last SQL snippet. That’s because I wanted to show you that you can use standard “SELECT” for inserting the data. Also, you virtually have no limitations on that part because you can use multiple joins on multiple temp/regular tables etc.
I’ll look into writing a bit more complex example in near future. I hope you learned something!
Until next time, bye.
