Table Setup Question - Index v. Unique 1

[RRoswell]

Hi,

I've got one main table with nothing but a numeric, primary key and a varchar column that will contain unique domain names (never a duplicate).

The primary key auto_increments and the varchar column is "unique" for now.

Is that pretty much the most optimized setup, or should I make the varchar column a secondary index as well, since it does tie into other tables?

Thanks.

 

[r937]

any column which you have declared UNIQUE already has an index

tables do not require that indexes exist in order to be "optimized"

actually, what needs to be optimized are the queries

indexes which optimize one query might not optimize another, and vice versa

so we'd need to see your various queries in order to determine the "the most optimized setup"



r937.com | rudy.ca

 

[RRoswell]

That helped a lot right there.

This is one of the main queries:

$query = "SELECT dom FROM domains WHERE dom = '$n' UNION SELECT dom FROM cart WHERE dom = '$n'";

Basically what I'm trying to check for is an existing domain or a domain in the process of being purchased.

'dom' is unique in both tables.

Each table has a primary key that is basically an auto-incremented integer.

 

[r937]

the auto_increment PKs do nothing for that UNION query, agreed?

you can optimize that query by ensuring that [tt]dom[/tt] is indexed in both tables

(my suspicion is that it should be the PK in both tables, but perhaps i don't know your application requirements well enough)

also, change UNION to UNION ALL ;-)

r937.com | rudy.ca

 

[RRoswell]

"you can optimize that query by ensuring that dom is indexed in both tables"

'dom' is unique in both tables - changing that to index would be better?

I guess I'm not understanding PK's very well - I didn't think it was wise to make a string a key value.

In places where I used to work, we always had one numeric "identifier" for every table. It's been a while now (since the early 90's) but I always thought those were the PKs.

I appreciate your time and help.

I'll try the UNION ALL - the dom field value should never be in both tables at the same time.

 

[r937]

if [tt]dom[/tt] is unique, it already has an index

so "changing" it to index would actually be a step backward, since it would still be indexed but no longer guaranteed unique

unique constraints are implemented as unique indexes

PKs can be anything -- numbers, dates, strings

your experience that every table had a numeric PK is, unfortunately, all too common

surrogate versus natural keys is one of the perpetual holy wars of database design

r937.com | rudy.ca

 

[RRoswell]

I really appreciate your help.

Thank you!