I want to create a table which will contain approx 20,000 records of three fields each. Two of the fields will contain numeric values.

I want to be able to issue a SELECT BETWEEN command. One record will be returned.

I anticipate that certain records will be returned more frequently than others. Will I gain any performance increase by placing these records near the start of the table? (I'm assuming that MySQL reads a table sequentially).

  • if your query returns one record from 20,000 then this is called a seek or range seek and it doesnt matter where they are.A table is an unordered set,order only makes sense when you retrieve the data with ORDER BY. – Mihai Apr 3 '16 at 11:16

The answer is kind of "no" but more "your question does not have an answer because it's based upon incorrect assumptions".

1) Tables are not really stored in a particular order.

2) Even if they were, how would the DB know that the question had been answered until it had finished reading the entire table. Putting things "up front" would not help.

3) What you ask (range of values) is in the realm of an index. An index has more structure and roughly the concept of "beginning and end" so would be the way to go. Build an index on the values you intend to query.

4) The way indexes are structured means that individual rows can be selected from billions with just a handful of I/O reads. However you need to have the right values indexed to match the queries. Look at the plan to see if it's working for you.

  • 1) Technically they are stored ordered by primary key in InnoDB, since that's always the clustered index, although that still doesn't matter, for the other reasons, which you correctly stated. Indeed, it's a simplification but a reasonable one to say that a fundamental purpose of an index is to prevent the server from scanning through the table sequentially, regardless of what "sequentially" actually happens to mean. – Michael - sqlbot Apr 3 '16 at 23:08
  • Thanks. Your number 2 raises an interesting point. If I know that the value I want only exists in one record, then I want a SELECT option that says something like "once you have found one occurrence, don't bother searching any more". Does such a thing exist? – Simon Roberts Apr 4 '16 at 8:23
  • To answer my own question - yes, it does. It's "LIMIT 1". – Simon Roberts Apr 4 '16 at 9:45
  • True, but if that's really, always the case you should have a unique constraint on those columns. That will be most correct, fast and in keeping with the way things are 'supposed to be' in the world of RDBMS – LoztInSpace Apr 4 '16 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.