Is it bad to create many mysql temporary tables simultaneously?

I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better (extremely more efficient and faster than PHP arrays; obviously due to their mechanism).

My tentative solution is to form a temporary table upon each request (i.e. connection) to import and process the data. However, I am not sure if there is a drawback for creating many temporary tables simultaneously?

Can this make problem for the mysql server? Or I can use it as an alternative to PHP arrays in numerous simultaneous requests?

You want to prevent making temp tables as much as possible : Prevent copying to temp table (sql)

They do not work well in MySQL Replication : How are binlogs updated for MySQL temporary tables?

Since temp tables are a fact of life in the DB world, you may have to make some unorthodox changes to accommodate their existence.

Normally, mysqld has the habit of placing tmp tables in /tmp or wherever tmpdir is configured. That's usually on some poor unsuspecting disk.

An interesting alternative would be to setup a RAM disk and reconfigure tmpdir to use it

STEP 01 : Create a Mount Point for a RAM Disk

mkdir /var/tmpfs

STEP 02 : Add the RAM disk to /etc/fstab (16GB)

echo "none   /var/tmpfs              tmpfs   defaults,size=16g        1 2" >> /etc/fstab

STEP 03 : Add this line to /etc/my.cnf

[mysqld]
tmpdir=/var/tmpfs

STEP 04 : Enable the RAM Disk

You can do one of the following:

• Just reboot the DB Server

• mount -t tmpfs -o size=16g none /var/tmpfs

Before you do this, make sure you have enough RAM

Give it a Try !!!

StackExchange Database Administrators Q#29636, answer score: 6