Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The first explain has "uses index" at the end. This means that it was able to find the rows <em>and</em> return the result for the query by just looking at the index and not having to fetch/analyse any row data.</p> <p>In the second query you add a row that's likely not indexed. This means that MySQL has to look at the data of the table. I'm not sure why the optimiser chose to do a table scan but I think it's likely that if the table is fairly small it's easier for it to just read everything than trying to pick out details for individual rows.</p> <p><b>edit:</b> I think adding the following indexes will improve things even more and let all of the join use indexes only:</p> <pre><code>ALTER TABLE order_line ADD INDEX(session_id, id); ALTER TABLE donations ADD INDEX(order_line_id, npo_id, id) </code></pre> <p>This will allow <code>order_line</code> to to find the rows using <code>session_id</code> and then return <code>id</code> and also allow <code>donations</code> to join onto <code>order_line_id</code> and then return the other two columns.</p> <p>Looking at the <code>auto_increment</code> values can I assume that there's not much data in there. It's worth noting that the amount of data in the tables will have an effect on the query plan and it's good practice to put some sample data in there to test things out. For more detail have a look in this blog post I made some time back: <a href="http://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/" rel="nofollow">http://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/</a></p>
    singulars
    1. This table or related slice is empty.
    1. POMySQL gurus: Why 2 queries give different 'explain' index use results?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJames C
    1. USJames C
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMySQL gurus: Why 2 queries give different 'explain' index use results?
      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. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COactually this might not be 100% right. Can you add the `SHOW CREATE TABLE` for the tables and the full `EXPLAIN` output please?
      singulars
      1. PO
      1. USJames C
    2. COJust adding the second index worked, though I don't understand why. All three of these ids were indexed--why does creating an index made up of these ids get picked up where the order_line_id index is ignored?
      singulars
      1. PO
      1. USiopener
    3. COI *think* the reason this is happening is because the optimizer has decided that there is no advantage to using the single index. I'll keep an eye on this as the size of the table grows. Thanks!
      singulars
      1. PO
      1. USiopener
 

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