MySQL reporting question

[Joseph Sinclair]

As I understand your structure and requirements (it's a bit fuzzy, especially the t2.varchar2 == t2.varchar2)
Create two *materialized* views and refresh both views (1 then 2) every reporting period (daily?).
  View1 : select status, varchar1, varchar2, max(timestamp) from t2 where varchar1 != NULL AND max(timestamp) GROUP BY status, varchar1, varchar2;
  View2 : select stuff, status from t1 INNER JOIN View1 ON t1.varchar1 = t2.varchar1;

Report is then:
  Select * from View2 where status = $some_val;

der.hans wrote:
> moin moin,
> 
> t1: t1id, varchar1, stuff
> t2: t2id, timestamp, varchar1, varchar2, status
> 
> I need to match the most recently entered status from t2 with stuff from
> t1 in MySQL.
> 
> t2 is grouped by varchar2.
> 
> t1 will have millions of rows. t2 has millions of rows and will grow at 4
> or 5 times the rate of t1. t2.varchar1 will often be NULL. Non-NULL values
> will mostly match up with t1.varchar1. t1.varchar1 can not be NULL.
> varchar2 is a clustering of information about varchar1, but usually only
> one entry in the cluster will have a value for varchar1.
> 
> t1.stuff, t2.status == $some_val where max(t2.timestamp) and t1.varchar1
> == t2.varchar1 and t2.varchar2 == t2.varchar2
> 
> This is for reporting and will run on a slave.
> 
> Any suggestions on how to efficiently build out the report? Unlike my last
> DB job, I can create indexes, views, etc.
> 
> I can also add columns to either table.
> 
> ciao,
> 
> der.hans

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: OpenPGP digital signature
URL: <http://lists.PLUG.phoenix.az.us/pipermail/plug-discuss/attachments/20100622/606329b8/attachment.pgp>