Indexes for Temporary tables Concept

Creating a Temporary table:

create table emp like employees

 This command creates emp table with the structure  like employees table

create table emp1  select * from employees

This command creates emp1 table with the structure and data  like employees table

you can create the table and add the index afterwards:

CREATE TEMPORARY TABLE temp_table(employeeNumber int)

ALTER TABLE temp_table ADD FULLTEXT INDEX (employeeNumber)

INSERT INTO temp_table SELECT * FROM regular_table

But the index will be, again, updated on every insert.
Probably the most efficient way would be to create the temp table, insert all, build index afterwards:

CREATE TEMPORARY TABLE temp_table

ALTER TABLE temp_table ADD FULLTEXT INDEX (employeeNumber)

ALTER TABLE temp_table DISABLE KEYS

INSERT INTO temp_table SELECT * FROM regular_table

ALTER TABLE temp_table ENABLE KEYS

Creating a MySQL Index - New Table:

CREATE TABLE employee_records

(

               name VARCHAR(50),

               employeeID INT, INDEX (employeeID)

);

Creating a MySQL Index - Existing Table:

CREATE TABLE employee_records2 (name VARCHAR(50), employeeID INT);

CREATE INDEX id_index ON employee_records2(employeeID);

ALTER TABLE  employee_records2 ADD INDEX (employeeID);

Droping a MySQL Index - Existing Table:

ALTER TABLE employee_records2   DROP INDEX (employeeID);

 Creating a multi-column MySQL table index:

CREATE INDEX idx_employee_records2  ON employee_records2 (name, employeeID);

The MySQL SHOW INDEX command :

show index from node;

This command shows the indexes from the database table named node

Creating a MySQL Index - New Table

If you are creating a new MySQL table you can specify a column to index by using the INDEX term as we have below. We have created two fields: name and employeeID (index).

MySQL Code:

CREATE TABLE employee_records (
 name VARCHAR(50), 
 employeeID INT, INDEX (employeeID)
)

Creating a MySQL Index - Existing Table

You can also add an index to an older table that you think would benefit from some indexing. The syntax is very similar to creating an index in a new table. First, let's create the table.

MySQL Code:

CREATE TABLE employee_records2 (name VARCHAR(50), employeeID INT)

With our newly created table we are going to update the "employee_records2" table to include an index.

MySQL Code:

CREATE INDEX id_index ON employee_records2(employeeID)

We keep our existing employeeID field and create a new index id_index that is made up of employeeID data.

Creating Very Large Indexs and Sorting on Large Tables

If your dataset is anywhere between 1 million to  180 million then you have come to the right blog.  Normally a procedure like this will require atleast 1 day or more depending on dataset. But I will teach you how to get results in as quick as 3 hrs. You will be able to execute queries on indexes fully utilizing them without going through the painful process of creating them on the the entire table. Recently at work I had to deal with a dataset of 180 million  and this entry gives details of the work flow that I adopted.  I will not effectively sort the complete table but will tell you how to achieve the same effect in less than 2-3 hrs for 180 million rows. I tried well known methods to achieve indexing. I tried to use Alter table statement after 2 hrs stopped it. I tried to dump the data in hope of inserting it in an indexed table but after 1 hr stopped that as well. Then I explored Mysql functionality a bit to discover 3 features which can really reduce time, those were Memory tables, Merge tables and Partitioning(not necessary). Fortunately sorting using merge sort is a recursive job, so in the process of sorting 180 million rows I also had to learn how to sort 2 million faster than normal index creation. So the first thing that you have to do is  find out the order of rows that you have.
If you have less than 1 million rows then you are better off using mysql’s inbuilt commands

Alter table <tblname> create index (<column name>);

If you have more than than then read on…..
The concepts I will be using are as follows:

Memory Tables:

These tables are hash maps that mysql stores in memory. These tables are temporary (in the sense they disappear after mysql server restart)  and have limited rows. However they are lighting fast on index creation or sorting.

Partitioning:

Somebody in Mysql finally realized that to be fast database has to split the data across servers. Finally in version 5 they have introduced partitioning. If you have the luxery of more than 1 server you can use this. I did not have this luxury so will do indexing without it.

Merge tables:

So these are 1 server counterpart of Partitioning. If you want to split your dataset into multiple tables and run single query on all of them you can use Merge tables. They have their own share of problems, but for general functionality of insertion, deletion, searching and dumping, they work great. The only 2 major limitations one that they only work if base tables are MyISAM and searching on them is multiples of logn(see below if interested)

Irrelevant Mathematical jargon(feel free to skip)

So if you search  table on indexed columns then search takes Log(n) time (n = number of tuples). So imagine you have 100*n size of table and you create 100 sub tables of n each. Now searching on one table will take Log(n) time and total time is 100*log(n)=log(n to the power 100). If it were a single table it would have taken Log(100*n) =Log(100)+log(n). So in a single table it scale logarithmically but merge table scales exponentially.

I will explain the steps that I took below, you can customize them to your dataset.
There are several ways of creating index however mine will require you to have quite a powerful machine and a reliable power supply. Atleast 2gb of ram is required and more is better here. If you have around 2 million rows then skip to step 4.

Step 0

This step is about reconnaissance. It will determine the numerical data in the next steps. You need to know the approximate amount of space your row takes. This will determine the size of your memory table. You can determine the size of memory table by trial and error also but a numerical estimate will help.  In the steps below I will assume initial table as client_data and final table as client_data_sorted. All other tables are temp and generated on the spot.

Step 1: Increasing allowed size of Memory table to fit data

The first thing you need to do is extend the allowed memory limit of mysql. To sort faster we will need memory tables. Do so by adding the lines marked as red below under  [mysqld] in my.cnf file. Typically located in /etc/my.cnf or /etc/mysql/my.cnf depending on your distribution.

my.cnf file
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
..
max_heap_table_size=4096M
tmp_table_size=4096M

Adding these lines will ensure that mysql can now take 4GB space for storing memory tables.Feel free to increase this number if you have higher memory but ensure that you dont encounter swapping.

Step 2: Dump data in memory table for indexing

Now you need to create a  memory table with same structure as your final table.

create table client_data_memory like client_data_sorted;
alter table client_data_memory engine=Memory;

The second line alters the engine to memory. The only thing to keep in mind is that memory engine keeps all the data in memory hence if your mysql server restarts or machine restarts then all the data is lost. Ensure that you never rely on them as a reliable storage.
Figure out  how many rows your memory table can contain. In a typical scenario this would be order of 10 million. This value will change depending on your system’s memory and values set in step 1.  You can test it out by inserting data from source table to memory table. After the limit the process will interrupt in the middle giving error like table is full.

Insert into client_data_memory select * from client_data limit <test limit value>;
Ex:  Insert into client_data_memory select * from client_data limit 0,10000000;    # 10 million rows from 0th row

Step 4: Storing data from Memory table to MyISAM table

If you have order of 10 million rows then the process stops for you here. Just use an alter table command to create index on memory table(3-4 minutes) then insert the memory table data into client_data_sorted table. You just saved yourself  hours of pain. If you have more than 10 million then  skip this step.

Alter table client_data_memory add index (<column name>);
(or sort the table)
Insert into client_data_sorted select * from client_data_memory;

If you can store 10million rows in your memory table and total tables are around 40 million then you are better off iteratively repeating the above steps. Merely sort 10 million then insert, then truncate memory table , then insert next 10 million , sort then insert. The process will take exponentially more time every time but still will finish far faster than normal.  If you have more than 40 million then read on. A world of pain awaits you….

Step 5: Create Merge table for accessing data

The above process will not work iteratively for you if you have more than 50 million rows as while inserting you have to merge too. Suppose your memory can store 10 million and you have 180 million rows then create 18 temporary tables of engine type MyISAM.

create table data_temp1 like client_data_sorted;
alter table data_temp1 engine=MyISAM;

Now use the above technique to insert data 10 million a piece into each of these tables. So data_temp0 will contain 0-10 million then data_temp1 will have 10 miliion to 20 million and so forth.

truncate table client_data_memory;insert into client_data_memory select * from client_data limit
0,15000000;insert into data_temp0 select * from client_data_memory;
truncate table client_data_memory;insert into client_data_memory select * from client_data limit
10000000,15000000;insert into data_temp1 select * from client_data_memory;
…….

This will take quite a long time, for me it took 10 min for every 10 million so 180 min in total. Meanwhile consider some of the more worthwhile alternatives like  hive, its too late for it now but its worth a look. This is also useful.
Now create a merge table. A merge table is created on top of several MyISAM tables of same structure. Its important to have same structure and MyISAM tables. You can use this as  a single table however all the searching is performed on all tables and hence is slower than single table.

create table client_data_merge like client_data_sorted;
alter table client_data_merge engine=merge union=
(data_temp0,data_temp1,data_temp2,data_temp3,data_temp4,data_temp5,data_temp6,data_temp7,data_temp8,data_temp
9,data_temp10,data_temp11,data_temp12) INSERT_METHOD=LAST;

Step 5: Final Sorted table(if required)

For most temporary uses this merge table will work. You can use this table normally. Insert data into the table etc etc. However if the limitations of merge tables are not acceptable you have to create the final table  from this merge table. You can use merge tables to dump the data in a sorted fashion according to indexes or simply use

Insert into <tblname> Select * from <merge table> order by <index1>,<index2>…..

This way you get to Insert much faster as the data is already sorted and mysql simply has to insert the data and update the indexes. If this method helped.. do let me know in the comments.

The MySQL SHOW INDEX command

For a long time I couldn't remember how to show the indexes for a database table with MySQL, but I think it's finally sunk in. Just use the MySQL SHOW INDEX command, like this:

show index from node;

That example can be read as, "Show the indexes from the database table named node". It assumes that you have already selected a database table, so really, a full sequence of commands to show a MySQL database table index looks like this:

Syntax variations

As you may have noticed from that last example, you don't have to type show index; either of the following three commands will give you the same results:

show index from node;
show indexes from node;
show keys from node;

A MySQL database table index example

How do I create a MySQL database table index?

Here's an example of how to create an index on a MySQL database table. In this particular example my table uses the MySQL InnoDB database engine, but that doesn't matter too much -- it just happens to be a MySQL database engine that supports foreign key relationships.
To get started, here's the SQL definition of a MySQL table I've named ftp_files:

CREATE TABLE ftp_files (
  id int unsigned auto_increment not null,
  ftp_username varchar(32),
  ftp_command varchar(4),                     # STOR, DELE, RNFR, RNTO
  ftp_root_dir varchar(1024),                 # ex: /ftp/customer1/file1.pdf
  file_name varchar(1024) not null,
  file_size_bytes bigint unsigned not null,
  primary key (id)
) ENGINE=InnoDB;

As you can see, this database table describes the attributes of an FTP event, with all of the fields related to the concept of a file. (I've greatly simplified this from the actual table I use in production.)
A lot of people prefer to create their index in the CREATE TABLE statement, but I prefer to add my indexes after the CREATE TABLE statement, and I'll show my CREATE INDEX definition next.

How to create a multi-column MySQL table index

Getting to the index ... immediately after my table definition I add the following line of MySQL code to create an index on my ftp_files database table:

CREATE INDEX idx_ftp_files_command_and_dir
ON ftp_files(ftp_command, ftp_root_dir);
This creates an index over the two fields ftp_command and ftp_root_dir. This index will improve the performance of any queries run against these fields. In my case I have a bunch of "worker" programs running against this table that keep running a query that looks like this:

SELECT * FROM ftp_files
WHERE ftp_command='STOR'
AND ftp_root_dir='/ftp/customer1'
(The actual situation is a little more complicated than this, but I'm trying to keep this simple.)
Because I know this table is going to get very large, and because I know this query is going to be run thousands of times each day, and response time is important, I create this index to speed up these queries.
As a final note, I created this index with the name idx_ftp_files_command_and_dir, but you can create your own indexes with whatever naming pattern you prefer. I do suggest, however, making the names meaningful. You'll appreciate that later when you have a large number of indexes in your database.

How to create a single-column index

I've already shown how to create a multiple-column index on a MySQL database table. For completeness, I thought I should also show how to create a single-column index, which is actually slightly easier:

CREATE INDEX idx_ftp_files_username
ON ftp_files(ftp_username);

This index would be helpful if I was running queries against this field, like this:

SELECT * FROM ftp_files
WHERE UPPER(ftp_username)='FRED'