About Indexing in MySQL DB [Resolved]

Posted by Thiru under Others on 8/16/2011 | Points: 10 | Views : 2013 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi

If we have two fields: CustomerID and CustomerName in a table named tblcust of having 3 Lakh records.
we are indexing customer name to get result faster for query like: select * from tblcust where CustomerName like '%a%'

Can we index CustomerID to get result faster for query like: select * from tblcust where CustomerID = '3002'

I mean indexing is enough for any one field in a table or
we need to have index for two or more fields as per our wish ?

And Let me know what is the DisAdvantage of Index

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Dhirendra on: 8/16/2011 [Member] Starter | Points: 50

0	See..there are two types of indexes, Cluster and otehr is non-cluster... whenver you create primary key column then db server autimatically creates cluster index on that column and whenver you do insert/delete operation on the table then it reduce some fraction of performance of clluster index. In your above condition, if you are using filter condition on customer name and your primary key column is customer Id then you should also create non-cluster index on customer name column which will index your record by customer name with the help of customerId primary index's (which is physical indexed records). Non-cluster index will take sort the records based on cluster index(primary key) Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

See..there are two types of indexes, Cluster and otehr is non-cluster...
whenver you create primary key column then db server autimatically creates cluster index on that column and whenver you do insert/delete operation on the table then it reduce some fraction of performance of clluster index.
In your above condition, if you are using filter condition on customer name and your primary key column is customer Id then you should also create non-cluster index on customer name column which will index your record by customer name with the help of customerId primary index's (which is physical indexed records). Non-cluster index will take sort the records based on cluster index(primary key)

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts