Usage of temporary tables in MySQL databases – Part 2

Usage of temporary tables in MySQL databases – Part 2

Hi, this is part 2 of my most recent article “Usage of temporary tables in MySQL databases“. If you haven’t read it, please do before you continue. In this specific article, I’ll give you a real example of temp table usage, and hopefully, you will see its advantage.

Let’s take part of Magento EAV system database structure as an good example. The tables I’m going to use are shown in the image below (and SQL for sample schema is at the bottom of the article):

We are going to create a simple SQL for Magento catalog importer (non functional on real Magento store) using temporary table.

The logic is simple:

  1. Import CSV into temp table
  2. Use some SQL, read that data from temporary table and import it on right place in database

To begin, we need SQL for the creation of our temporary table. So here it is:

CREATE TEMPORARY TABLE csv_import(
id INT,
entitiy_id INT,
attribute_id INT,
VALUE TEXT
)ENGINE MyISAM

After we create it we populate it with:

INSERT INTO csv_import VALUES (1,3,2,584), (2,2,2,112), (3,1,5,"value 1"), (4,4,5,"value 2");

(it must be in the same database session / connection).

Assumption is that we have “catalog_category_entity” table populated with some sample data as follows:

entitiy_id   attribute_id
 
1            5
 
2            2
 
3            2
 
4            5

And on the other hand, we have a CSV that we inserted into “csv_import” table as foollows:

id    entitiy_id        entitiy_type_id      VALUE
 
1     3                 2                    584
 
2     2                 2                    112
 
3     1                 5                    "value 1"
 
4     4                 5                    "value 2"

And now, the fun part: populating the “catalog_category_entity_int” and “catalog_category_entity_text” tables from our temp table (“csv_import”) With 2 simple queries.

INSERT INTO catalog_category_entity_int (value_id, attribute_id, VALUE)
SELECT NULL,csvi.attribute_id,csvi.VALUE FROM csv_import AS csvi WHERE csvi.attribute_id = 2;
 
INSERT INTO catalog_category_entity_text (value_id, attribute_id, VALUE)
SELECT NULL,csvi.attribute_id,csvi.VALUE FROM csv_import AS csvi WHERE csvi.attribute_id = 5;

And all we need to do it in a batch, is to create a script that would run contain the information which attribute_id is saved in which table.

And still, this is just a simple usage (temp tables aren’t necessary for this example), but I intentionally used this one to keep both simplicity and functional example in one relatively short article.

Feel free to comment!

You made it all the way down here so you must have enjoyed this post! You may also like:

Deep Dive into Validation Rules Danijel Vrgoc
, | 2

Deep Dive into Validation Rules

Moving the validation error message, this time globally Danijel Vrgoc
, | 0

Moving the validation error message, this time globally

How to generate SSH keys for Git authorization Hrvoje Ivancic
, | 21

How to generate SSH keys for Git authorization

1 comment

  1. It’s interesting and I’ll be interested to see in application the PHP script. Also I’d like to know what kind of pros you have to put in a table? Save time? Ressources? Balance loads on MySQL instead of PHP ressources?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.

Tell us about your project

Drop us a line. We'd love to know more about your project.