Is this proper way to create indexes?

Question

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 these queries:

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

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

 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 use? Because name of both indexes is same...

Answer 1

Index names must be unique within the same table. That is, you can't have two indexes in the same table and name both indexes username.

You can reuse an index name on a different table, like you have shown. Index names don't have to be unique over multiple tables. In this way, they are like column names. You can use the same column name in more than one table.

Some people like to define a naming convention for their index names, but it doesn't really affect anything as far as the database is concerned.

I'm especially puzzled when I see developers who think they have to use "idx_" as a prefix for every index name. It's not necessary, it's just four extra characters you have to type.

The SQL query optimizer knows which index belongs to each table, even if they have the same name. It will not get confused.

You might like my presentation How to Design Indexes, Really, or the video of me presenting it: https://www.youtube.com/watch?v=ELR7-RdU9XU

---

P.S.: I have a couple of comments that are not directly related to your question, but I have to caution you:

• Please don't store passwords in plain text. If a hacker gains access to your database, you'll be sorry. Read You're Probably Storing Passwords Incorrectly.

• You're using old-fashioned syntax for your joins. Read Why isn't SQL ANSI-92 standard better adopted over ANSI-89?