Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2011
    Posts
    17

    Exclamation Unanswered: Which Index scheme is best suitable ??

    Click image for larger version. 

Name:	per.png 
Views:	18 
Size:	83.4 KB 
ID:	11693

    above is the figurefor which we have to apply indexing scheme, click to enlarge the figure

    Hi above is the Shema pls suggest which would be best indexing scheme suitable for this ie clustering, primary,secondary .My request to database masters is that all of us know how to write queries so there is no need for queries here , we can observe table and one can easily tell which indexing scheme is best choosen so my request is to please suggest which indexing scheme we can apply here and why ??

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what do you think you will need to index, bearing mind the ways you expect the data to accessed
    what do you think needs to indexed so the SQL engine will benefit from indexing
    so what coloumn(s) in waht index(es)
    as a first off I'd expect all PK's and where appropriate FK's
    but what e3lse strikes you as benefitting from indexing?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    My advise here is no queries, then no indexes needed.

    I think this question has been raised before.

    Your requirement is like saying I need a client system developed but I am not going to tell you about what is in it because you should all already know.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by it-iss.com View Post
    I think this question has been raised before.
    you recall correctly, sir

    http://www.dbforums.com/mysql/166566...me-choose.html

    he got no indexes then because he refused to show his queries

    i see the same outcome in this thread

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Posts
    17

    Exclamation

    hi ,

    i looked into the index , as for learning u do not need a queries like you are really doing a actual implementation , you can identify columns which would be frequently updated ( pls who gives answer can make suitable assumption ) and hence forth it is possible to know which column will be constructed on primary /keyfield or non keyfield and so which index type to choose seeing their perfomance efficiency and validity . I have no queries sorry , its a very basic question which was from reputed material ( stated that no queries required at start just make assumption of fields getting updated )

    So database masters I have explained what ever is the scenarion , pls help !!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, here, try this ...

    on each table, figure out which column is the primary key, and declare it when you create the table using PRIMARY KEY syntax -- this will automatically create a unique index on that column

    in addition, declare an index on each column used as a foreign key in other tables, e.g. custno in the account table

    vwalah!! there is your answer to your "very basic question which was from reputed material"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2011
    Posts
    17
    hi , thanks for the help , the index created on primary key will e by default cluster index , right ? , now for the other attributes how will u decide from above schema which index we can use , see you can assume some fields gets updated most frequently so we use index on it , pls guide me , I know in real time we require query but its just a example so pls guide !!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by learnerkid View Post
    see you can assume some fields gets updated most frequently so we use index on it
    this is incorrect

    just because a column is updated frequently, it does ~not~ get an index

    for example, current_balance in your account table is probably the most frequently updated column in that table -- but it does not get an index

    i've said it before, and this is the last time i will say it -- indexes are ~not~ created in a vacuum, they are created in order to optimize specific queries, and if you cannot give a specific query, then you do not need any more indexes

    good bye and good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •