RJ,
1. Not sure if I got this right, just let me know, but for the EXPLAIN
Quote
Would you do
EXPLAIN SELECT c1, c2, c3, c4 FROM t1, t2 WHERE t1.c4 = t2.c4;
I suspect it will do a table scan on t2 (the smaller table) and then look up the appropriate row in t1. The UPDATE will _probably_ work the same way.
Ran:
EXPLAIN SELECT t1.c1, t1.c2, t1.c3, t1.c4
FROM t2, t1
WHERE t1.c4=t2.c4;
Which returned:
+----+-------------+-------+------+---------------+------+---------+-----------------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-----------------+----------+--------------------------+
| 1 | SIMPLE | t1 | ALL | c4 | NULL | NULL | NULL | 43660685 | |
| 1 | SIMPLE | t2 | ref | c4 | ITEM | 78 | myDb.t1.c4 | 52 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-----------------+----------+--------------------------+
2 rows in set (0.00 sec)
or same answer but from Workbench to spread sheet(don't know how the above will read in the post)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL c4 NULL NULL NULL 43660685
1 SIMPLE t2 ref c4 ITEM 78 Mydb.t1.c4 52 Using where; Using index
2. Partition:
An accounting function. Considered bad juju to ever lose even one record. When an audit is concluded, the entitre db, source document files and queries will be saved to smallest optical disk possible cd, dvd, br and archived with the goal of being able to reconstruct everything quickly if needed. Then the data will be truncated, with the schema saved so we can do it all again next year with new data. Dbs are never “live”.
3. Partitioning sounds interesting for future "live" dbs in planning. Got a link to where you consider a “best” explanation of it?
4. Column names: The current needs just match product properties together by Widget. IE t1 (widgetA, domestic, companyB, dt_sold), t2 (widgetA, blue, plastic, 8inch). Key is widget, c4.
5. Want updated table to use for report queries otherwise have to use a join every time in addition to report conditions. One time shot correct.
6. Will use php. Will work out the coding. Thanks
7. Thinking to use auto increment id to solidly limit reports to 1 million records with out adding in yet another calculated condition. IE: WHERE idt1 BETWEEN '1000000' AND '2000000'. Reports much over 1 million (+/-) rows won't open in spreadsheets. Better option?
8. Dedicated servers, built from parts on hand with new mb, cpu, memeory, and hard drive as needed. Plug them into the net and kvm boxes, but doing a web app, which is were was worried about file locking.
Changed my buffer sizes. Have not had time to check. Blah, Blah, but the source files had to be redone based on our findings. No doubt it will help a ton and will do more homework before running queries again. Have not had time for fully check the links you sent at this time as was working on previously mentioned project.
Again many thanks. Have to believe you've been there and done that, so you know how great it is to get advice that actually works.
Ed