-1

The question was about "why use the index for a SELECT *" and it turns out that the primary key is a clustered index, so reading from a covering index is faster.

You lot locked onto "there's no order by", and yes of course any order is allowed in this case, but no one explained why MySQL would choose not to use primary key order with a SELECT * FROM

I think that this is a perfectly valid question, also it isn't a duplicate! I wasn't asking about the 'default' search order, I was confused as to why MySQL would choose a different order to primary key.

  • 4
    You wording: "I'm using MySQL and I've always expected a SELECT * FROM 'Table' query to return rows in order of primary key, ... However it is not!" The selected (duplicate) question explains why you should never do that (expect an order when there is no ORDER BY.) That's why it was closed. – ypercubeᵀᴹ Sep 7 '15 at 7:40
  • Perhaps we should have chosen this one, which has a similar issue, a different index selected and not the primary: Why does InnoDB table by default uses the UNIQUE constraint instead of the PRIMARY KEY? – ypercubeᵀᴹ Sep 7 '15 at 7:44
  • Which wording do you mean is synonymous with "why isn't MySQL walking the primary key"? Is it the one @ypercube highlighted in bold (i.e. taken from your question) or the title of the other proposed duplicate target? Sorry, I'm just trying to understand whether "why isn't MySQL walking the primary key" is the summary of your closed question or you mean it's the question that you were actually not asking (but people might have misunderstood you were). – Andriy M Sep 7 '15 at 8:52
  • @AndriyM the query is SELECT * FROM and MySQL isn't using the primary key to do this. This is because InnoDB (BUT NOT MyISAM) uses a clustered primary key. So basically the primary key is a BTREE of the primary key columns with pointers to the rows. An index is a (structure) containing some columns and the primary key. In my example there was a covering index which could be read directly (not requiring any lookups) and thus was faster. In the absence of an order by MySQL was free to do this. At the time I was coming from MyISAM, also indexes that contain THE ENTIRE TABLE are rare. – Alec Teal Sep 7 '15 at 8:56
  • 6
    Questions with misconceptions about expected order without ORDER BY come up often. Without an explicit statement in your question that you are aware that there's no default order and that MySQL is free to choose the output order at any time, even for the same query, your question was probably seen as one of those with misconceptions. However, even if you explained things right away and were understood correctly, there might still be the issue of why you needed to know this. To me, personally, this would seem like a too localised problem. I honestly can't see why the answer can matter here. – Andriy M Sep 7 '15 at 9:08
  • @AndriyM I will be honest and say on the DBA site I assumed some knowledge would be implicit. Also yes I agree it is poorly phrased, I really REALLY want to edit/delete - I'm not saying it's a brilliant question. – Alec Teal Sep 7 '15 at 9:28
7

The issues here are:

  1. Should the question have been closed as a duplicate?
  2. Should it have been locked?
  3. Can it be unlocked and reopened now to allow clarification?
  4. Can the question be deleted because the author regards it as looking silly?

Having read every edit and every comment (deleted or otherwise) here, in chat, on the question itself, and a later question, here are my views:

  1. Yes. A question can be a duplicate, without being an exact duplicate. As currently written, the question is addressed by the linked answers. In addition, the title of the question is useful for people searching for answers to the this broad class of question. Note: closed questions cannot receive new answers.

  2. Yes. There are 22 deleted comments, which easily justify locking to prevent further extended discussion. Note: Locked questions cannot be voted on, commented on, or deleted

  3. On balance, I feel this is not the right course of action. The edit would almost certainly change the question fundamentally. Asking a new, more specific question seems more appropriate, perhaps with a self-answer to explain the specific results.

  4. The thing preventing this question being deleted by the owner is the locked status (an unanswered question can be self-deleted). As noted above, I regard the lock as appropriate.

    That said, my reading of all this is that while we desire good, searchable Q & A, we surely do not want people to feel embarrassed (even though there was probably ample opportunity to clarify the question before it was closed and locked).


I therefore propose a compromise solution, which will ensure the question title and body is still searchable, while removing any embarrassment felt by the original author:

  • I will re-ask the question as Community Wiki, and immediately close as a duplicate and lock. The original question can then be deleted without loss to the site. If the original author wishes, he can then re-ask, and perhaps answer, the question he actually intended to ask*.

Please indicate your support or otherwise for this proposal by voting.

EDIT: The other option you have is to request deletion of your Database Administrators account.

* Though it may be a duplicate of this question as ypercube noted in a comment to the question above.

  • 4
    Seems like a good compromise – Philᵀᴹ Sep 7 '15 at 11:19
5

I think you should go with the recommendation provided with the close reason.

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

Ask a new question and don't mention order by in the output in the new question. Just refer to the EXPLAIN and ask a question why the secondary index was used and not the primary index.

  • MySQL chose to use an index for the SELECT * because a clustered index is slower than a covering index read. I don't see this mentioned on the page. – Alec Teal Sep 7 '15 at 8:34
  • 2
    @AlecTeal Well, that is just great that you have found it out. If this is not covered in a question/answer on this site I suggest that you ask a new question and answer that question yourself. There is nothing wrong in answering your own questions. Example 1, Example 2 – Mikael Eriksson Sep 7 '15 at 8:43
  • I would love to answer that question rather than look silly (of course I know without an order by it's under no obligation to order results) but it's been locked so I cannot edit or answer. That's what I'm annoyed about. – Alec Teal Sep 7 '15 at 8:44
  • BTW. MySQL chose the index because it's a covering index for the statement. The primary key is a clustered index. So a btree of the primary key with pointers to row data. An index is a btree of all the columns in the index. So all it had to do was walk the tree, rather than lookup too! This is specific to InnoDB NOT MyISAM and co. So a very 'specific' question – Alec Teal Sep 7 '15 at 8:49
  • 1
    @AlecTeal "specific" it was not. because your question never mentioned InnoDB, you never supplied the CREATE TABLE script (although you were asked to in comments). If you had done, there was already a similar question (for InnoDB and an index that covers all the table columns) and answer here: dba.stackexchange.com/questions/61935/… – ypercubeᵀᴹ Sep 7 '15 at 8:59
  • chat.stackexchange.com/transcript/message/23928828#23928828 regarding this, I completely agree. At the time I had no idea that it was some specific InnoDB behaviour as I mention. I am aware that there's no order by (so I got frustrated in the comments - why wouldn't it use the primary key) now I know and want to fix this. I thought at the time phpMyAdmin had some default ordering column preference or something actually. Rather embarrassingly now. – Alec Teal Sep 7 '15 at 9:12
5

In addition to the points that others mentioned there are two points I would like to add.

Firstly, since this question is being asked, and handled on Meta there is a strong chance that this will set policy. What do we want our policy to be on this matter? Is it appropriate that everyone with a grievance of this nature be allowed to require that a moderator/community member take credit for their question? Alternatively should moderators have the power to assign so called "embarrassing questions" to the Community User at the OP's request?

Secondly, it is normal to wish to undo something you did/said in the past. I'm absolutely confident that every single person in this community has said something that in retrospect wasn't how they would like to be judged eternally. I know I've said lots of dumb things in my life, and I'm sure even some members of this community could point out a few of them if they so desired (If you can I'm not asking you to here).

My general stance:

  1. It should not be the policy of this site to routinely require/expect community members to take credit for "embarrassing" questions/answers.
  2. It should be understood that people who come to this, or any other public forum, asking for help shouldn't feel demeaned or be demeaned, because they don't know how to solve their problem. We're here to help and get help, not judge or be judged. As such there is no need to feel embarrassed. This incidentally is the heart of the "Be nice" policy.
  3. There are lots of avenues to rectify this situation if you feel embarrassed including (notice you don't have to only do one):
    • Write a blog post explaining your initial incorrect line of reasoning and your new knowledge. This demonstrates growth, will allow you to completely control your message, and gives you strong position if someone desires to embarrass you in the future. It will also be helpful for others who are caught in a similar line of reasoning.
    • Ask another more targeted question and provide your own answer. This gives you less control, but you can link your new question/response to your old question. That would make your updated views easier to find. As above this will also be helpful for others who are caught in a similar line of reasoning.
    • Delete your account. No real positive benefit for anyone.
    • Recognize you are a human. As a human you sometimes will make errors, and some of those errors will inevitably be embarrassing. If people insist on focusing on your errors more than the lessons you have learned from those errors they aren't the kind of people you want to be around.
  4. The moderators should do what they feel is best with this and other individual grievances. They were elected because we feel they have good judgment and trust them to make the right decisions regardless of if the decision is hard or easy to make.
  5. This meta post isn't the best way to handle the situation in my opinion. I feel meta is for problematic trends, questions of policy, and issues of interest to the community as a whole. I for one don't want meta to become a complaint forum for everyone who feels the community was unjust with an individual question/answer/etc.

Finally since I'm human. I recognize that I might be completely wrong.

4

You lot locked onto "there's no order by", and yes of course any order is allowed in this case, but no one explained why MySQL would choose not to use primary key order with a SELECT * FROM

Isn't the the whole point of the closure and the duplicate? It's just re-casting the same question, and the answer is still "use ORDER BY if you care about ordering".

This is the message that we want any users to the site getting. If you ask another question about ordering without ORDER BY it will be closed again because it is a duplicate by our standards (it is the community that decides this, not the person asking the question).

  • MySQL chose to use an index for the SELECT * because a clustered index is slower than a covering index read. I don't see this mentioned on the page. – Alec Teal Sep 7 '15 at 8:34
  • As you're the one that locked - I want to answer or delete the linked to question. It just makes me look silly, but "why isn't MySQL walking the primary key on a SELECT *" is a legitimate question. – Alec Teal Sep 7 '15 at 8:39
  • 2
    Yeah, I don't want to be your posterboy for 'why doesn't MySQL use primary key ordering' - ESPECIALLY WHEN THERE'S NO ANSWER. Unlock the post and let me edit it – Alec Teal Sep 7 '15 at 8:43
  • 1
    With hindsight I think I shouldn't have prevented you from deleting the question, sorry about that. The marginal benefit to the site of preserving a signpost warning against ORDER BY misconceptions isn't worth embarassing anyone over (much less causing the drama that we ended up with). – Jack Douglas Sep 8 '15 at 4:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .