Join optimization - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

Join optimization

Join optimization# Database - 数据库

a*s2007-10-31 07:10

1 楼

I have two tables. table email has 1 million records. table domain has 20
records. domain is part of email address.
for example: email: a*******[email protected]
domain: yahoo.com
create table email(email varchar(40) primary key, domain varchar(20), index(
domain));
create table domain(domain varchar(20) primary key);
case1:
select p.email from email p, domain q where p.domain = q.domain
select p.email from email p, domain q where q.domain = p.domain
Is it the same?
Does the databa

B*g2007-10-31 07:10

2 楼

the answer for these questions is "depends".
Without talking about more database structure like index, and without analys
is the real data, can not answer this questions.
但是一般来说，最好不要太依赖database。你是最了解你自己的table的，而不是datab
ase

index(

【在 a*******s 的大作中提到】

: I have two tables. table email has 1 million records. table domain has 20
: records. domain is part of email address.
: for example: email: a*******[email protected]
: domain: yahoo.com
: create table email(email varchar(40) primary key, domain varchar(20), index(
: domain));
: create table domain(domain varchar(20) primary key);
: case1:
: select p.email from email p, domain q where p.domain = q.domain
: select p.email from email p, domain q where q.domain = p.domain

a*t2007-10-31 07:10

3 楼

you can see the query excution plan to see how the db engine optimize the
execution
in most cases, it does a good job

index(

【在 a*******s 的大作中提到】

a*s2007-10-31 07:10

4 楼

In MySQL, I use the explain to see how it works.
It seems that primary key has higher priority that index(non primary or
unique keys). It is reasonable.
Thanks

【在 a*******t 的大作中提到】

: you can see the query excution plan to see how the db engine optimize the
: execution
: in most cases, it does a good job
:
: index(

B*g2007-10-31 07:10

5 楼

呵呵，primary key一般都有index

【在 a*******s 的大作中提到】

: In MySQL, I use the explain to see how it works.
: It seems that primary key has higher priority that index(non primary or
: unique keys). It is reasonable.
: Thanks

b*e2007-10-31 07:10

6 楼

IN SQL, SET Statistics IO ON, will show the results.

x*e2007-10-31 07:10

7 楼

index(
I have to say you need to read some book before posting questions.
Yes, they are the same, cause they are inner joins.
If outer join, it depends.
some recommendations for you.
avoid using varchar as primary key.
try to have ID columns instead.
like email(email_key, email, domain_key)
domain(domain_key, domain)
all key columns should be int,which is the best for index.
domain table does not need primary key or index, since it is just too small.

【在 a*******s 的大作中提到】

B*g2007-10-31 07:10

8 楼

wk，你们翻老贴。
其实在email table 给domain加个index就行了，1M record也不是很多

20
small.

【在 x***e 的大作中提到】

:
: index(
: I have to say you need to read some book before posting questions.
: Yes, they are the same, cause they are inner joins.
: If outer join, it depends.
: some recommendations for you.
: avoid using varchar as primary key.
: try to have ID columns instead.
: like email(email_key, email, domain_key)
: domain(domain_key, domain)