temporary/memory tables in mysql
by Hamid Reza Fahimi Madjd @ Jan 21, 2008
As you know memory tables (temporary tables) are a kind of tables which store data in memory and respond to your query very fast.
I've experienced temporary tables in Oracle and SQL Server by myself but I had no practical experience about that in mysql till yesterday!
Yesterday, I had a query that was very slow and took 4-5 hours for running! So I started to search about temporary tables in mysql and found how to use them.
After I used memory table in my queries, it took only 9 minutes !!! It was unbelievable.
So I decied to post a new mini article here and share result of my research with you.
There is a very simple command for creating a memory table :
create table tbl1(fld varchar(64)) engine=memory;
This commands will create a table in memory for us but it's empty. Now, how we can fill it ? We can use insert command for filling this table or fill it on creation time:
create table my_table engine=memory select ip,country from ip_location;
As you see it's like a piece of cake ;)
Now we have a table in memory which help to decrease our query run time. if you get
Error Code : 1114 The table 'my_table' is full
error message during creation memory table, you have some solutions to solve the problem:
1. increase max_heap_table_size system variables.
It's a system variables that force maximum size on memory tables
2. limit rows by max_rows table option. It's a table option in the create table statement to keep the table from becoming too large
3. decrease length of fields type, for example if you have a field as varchar(100) change it to varchar(32)
After creation, the table will be accessible for all sessions till server doesn't restart or shut down, after that you have your memory table without any rows.
You can read a complete document about memory tables here.
The solution solved my problems, I wish you enjoyed it :)
- › how to use phing to build php projects
- › slice/paging large contents using php
- › how to log methods call in php ?
- › sql IN logical operation for java
- › backup from mysql database's routines
- › how to deploy war file into web root ?
- › how to get all oracle components version ?
- › temporary/memory tables in mysql
- › Set JFreeChart data from database
- › How to search and sort primitive arrays in Java ?