Databases
StackOverflow2013
Postsdbo
POIndices and using primary keys as indices in MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIndices and using primary keys as indices in MySQL
    primarykey
    data
    text
    <p>I have a table <code>Assets</code> on an InnoDB engine defined as:</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE Assets ( qid SMALLINT(5) NOT NULL, sid BIGINT(20) NOT NULL AUTO_INCREMENT, ... PRIMARY KEY (sid,qid), KEY sid (sid) ); </code></pre> <p>I'm running the following query:</p> <pre class="lang-sql prettyprint-override"><code>SELECT COUNT(*) FROM Assets WHERE sid&gt;10000; </code></pre> <p>On my machine, this query takes about 30 seconds with 2 millions entries in the table. Now, if I modify the query to use indices, the results vary widely:</p> <pre class="lang-sql prettyprint-override"><code>SELECT COUNT(*) FROM Assets USE INDEX(&lt;index&gt;) WHERE sid&gt;10000; </code></pre> <ul> <li><code>NO INDEX</code> : No explicit <code>USE INDEX</code>, i.e., the first <code>SELECT</code> query : <strong>30 secs</strong></li> <li><code>KEY sid (sid)</code> : <strong>1.5 secs</strong></li> <li><code>KEY cid (sid,qid)</code> : <strong>1.5 secs</strong></li> <li><code>PRIMARY</code> : I used <code>USE INDEX(PRIMARY)</code> within the query. : <strong>30 secs</strong></li> </ul> <p>So these are my questions:</p> <ol> <li><p>I thought a query would automatically use the primary key as its index, based on <a href="https://stackoverflow.com/questions/7485578/is-there-any-benefit-to-creating-and-index-on-a-primary-key">this</a>. And yet there's a major difference between <code>USE INDEX (cid)</code> and <code>NO INDEX</code>. What's the difference? Also, how I do explicitly give the primary key as the index?</p></li> <li><p>If <code>NO INDEX</code> doesn't actually use the primary key as an index, what does <code>USE INDEX(PRIMARY)</code> do that causes it to have the same running time as <code>NO INDEX</code>?</p></li> <li><p>Is there a difference (not just performance wise) between <code>USE INDEX(sid)</code> and <code>USE INDEX(cid)</code> in a query that only filters by <code>sid</code>?</p></li> </ol> <p>Forgive the long post, but I wanted to let it be open to discussion.</p> <hr> <p>Ok, here's what I have found out so far:</p> <p>First of all, I'm told the key setup should be either: <code>PRIMARY KEY(qid,sid), KEY(sid)</code> or <code>PRIMARY KEY(sid,qid), KEY(qid)</code>. I don't really understand the difference. If someone does, please let me know.</p> <p>Secondly, the KEY <code>sid</code>(<code>sid</code>) references far fewer index pages than a larger key, so it tends to be faster. As for the difference between using the PRIMARY KEY as the index and a proper KEY (even if they use the same fields), I've been told it's something like this:</p> <p>Primary keys index the entire table data with the fields of the primary key. That means that the PRIMARY KEY and the data are stored together. So a query using the PRIMARY KEY would have to go through the entire table data, which even indexed would bog down on large uncacheable tables.</p> <p>With discrete keys, the number of rows may be the same, but a much smaller index (consisting of the indicated fields) is scanned, which hits a smaller number of disk blocks, and hence runs much faster. I'm assuming this is also the reason for the difference in using <code>USE INDEX(cid)</code> and using the primary key as index, both of which have the same fields.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USKarthik. V
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIndices and using primary keys as indices in MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIndices and using primary keys as indices in MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIndices and using primary keys as indices in MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COJust a side note: if you have `PRIMARY KEY (sid,qid)` then `KEY sid (sid)` makes no sense
      singulars
      1. POIndices and using primary keys as indices in MySQL
      1. USzerkms
    2. COPS: put EXPLAIN for `SELECT COUNT(*) FROM Assets WHERE sid>10000` please
      singulars
      1. POIndices and using primary keys as indices in MySQL
      1. USzerkms
    3. COhow much rows with `sid>10000` out of total 2M? If it is more than ~30% then mysql decides that fullscan is cheaper. And that is why forcing index helps
      singulars
      1. POIndices and using primary keys as indices in MySQL
      1. USzerkms
 

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