MySQL Forums
Forum List  »  General

Re: Updating 100 million record tables
Posted by: ed ziffel
Date: December 05, 2011 11:11AM

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

Options: ReplyQuote


Subject
Written By
Posted
November 26, 2011 02:58PM
Re: Updating 100 million record tables
December 05, 2011 11:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.