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.
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.