StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSelect records from table where field not in left join of different table in MySql
text
Body
copied!<p>Struggling to get this at any sensible run time. I have three tables:</p> <p>temp_company</p> <ul> <li>id (PRIMARY KEY), number (KEY), s_code (KEY)</li> </ul> <p>company</p> <ul> <li>id (PRIMARY KEY), number (KEY)</li> </ul> <p>company_scode</p> <ul> <li>company_id (UNIQUE on company_id and code), code (KEY) There is also a foreign key between code and code the code_description table. There is also a foreign key between company_id and the id in the company table</li> </ul> <p>I need to match up the temp_company table to the company table on the number field, I then want to check if the s_code in temporary table exists for the company in the company_scode table, if it doesn't then select that row.</p> <p>So far I have:</p> <pre><code>SELECT temp_company.s_code FROM temp_company WHERE temp_company.s_code NOT IN (SELECT code FROM company LEFT JOIN company_scode ON company.id = company_scode.company_id WHERE company.number = temp_company.number ) </code></pre> <p>but this is very slow, I would appreciate a better way to select every temp_company record where it's s_code does not exist in the many to many relationship between company and company_scode.</p> <p><strong>* UPDATE <em>*</em></strong></p> <p>Thank you to Loc and Ollie for your answers, these are still taking a very long time (I left Ollie's for 8 hours and it was still going).</p> <p>In terms of index's I have updated above with info. I've put the explains below for the two answers to try to shed some light and hopefully get this faster.</p> <p>EXPLAIN for Ollie's answer:</p> <pre><code>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+--------------------+------------+-------+---------------+------------+---------+-----------------------+---------+--------------------------+ | 1 | PRIMARY | tc | ALL | (NULL) | (NULL) | (NULL) | (NULL) | 3216320 | | +----+--------------------+------------+-------+---------------+------------+---------+-----------------------+---------+--------------------------+ | 1 | PRIMARY | <derived2> | ALL | (NULL) | (NULL) | (NULL) | (NULL) | 2619433 | Using where; Not exists | +----+--------------------+------------+-------+---------------+------------+---------+-----------------------+---------+--------------------------+ | 2 | DERIVED | s | index | company_id | code | 62 | (NULL) | 2405379 | Using index | +----+--------------------+------------+-------+---------------+------------+---------+-----------------------+---------+--------------------------+ | 2 | DERIVED | c | eq_ref| PRIMARY | PRIMARY | 4 | mydbname.s.company_id | 1 | | +----+--------------------+------------+-------+---------------+------------+---------+-----------------------+---------+--------------------------+ </code></pre> <p>EXPLAIN for Loc's answer:</p> <pre><code>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+--------------------+-------+-------+---------------+------------+---------+---------------+---------+--------------------------+ | 1 | PRIMARY | tc | ALL | (NULL) | (NULL) | (NULL) | (NULL) | 3216320 | Using where | +----+--------------------+-------+-------+---------------+------------+---------+---------------+---------+--------------------------+ | 2 | DEPENDENT SUBQUERY | c | index | (NULL) | number | 63 | (NULL) | 3189756 | Using where; Using index | +----+--------------------+-------+-------+---------------+------------+---------+---------------+---------+--------------------------+ | 2 | DEPENDENT SUBQUERY | cc | ref | company_id | company_id | 4 | mydbname.c.id | 1 | Using where; Using Index | +----+--------------------+-------+-------+---------------+------------+---------+---------------+---------+--------------------------+ </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload