0

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  
ORDER BY salary DESC 
LIMIT 0, 24
2
  • 1
    Please provide table structure, input data and expected result. Dec 7, 2014 at 10:16
  • 1
    And add mysql version, dbengine and indexes you are using Dec 7, 2014 at 12:52

1 Answer 1

0

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')
ORDER BY salary DESC 
LIMIT 0, 24
5
  • 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, 2014 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. Dec 7, 2014 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, 2014 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. Dec 7, 2014 at 13:01
  • SELECT c.*, e.* unless you want information from multinational as well.
    – SQB
    Dec 8, 2014 at 7:37

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.