Index creation in a database

[momentum]

red marker part in my book is very confusing.
It says you will not succeed to create index by that.

Is it trying to say that if we have two tables and there is a FK relation in them , then I wont be able to create index in either table?

Can you please help with an example exactly when I wont be able to create index as per that red marker.

 

[cronxeh]

If you have data in your table (relations) that has duplicate entries and you try to create a unique index using that key, you should not be able to.

For example you can have people with same names, dates of birth, address, department id, title, etc.

Example where it will fail: https://stackoverflow.com/questions...xisting-table-in-mysql-which-contains-records

You should choose something unique like SSN# or EmployeeID for unique index creation, i.e

likely to be a unique table:
EmployeeID FirstName LastName Title Department Salary

vs

unlikely to be unique:
FirstName Gender Department

 

[momentum]

.some confusions ....

If you have data in your table (relations) that has duplicate entries ..

1. Are we talking about single table here ? Yes/No?

2 .You wrote table(relations) ? are they same here ?

You should choose something unique like SSN# or EmployeeID for unique index creation

3. You said "unique index creation".....ok ..fine ... can we create non unique index also ?

4. An example,suppose, vehicle_state_code and registration_no combination will make unique tuples in my table.
Shall I be able to create unique index with these two fields? Is it okay?

 

[cronxeh]

The indexes are per specific table.

The “[unique]” in textbook means it’s optional and just adds more constraints. Whenever you see brackets that means it’s an optional specifier.

You can create index without unique constraints and have duplicate entries like second example below in that book where same name and same salary can exist in the table, for example interns with names like Dave/John/Andrew getting paid zero or minimum wage.

For vehicle state code and registration number question, it depends on the data, you can have same registration number but different vehicle state code, and same registration number if it’s been reused by that state. So given how dysfunctional governments are, you can bet there will be duplicates.

 

[momentum]

.

You can create index without unique constraints and have duplicate entries like second example below in that book where same name and same salary can exist in the table, for example interns with names like Dave/John/Andrew getting paid zero or minimum wage.

okay.

Please look at this script.

create index empindex
on EMPLOYEE(Name Asc,Pay_Rate Desc)

Its very confusing that there is Asc,Desc. What index does with this ordering? does ordering is optional ?

 

[verty]

okay.

Please look at this script.

create index empindex
on EMPLOYEE(Name Asc,Pay_Rate Desc)

Its very confusing that there is Asc,Desc. What index does with this ordering? does ordering is optional ?

Your example is an index (name asc, pay_rate desc). This index will be used when it can be, but it's quite restrictive. For example, these will be quicker:

1. ORDER BY name
2. ORDER BY name, pay_rate desc
3. ORDER BY name desc, pay_rate

And these will be just as slow as before:

1. ORDER BY pay_rate desc
2. ORDER BY pay_rate desc, name
3. ORDER BY name, pay_rate

Ordering is not optional, it just defaults to ascending.