Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Table t has an Int type primary key id without "Auto Increment"

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Use the following three ways to insert data:

  1. Insert sequence id: 1-10000
  2. Descending insert id: 10000-1
  3. Random insert id: 1-10000

Does the order in which rows are inserted as suggested above affect performance in select, order by id and query operations?

share|improve this question
1  
For 10K rows, it won't matter. For a million, see Rolando's comments. Also, MyISAM vs InnoDB matters. And whether you have the secondary index: UNIQUE(name). –  Rick James Nov 29 '12 at 1:02

1 Answer 1

up vote 1 down vote accepted

I already answered a question like this

Of the three methods to insert data you mentioned, I would easily choose method #3 : Random insert id: 1-10000. Why ?

Ascending or Descending Ordered Insertion into an index structure will generate root and node splits 45% of the time as I mentioned in my two earlier posts.

In contrast, the ordering does not matter when it comes to reloading of a table from a mysqldump or upon the execution of a LOAD DATA INFILE command. That's because of the use of sorting mechanisms after the completion of the data load of the table and the index creation can be performed.

For example, look at how a MyISAM table is reloaded.

  • Data Pages for .MYD are populated
  • Index Pages for .MYI are populated with unique keys in parallel with Data Page Reload
  • Secondary Indexes are built by the internal execution of Repair by sorting

In that instance, order does not matter.

Switching gears to SELECT ORDER BY, performance could be nominally affected if needed keys from indexes are so split apart that multiple index pages must be accessed for ranges of data, this again leads me to choose method #3. The lower the frequency of index page splits, the fewer pages need to be read by an ORDER BY request.

share|improve this answer
    
Awesome! Insertion method #1 and #2 performance is the same?(I think it should be) –  Zenofo Nov 26 '12 at 18:34
1  
Yes, Insertion method #1 and #2 performance are the same. The only difference is that the lower values will be more fragmented in the index pages loading in ascending order, while the higher values will be more fragmented in the index pages loading in descending order. –  RolandoMySQLDBA Nov 26 '12 at 18:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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