Re: Self Join question - large table

[Brent Baisley]

Absolutely not. First, you have the overhead of maintaining the two 
tables. Second, MySQL doesn't know that they are identical tables, so 
it will try to cache both of them. On a self join, MySQL does know they 
are identical, so it will only need to load the data into memory once. 
If there is enough memory.

If you want the best performance and you have enough memory, the best 
thing you can do is load the table into a heap table. Essentially you 
are preloading the entire table into RAM, eliminating the disk as a 
performance bottleneck. You just need to refresh the heap table when 
you update the real table.

On Nov 25, 2003, at 8:31 AM, Henry Hank wrote:

Hello,

  I have a query that will be used alot on my website that involves a 
42
million record table being self-joined.  The primary table instance 
will be
limited by an index resulting in 1 to about 50,000 rows being 
selected, then
joined to the second instance of the table, which will retrieve one to 
five or
so rows for each record in the source table. This table is totally 
static and
is updated once per day during a maintenance cycle.

Here's my question:  Does it make sense (from a performance 
standpoint) to
actually make a duplicate of this table so MySQL is joining two 
identical
tables rather than self joining the same table?

Thanks,

-Hank

__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]