a*******s
发帖数: 324 | 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*****g
发帖数: 34098 | 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*******t
发帖数: 891 | 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 的大作中提到】
: 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*******s
发帖数: 324 | 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*****g
发帖数: 34098 | 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*****e
发帖数: 364 | 6 IN SQL, SET Statistics IO ON, will show the results. | x***e
发帖数: 2449 | 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 的大作中提到】
: 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
| B*****g
发帖数: 34098 | 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)
|
|
