Let me start off by saying that your JOINs are well written but you don't
"need" to check against both table_1 and table_2. If A = B and A = C then B
= C. It works in both algebra and in databases. However, I would separately
try joining _1 to _3 and _2 to _3 to see if either join resolved faster.
Your performance will vary according to your system variables, your
available memory, and lots of other factors. The only way to determine what
will be faster for your design is to benchmark each combination and see
which one gives you the best response.
The judicious use of indexes really helps to improve JOIN performance. If
you are only using a few columns from table_1 or table_2 you _might_ be
able to boost performance by creating a "covering index" that contains all
of those columns. You will have to weigh the increased storage cost of
another index against any performance gains it might give you. Once again
it comes down to what works best with your application on your hardware.
Test, test, and retest. That's the only way to tweak-out peak performance.
Best Wishes,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
ighen
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
net.il> cc:
Fax to:
06/15/2004 05:17 Subject: performance with joins
AM
I have 3 tables:
- table_1 - pri key is id+lang
- table_2 - pri key is id
- table_3 - pri key is id+lang (Full-Text table)
To join all 3 tables with their keys and run a full-text query, I
understand I need to put the Full-Text table at the end of the joins..
If I use the tables as defined above, then the join would be:
... FROM table_1 INNER JOIN table_2 ON table_1.id=table_2.id INNER JOIN
table_3 ON table_2.id=table_3.id AND table_1.lang=table_3.lang WHERE ...
The last join uses a primary key from both table_1 and table_2.. is that
slower than a case that table_2 key would be id+lang (which then the last
join would use id+lang from table_2 alone) ???
-thanks, Lorderon.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
