1 table, but two tables. Something like disk partitioning for mysql tables

Is there a way to have one table that is spli开发者_运维问答t in two, or partitioned like a hard drive, so that I can call

SELECT * FROM email validated

or

SELECT * FROM email pending

and get two different results. Both results not containing the other's rows.

is this something. I feel like I read about mysql partitioning somewhere, a long time ago, and I was wondering if that is what this is. If not, is this possible.

MySQL supports a virtual table known as a VIEW. A VIEW is effectively a stored MySQL query that can be queried as though it were a real table.

Using the example you provide, you would create a base table called email, as follows:

CREATE TABLE `email` (
            `email` VARCHAR(64) NOT NULL,
            `validated` CHAR(1) NOT NULL DEFAULT '0',
            PRIMARY KEY (`email`))
            ENGINE = MyISAM;

and then two virtual tables (VIEWS), as follows:

CREATE VIEW `email_validated` AS SELECT * FROM `email` WHERE `validated`='1';
CREATE VIEW `email_pending` AS SELECT * FROM `email` WHERE `validated`='0';

You can then query both of the views as though they were actual tables.

Recognize, however, that using views contains a performance penalty in that the entire view is queried (the entire select statement for the view is executed) whenever the view is referenced. On an example as trivial as this, it won't be a big deal provided the 'validated' field is indexed in the base table. On a more complicated view, however, it may not make sense to load the entire view virtual table into memory when only trying to retrieve a few rows.

Other database engines have a structure called a Materialized View, which is the same thing as a MySQL view, excepting that the materialized view exists as a realized table updated at some frequency or trigger. Any operation that can be done to a real table can be done to a materialized table, including changing indexes or even storage engines. It is completely reasonable to have a transaction history using an Archive storage engine while maintaining a roll-up summary table materialized view using a Memory storage engine. Although MySQL does not natively support Materialized Views, there are tricks to mimic the behavior of Materialized Views.

Well, even if that was possible using partitioning, it would be wrong, as the user eventually will validate the email, and you will have to move it to another partition, which is expensive operation.

You have to add column in the table, to store the value. Then, you can partition on that column, so physically recored would be at different places, even on different servers if you setup multiple servers, but that does not make sense.