How come MySQL is not using the indexes when querying the view? Each one of these tables have about 600,000,000 records.
Here are the tables definition
CREATE TABLE tbl2005 (
Col1 datetime,
Col2 varchar(12),
Col3 char(2));
CREATE INDEX idx_tbl2005_Col1_Col2 ON tbl2005 (Col1, Col2);
CREATE TABLE tbl2006 (
Col1 datetime,
Col2 varchar(12),
Col3 char(2));
CREATE INDEX idx_tbl2006_Col1_Col2 ON tbl2006 (Col1, Col2);
CREATE VIEW vwMyView
AS
SELECT Col1,
Col2
FROM tbl2005
UNION
SELECT Col1,
Col2
FROM tbl2006;
SELECT *
FROM vmMyView
WHERE Col1 = ‘20050110’;
This SELECT statement will take forever.
But if I run from the query window the DML specified in the view with the WHERE clause, I get the results inmediately - see below.
SELECT Col1,
Col2
FROM tbl2005
WHERE Col1 = ‘20050110’
UNION
SELECT Col1,
Col2
FROM tbl2006
WHERE Col1 = ‘20050110’;