Announcement

Announcement Module
Collapse
No announcement yet.

Partitioned view performance

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Partitioned view performance

    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';

  • #2
    Can you please show EXPLAIN for both query using view and direct query?

    Comment

    Working...
    X