| Partitioned view performance [message #3026] |
Wed, 30 April 2008 01:09  |
 saleyoun
Messages: 1
Registered: April 2008 |
Junior Member |
|
|
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';
|
|
|
|
Members
Search
Help
Register
Login
Home
