This question already has an answer here:

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;

marked as duplicate by ypercubeᵀᴹ mysql Jul 13 '17 at 13:49

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.

  • 2
    "when I do a simple SELECT * FROM table". Do you really select all 53 million rows? – ypercubeᵀᴹ Jul 13 '17 at 13:48
  • 1
    Please provide SHOW CREATE TABLE; there are several reasons that might explain what you are seeing. – Rick James Jul 13 '17 at 19:44

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.

  • ORDER BY takes a long time to return data even if I run SELECT * FROM contacts WHERE phonebook_id = 2404 ORDER BY id LIMIT 100; What's the solution to this problem? – NBhatti Jul 13 '17 at 14:59
  • 1
    Do you have an INDEX phonebook_id (phonebook_id) for your table? Or even INDEX id_phonebook_id (id, phonebook_id)? – Kondybas Jul 13 '17 at 15:08
  • Compound index on phonebook_id, contact. I guess if we add another with id, phonebook_id that may help? – NBhatti Jul 13 '17 at 15:53
  • 1
    General rule is to have an index with all the columns mentioned in the WHERE and ORDER BY clauses as well as used for JOIN ... ON conditions. – Kondybas Jul 13 '17 at 15:57
  • 2
    This Answer is assuming InnoDB. MyISAM works different. Which engine is involved? – Rick James Jul 13 '17 at 19:44

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