[SOLVED] [MYSQL 5.0] Views

[Hooker]

I was just wondering if it were possible to select the content's of a view into a table efficiently?

 

I'm currently generating the view like so:

CREATE OR REPLACE VIEW initial_check AS
   SELECT LEFT(tbr.Forename, 1) AS Forename, tbr.Surname, LEFT(tbr.Addr1, 5) AS Addr1, tbr.Postcode
   FROM test_suppress
   INNER JOIN tbr
      ON (LEFT(test_suppress.forename, 1)=LEFT(tbr.forename, 1)
      AND test_suppress.surname=tbr.surname
      AND LEFT(test_suppress.addr1, 5)=LEFT(tbr.addr1, 5)
      AND test_suppress.postcode=tbr.postcode)

 

Which only adds around 375 rows to the view but when i go to select the contents it takes 27 seconds to pull it all out.

[fenway]

Did you run the EXPLAIN on the underlying select?

[Hooker]

Yes, when actualy generating the view it runs in 0.2 seconds and uses the right indexes but as soon as i try to query it after it totaly crawls.

[Hooker]

1, 'SIMPLE', 'test_suppress', 'index', 'Surname,Postcode,test', 'db', '10', '', 29932, 'Using index'
1, 'SIMPLE', 'tbr', 'ref', 'Surname,Postcode,test', 'Postcode', '10', 'db.test_suppress.Postcode', 1, 'Using where'

 

[fenway]

And the EXPLAIN on the corresponding query on the view?  Index usage is very wonky with VIEWs.

[Hooker]

It took about 30 seconds to generate the explain on the query against the view, the tables are only about 500,000 records big

 

1, 'PRIMARY', '<derived2>', 'ALL', '', '', '', '', 172, ''
1, 'PRIMARY', 'test_suppress', 'ref', 'Surname,Postcode', 'Postcode', '10', 'dup_checks.Postcode', 1, 'Using where'
2, 'DERIVED', 'test_suppress', 'ALL', 'Surname,Postcode', '', '', '', 152199, 'Using temporary; Using filesort'
2, 'DERIVED', 'tbr', 'ref', 'Surname,Postcode,Both', 'Postcode', '10', 'bandecote.test_suppress.Postcode', 1, 'Using where'

 

I've only got experiance of views in foxpro and there, they are alot more efficient.

[fenway]

I see ALLs and FILESORTs...

[Hooker]

yea, i'm trying to work out how to get the view to use the index's from the query that generated it.

 

The query that generates it runs in under 3 seconds consistently, the query that i use the view within runs in 0.3 seconds consistently when it's querying a normal table but for some reason the actual view seems to ditch the index's completely.

 

I've been looking around and found a couple of mentions of being able to force the view to use index's but no explinations/examples.. anyone have a clue?

[Hooker]

Here are the actual queries by the way:

CREATE OR REPLACE VIEW dup_checks AS
   SELECT LEFT(tbr.Forename, 1) AS Forename, tbr.Surname, LEFT(tbr.Addr1, 5) AS Addr1, tbr.Postcode
   FROM tablea
   INNER JOIN tbr
      ON (LEFT(tablea.forename, 1)=LEFT(tbr.forename, 1)
      AND tablea.surname=tbr.surname
      AND LEFT(tablea.addr1, 5)=LEFT(tbr.addr1, 5)
      AND tablea.postcode=tbr.postcode)
   GROUP BY tbr.Postcode;

UPDATE tablea
  INNER JOIN dup_checks
    ON (LEFT(tablea.forename, 1)=dup_checks.forename
    AND tablea.surname=dup_checks.surname
    AND LEFT(tablea.addr1, 5)=dup_checks.addr1
    AND tablea.postcode=dup_checks.postcode)
SET tablea.dupe="TBR";

DROP VIEW dup_checks;

 

Because the tables can run into the millions (and potentialy hundreds of millions) of records the idea was the dump the matching records into a temp table and then run an update on "tablea" with the resulting set but the insert's into the temp table were taking alot of time which is why i'm looking into views.

 

 

[fenway]

Why no where clauses?

[Hooker]

I'm just trying to flag matching records between the tables.

[fenway]

I'm just trying to flag matching records between the tables.

Yes, but that can blow up index usage... see what happens if you move the expressions to where where clause.

[Hooker]

where where clauses?

[fenway]

where where clauses?

Duplicate word.

[Hooker]

No luck, and ontop of that mysql has stopped using the index's for the "test_suppress" (tablea) table:

 

1, 'SIMPLE', 'test_suppress', 'ALL', 'Surname,Postcode', '', '', '', 152199, 'Using temporary; Using filesort'
1, 'SIMPLE', 'tbr', 'ref', 'Surname,Postcode,test', 'Postcode', '10', 'db.test_suppress.Postcode', 1, 'Using where'

[fenway]

OK... post the table structures (from SHOW CREATE TABLE) and some sample data, and I'll see what I can cook up... this is getting to complicated to follow, explains without queries, etc.

[fenway]

Ok... seems to work for me... try this:

 

CREATE OR REPLACE VIEW dup_checks AS
SELECT LEFT(tbr.Forename, 1) AS Forename, tbr.Surname, LEFT(tbr.Addr1, 5) AS Addr1, tbr.Postcode
   FROM test_suppress 
   INNER JOIN tbr use index (`both`)
      ON (test_suppress.surname=tbr.surname
            AND test_suppress.postcode=tbr.postcode)
where LEFT(test_suppress.forename, 1)=LEFT(tbr.forename, 1) AND LEFT(test_suppress.addr1, 5)=LEFT(tbr.addr1, 5)

 

And

 

select * from test_suppress 
  INNER JOIN dup_checks use index (`both`)
      ON (test_suppress.surname=dup_checks.surname
            AND test_suppress.postcode=dup_checks.postcode)
where LEFT(test_suppress.forename, 1)=LEFT(dup_checks.forename, 1) AND LEFT(test_suppress.addr1, 5)=LEFT(dup_checks.addr1, 5)

 

Hope that helps.

[Hooker]

decreased the query time from 4 minutes to 33 seconds comparing 47mill against 500k records thanks alot fenway!

[fenway]

My pleasure... sorry for the 2-week delay.