MySQL not using PRIMARY KEY to sort by default [duplicate]

Question

I have a large MySQL table on 5.6.35-81.0 Percona Server with gaps in the PRIMARY key. The table has around 53 Million records and the auto-increment key is now at around 370 million. The table is a list of phone numbers and users add/delete the numbers very frequently hence the PRIMARY key which is BIGINT has gaps inside.

The problem I am facing now is that when I do a simple SELECT * FROM table, MySQL does not used the PRIMARY key to sort the results by default. It uses the phone number field from the table to sort the data and because of this the data is SELECTed as sequential instead of the way it's saved in the DB.

Offcourse SELECT with ORDER BY id takes a very long time. The other solution is to copy all the data to another table but that would require application downtime. How can I fix this or to avoid such in the future?

CREATE TABLE `contacts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `phonebook_id` int(11) NOT NULL,
  `campaign_id` int(11) DEFAULT NULL,
  `contact` varchar(45) NOT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `call_status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `active_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `test_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `dnc_contact` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `v_node_name` varchar(45) DEFAULT NULL,
  `timezone` varchar(4) DEFAULT NULL,
  `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_date` timestamp NULL DEFAULT NULL,
  `contact_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phonebook_id` (`phonebook_id`,`contact`),
  KEY `idx_001` (`active_call`,`campaign_id`,`test_call`),
  KEY `idx_002` (`phonebook_id`,`call_status`,`test_call`,`campaign_id`,`active_call`,`dnc_contact`),
  KEY `idx_003` (`phonebook_id`,`test_call`,`call_status`,`dnc_contact`),
  KEY `FK_campaign_id_idx` (`campaign_id`),
  KEY `idx_testcall` (`test_call`),
  KEY `idx_004` (`campaign_id`,`call_status`,`test_call`),
  KEY `idx_active_call` (`active_call`)
) ENGINE=InnoDB AUTO_INCREMENT=2369201 DEFAULT CHARSET=latin1;

Answer 1

MySQL like any other RDBMS do not sort results by default. You have to add ORDER BY clause to get an ordered result. Otherwise the order rows are returned is undefined.

In some degenerate cases you have a chance to get an ordered result without ORDER BY - e.g. when you have bulk-inserted preordered rows into the newly created table, and if you do not DELETE or UPDATE any row before SELECT. But when the table is heavily DELETEd or UPDATEd, mysql performs so-called "index tree rebalancing". Traversing of the rebalanced index tree returns rows in the unpredictable order. Therefore you should never rely on assumption that query without ORDER BY returns an ordered result.