Q: using indexes in select 1

[thedaver]

I have two tables.
=====================================
create table company (
co_id int not null auto_increment primary key,
co_name char(80) not null);

create table user (
us_id int not null auto_increment primary key,
us_co_id int not null,
us_name char(80) not null,
index idx_us_co_id us_co_id());
=====================================

stupid question: I want to leverage the index to select users who are associated with the company. Do I have to reference "idx_us_co_id" or "us_co_id" from table 'user' in order to get the benefit of indexing. In short, which is (theoretically) faster because it is using the index???

STATEMENT 1:
select * from user u, company c where u.idx_us_co_id=c.co_id and u.us_id=5;

STATEMENT 2:
select * from user u, company c where u.us_co_id=c.co_id and u.us_id=5;

Do I even need the index in table 'user' in this use context?




Surfinbox.com Business Internet Services - National Dialup, DSL, T-1 and more.

http://www.surfinbox.com/business/

 

[sleipnir214]

As a good general rule, you can speed SELECTs by having indeces on those columns that appear in WHERE clauses.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[thedaver]

OK, good, but do I refer to the INDEX name or the column name in the where clause?!?!!? I am not using compound column indices (ie index a_and_b(a,b)).

Surfinbox.com Business Internet Services - National Dialup, DSL, T-1 and more.

http://www.surfinbox.com/business/

 

[sleipnir214]

Nope. MySQL will use indeces as appropriate automagically.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[tshot]

hi sleipnir214
Though not related to the tread but what is 'automagically' ? I have come across at no of places but never fighred what is the diff between this and automatically




----------------
ur feedback is a very welcome desire

 

[r937]

http://www.faqs.org/docs/jargon/A/automagically.html

"Automatically, but in a way that, for some reason (typically because it is too complicated, or too ugly, or perhaps even too trivial), the speaker doesn't feel like explaining to you."

in this case, too complicated

;-)

rudy

http://r937.com/

SQL Consulting

 

[sleipnir214]

r937 has it right.

The definition probably has its roots in Arthur C. Clarke's Third Law of Technology, which states, "Any sufficiently advanced technology is indistinguishable from magic."



If you want to understand how MySQL uses indexes, go to the horse's mouth:

http://www.mysql.com/doc/en/MySQL_indexes.html

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[tshot]

thanks to both of you sleipnir214 and r937.
And this time i am careful to write complete you for u. I write in short because i have to pay for the internet account charge for the time i am on + the phone call which has 2 min as one unit (+ the taxes on it. Presently 8% which is to become 12% verry shortly)




----------------
ur feedback is a very welcome desire

 

[r937]

i understand about connect time

the answer is shorter sentences

fewer words, clearer ideas

try it

it's easy





rudy

http://r937.com/

SQL Consulting