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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 4 down vote accepted

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`
share|improve this answer
Beautiful thanks! – Andy Jan 20 '12 at 17:55

Your Answer


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.