Have a look at this query. Is there anyway of making the IN-statement a JOIN instead?

Yes, I'm quite new to this. Thanks in advance for any help.

SELECT * FROM company AS t1, employee AS t2 
WHERE t1.id IN 
    (SELECT id FROM multinational WHERE market = 'EMEA') 
AND t2.ID = t1.employee_id  
LIMIT 0, 24
  • 1
    Please provide table structure, input data and expected result. – Mladen Uzelac Dec 7 '14 at 10:16
  • 1
    And add mysql version, dbengine and indexes you are using – Mladen Uzelac Dec 7 '14 at 12:52

Try this:

SELECT c.*, e.*
FROM company AS c
  INNER JOIN employee AS e on e.ID = c.employee_id
  INNER JOIN multinational AS m ON (m.company_ID = c.id and m.market = 'EMEA')
LIMIT 0, 24
  • Well, thanks, but the story from EXPLAIN is the same for this one as the one I had: Using index condition; Using temporary; Using filesort for the "multinational" part of the query. Please excuse my ignorance here but is this kind of query destined to always use a temporary table and filesort? – Adergaard Dec 7 '14 at 12:31
  • You asked to translate your query in another form. What does query planner does is something different. ;) Temporary table is used because you have joined tables, mysql doesn't support ordered indexes. All indexes are ascending by default. PostgreSQL supports ordered indexes. – Mladen Uzelac Dec 7 '14 at 12:45
  • Thanks Mladen. So basically you're saying go with PostgresSQL or suffer the pain that is MySQL when it comes to bad performance execution? i.e. there's nothing to do about it from here? Correct? – Adergaard Dec 7 '14 at 12:53
  • Every database products has it's advances and limits. I know PostgreSQL quite well and learning MySQL. This is the online book that will help you use indexes better: use-the-index-luke.com And will teach you about indexes in leading database sytems on the market. – Mladen Uzelac Dec 7 '14 at 13:01
  • SELECT c.*, e.* unless you want information from multinational as well. – SQB Dec 8 '14 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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