I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table :

CREATE TABLE `bbcmapimp_pc_raw` (
  `postcode` char(8) NOT NULL,
  `pc_sector` char(6) DEFAULT NULL,
  `pc_district` char(4) DEFAULT NULL,
  `pc_area` char(2) DEFAULT NULL,
  `parliamentary_constituency_code` char(9) DEFAULT NULL,
  `parliamentary_constituency` varchar(255) DEFAULT NULL,
  `mp_name` varchar(255) DEFAULT NULL,
  `district_code` char(4) DEFAULT NULL,
  `district` varchar(45) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `suffix` char(5) DEFAULT NULL,
  PRIMARY KEY (`postcode`),
  KEY `pcc` (`parliamentary_constituency_code`),
  KEY `suffix_index` (`pc_area`,`suffix`)

I just need to iterate the table, but in my first attempt I used OFFSET which was horrendous! I've since learned what MySQL was doing under the bonnet, and understood why it was getting slower and slower.

So, I'm trying to modify my query to use a WHERE clause instead, but the issue is that I'm ordering the query by two fields (the table has a composite index defined).

One solution would be to add an ID field that's in the correct order and use that; but I was wondering if there was any way I could specify both fields of the composite index in a single greater than WHERE clause?


1 Answer 1


You can use

FROM `bbcmapimp_pc_raw`
WHERE (`pc_area`,`suffix`) > ('FO', 'BAR')
ORDER BY `pc_area`,`suffix`

Though actually this seems to have a better explain plan

FROM `bbcmapimp_pc_raw`
WHERE `pc_area` >= 'FO' AND (`pc_area` > 'FO' OR `suffix` > 'BAR')
ORDER BY `pc_area`,`suffix`

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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