How does INDEX work with MYSQL?

How does INDEX work with MYSQL?

Suppose I got 2 tables like this

//customerTable
id auto_increment,
username char(30),
password char(40),
phone int(10)


//profileTable
id auto_increment,
username char(30),
description text


And I created an INDEX on username on both tables, like this

create index username on `customerTable` ( username, password )
create index username on `profileTable` ( username )

Then I run this query:

Query1:
select * from `customerTable` where username='abc' limit 1

Query2:
select * from `customerTable` where username='abc' and password='xyzzzzz' limit 1

Query3:
select customerTable.*, profileTable.* from
customerTable, profileTable where
customerTable.username='abc'
and customerTable.password='xyzzzzzzz'
and customerTable.username = profileTable.username
limit 1

Which indexes will these 3 queries access? Because name of both indexes is same "username"...
Thanks

 

I am not familiar with MySQL, my experience related to MS SQL. So, when you create Index, its name should contain Table name and field name (name convention). Otherwise it is hard to work with many indexes in data base. So, I would suggest to rename your Indexes to be sure.

 

oh, thanks for that
But is it just naming convention, or is that how database system uses to identify which index to use?

 

It is just naming convention. It allows you to distinguish different objects (indexes) in the database. Some time later you can decide to update indexes, and naming convention will help you to be sure that you are updating the right index.
About MySQL, I think it should be OK if you created two indexes with the same name but for different tables. It is similar when you created field with the same name "username" in the both tables.

 

Many thanks for your help I think I understand this concept now. Thank you

 

you are welcome

 

I decided to create indexes like this:

create index ct_username on customerTable ( username, password )
create index pt_username on profileTable ( username )

Thanks for your help

 

that looks better. In addition, I cannot recommend using short table names like 'ct' and 'pt'. It could mislead. One day you can create new table 'portfolioTable'. It is better to use full table name.

 

Nice point. Will use full table names instead.