MySQL - speed up query avoid filesort and temporary

Question

my MySQL query is slow. I have 3 tables: jobs (200k records), locations (300k), positions (700k).

SELECT
    j.job_offerid
FROM `job_offer` AS j 
INNER JOIN `job_offer_localitymap` AS d ON d.`job_offerid` = j.`job_offerid` AND 
    `gps_localityid` IN(35, 3301, 3302, 3303, 3305, 3306, 3307, 3308, 124, 3811, 3805, 3709, 3808, 3809) 
WHERE 
    j.`status` = 1 AND 
    j.`job_offerid` IN(
        SELECT `job_offerid` 
        FROM `job_offer_positionmap` 
        WHERE `cb_job_positionid` IN (1001, 6, 629, 7, 8, 9, 10, 11, 12, 13, 1, 15, 16, 17))
ORDER BY j.`job_offerid` DESC 
LIMIT 3

I must filter positions and localities so I used IN.

EXPLAIN: Using where; Using index; Using temporary; Using filesort; Start temporary

Table scheme with only used rows:

CREATE TABLE `job_offer` (
  `job_offerid` int(13) NOT NULL AUTO_INCREMENT,
  `status` int(13) NOT NULL DEFAULT '1',
  PRIMARY KEY (`job_offerid`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `job_offer_localitymap` (
  `job_offer_localitymapid` int(13) NOT NULL AUTO_INCREMENT,
  `gps_localityid` int(13) NOT NULL,
  `job_offerid` int(13) NOT NULL,
  PRIMARY KEY (`job_offer_localitymapid`),
  KEY `gps_localityid` (`gps_localityid`),
  KEY `job_offerid` (`job_offerid`),
  KEY `gps_localityid_job_offerid` (`gps_localityid`,`job_offerid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE `job_offer_positionmap` (
  `job_offer_positionmapid` int(13) NOT NULL AUTO_INCREMENT,
  `cb_job_positionid` int(13) NOT NULL,
  `job_offerid` int(13) NOT NULL,
  PRIMARY KEY (`job_offer_positionmapid`),
  KEY `cb_job_positionid` (`cb_job_positionid`),
  KEY `job_offerid` (`job_offerid`),
  KEY `cb_job_positionid_job_offerid` (`cb_job_positionid`,`job_offerid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

Indexes are everywhere.

Thank you for any advice

Answer 1

Your join would benefit from a composite

job_offer_localitymap.(job_offerid,gps_localityid)

That is, going the opposite way than your present composite in that table.

As such you could drop these two:

  KEY `gps_localityid` (`gps_localityid`),
  KEY `job_offerid` (`job_offerid`),

As you would then be left with two composite indexes, with left-most of each used by other queries that benefited by the above two I just said to drop

---

In your query line 5, be consistent and use the alias j as I had to hunt (not long) to see which table

---

In my opinion the KEY status (status) in job_offer might be relatively useless but I don't know you other queries. But as you datatypes are thin, a composite on job_offer(job_offerid,status) could make many of your queries fly, as it would be a covering index not needing to go after the datapage.

---

As for job_offer_positionmap, that could be a join removing a slow subquery and developer choice for adding a composite there too. The join being similar to the first join, conceptually.

---

I see no problems with the in clauses in general, as the mysql CBO cost-based optimizer should deal with that.

---

But these are just suggestions, as adding indexes is not exactly without a downside. It is a fragile balancing act, but in the end you may find that not only does this query fly, but your others too.

---