I have an suggestion regarding your second option.
If you need to keep the MyISAM tables with each unique my_column value separated from other MyISAM tables, you may want to look into the MERGE (Mrg_MyISAM) Storage Engine as an alternative to table partitioning.
This will allow your multiple MyISAM tables within the same database that have identical table structures and index layout to be mapped together in such a way that single query hits all MyISAM tables.
Suppose you have a table as follows:
CREATE TABLE tb1
(
my_column INT NOT NULL
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
PRIMARY KEY (my_column,id),
KEY name (my_column,name)
) ENGINE=MyISAM;
and you have 3 such tables (tb1, tb2, tb3) where
- my_column for tb1 = 1
- my_column for tb2 = 2
- my_column for tb3 = 3
You can merge them under a single table mapping as follows:
CREATE TABLE tbmerge LIKE tb1;
ALTER TABLE tbmerge ENGINE=Mrg_MyISAM UNION=(tb1,tb2,tb3);
To perform a search through all the tables, just use tbmerge. For example, suppose you want to see every name from tb2 that starts with 'Jack', you run this query:
SELECT name FROM tbmerge WHERE my_column=2 AND name LIKE 'Jack%';
Given the design of the table, you should always specify the value for my_column. In fact, for every index tb1 has, make sure my_column is always the first column. The reason? A query against tbmerge is always a query against tb1, tb2, tb3 (all underlying tables). Otherwise, this query
SELECT name FROM tbmerge WHERE name LIKE 'Jack%';
will experience horrible performance because it will perform table scans against all underlying tables. Please plan carefully the indexes you will be using, following that simple rule (using my_column as the first column of every index)
There is an additional benefit: You can INSERT into the underlying tables at your convenience, thus working with the
I discussed this using MERGE tables in an earlier post (Jan 4, 2012).
The answer depends a great deal on how well organized your data is and the query itself.
For example, look at the query you have in the question:
SELECT rank, COUNT(id) FROM tablename GROUP BY rank
The first thing I think about with this query is whether the table is properly indexed.
OBSERVATION #1
If tablename had no indexes, a full table scan would be required.
OBSERVATION #2
If tablename had an index on rank, you still get a full table scan because of the MySQL Query Optimizer ruling out the use of the index because of factors such as key distribution and the possibility of having to lookup each id for every rank during a full index scan.
OBSERVATION #3
If the table had a compound index of (rank,id), then you can a full index scan. In most cases, a full index scan that never references the table for non-indexed columns would be faster than a full index scan that does (See OBSERVATION #2)
OBSERVATION #4
If the query was written slightly different
SELECT rank, COUNT(1) FROM tablename GROUP BY rank
then an index on just the rank column would suffice and produce a full index scan.
CONCLUSION
In light of these observtions, it is definitely a thing of beauty to present to the MySQL Query Optimizer two things:
- a good query
- proper indexes for all tables in the query
In retrospect, it is also good to give the MySQL Query Optimizer as much of an advantage upfront as possible.
Best Answer
You need N
INSERT ... SELECT ...statements. A one-time tedious task.