Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>MySQL/InnoDB tables are always clustered (more on clustering <a href="http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index" rel="nofollow noreferrer">here</a> and <a href="http://www.ovaistariq.net/521/understanding-innodb-clustered-indexes/" rel="nofollow noreferrer">here</a>).</p> <p>Since primary key also acts as a clustering key<sup>1</sup>, using the surrogate primary key means you are physically sorting the table in order that doesn't have a useful meaning for the client applications and cannot be utilized for querying.</p> <p>Furthermore, secondary indexes in clustered tables can be "fatter" than in heap-based tables and may require double lookup.</p> <p>For these reasons, you'd want to avoid surrogates and use more "natural" keys, similar to this:</p> <p><img src="https://i.stack.imgur.com/EeE9w.png" alt="enter image description here"></p> <p><em>(<code>{USER_ID, PICTURE_NO}</code> in table <code>VOTE</code> references the same-named fields in <code>PICTURE</code>. The <code>VOTE.VOTER_ID</code> references <code>USER.USER_ID</code>. Use integers for <code>*_ID</code> and <code>*_NO</code> fields if you can.)</em></p> <p>This physical model will enable extremely efficient querying for:</p> <ul> <li>Pictures of the given user (a simple range scan on <code>PICTURE</code> primary/clustering index).</li> <li>Votes on the given picture (a simple range scan on <code>VOTE</code> primary/clustering index). Depending on circumstances, this may actually be fast enough so you don't have to cache the sum in <code>PICTURE</code>.</li> </ul> <p>If you need votes of the given user, change the <code>VOTE</code> PK to: <code>{VOTER_ID, USER_ID, PICTURE_NO}</code>. If you need both (votes of picture and votes of user), keep the existing PK, but create a <a href="http://use-the-index-luke.com/sql/glossary/covering-index" rel="nofollow noreferrer">covering</a> index on <code>{VOTER_ID, USER_ID, PICTURE_NO, VOTE_VALUE}</code>.</p> <hr> <p><em><sup>1</sup> In InnoDB. There are DBMSes (such as MS SQL Server) where clustering key can differ from primary.</em></p>
    singulars
    1. This table or related slice is empty.
    1. POMySQL 3-way 1..n tables relation
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USBranko Dimitrijevic
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMySQL 3-way 1..n tables relation
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COBy "create a covering index on {VOTER_ID, USER_ID, PICTURE_NO, VOTE_VALUE}" you mean adding an auto-increment index but make it to not be a PK, right?
      singulars
      1. PO
      1. USjerrytouille
    2. CO@jerrytouille This is not related to how values are generated, but what fields are included in the index. A regular index will include fields mentioned in WHERE/JOIN, but a covering index will _also_ include fields mentioned in the SELECT list. This will let the DBMS execute the query by accessing index _alone_, without touching the "main" table (which is either table heap or a B-Tree in the case of InnoDB). Please follow the link I have provided for thorough explanation. In the end, you'll have PK `{USER_ID, PICTURE_NO, VOTER_ID}` _and_ index `{VOTER_ID, USER_ID, PICTURE_NO, VOTE_VALUE}`.
      singulars
      1. PO
      1. USBranko Dimitrijevic
    3. COhmm that's pretty cool, a bit out of my league so it takes some time to sink in. if i understand correctly your answer of the whole thing: eliminates all surrogate id (auto-incre index) PKs, use real business PKs ('user_id','photo_id'...), the only index to be used is the covering index on {VOTER_ID, USER_ID, PICTURE_NO, VOTE_VALUE} for the VOTE table. My question on that is: do we create the covering index as an extra column added to the table when we design it or is it something we do when running query?
      singulars
      1. PO
      1. USjerrytouille
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload