テーブル上に存在しないデータの一覧を取りたい。

質問

mysql＋phpで開発を行っています。

テーブル
id
101
102
104
106
109
110

の様にデータが入っているテーブルaaaがあります。
プログラムで取得したid一覧が上記テーブルにレコードがあるのか確認するSQLは
例）select id from aaa where id in (101,102)
で取得できるということは分かっています。
ここで
select id from aaa where id in (102,103)
とやると102の1件が取得できます。

今回知りたいことは
逆に取得できなかった103という値を取得する方法はないのかという事です。

select id from aaa where id=102;
select id from aaa where id=103;
2回SQLを実行して値が戻ってこないSQL＝テーブルにそのレコードが無いというプログラムを書けばいい事は分かっています。
しかし、処理が少なくとも数千回発生してしまう予定ですのでできれば別の方法がいいと思っています。
また、in区で使っている一覧はテーブルには入っておらず、また、こちらも数千個あるので、一時テーブルに入れるといってもかなりの時間がかかってしまうと思います。

何か一発で取得できるいい方法などありますでしょうか？
ちなみにaaaテーブルは5万件ほどなので全件php側でメモリに展開してサーチも避けたいです

よろしくお願いいたします。

回答No.6

mpro-gram

根本の比較対照の「数千個の非テーブル上のデータ」　ってどんなデータで、もともとはどこに保持されてるんでしょうか？
毎回php側のオンメモリ上で作成しているのでしょうか？それで、php側で、数千個保持したくないから、数千回のループで毎回insertクエリ発行したのですか？
クエリ数千回発行は、selectにしろinsertにしろどうやっても非実用でしょう。
せめて、tsv ファイルに書きだして、load data local 文で一括insert くらいのことやらないと。tsvファイルに書き出すまでの部分ですでに相当の時間がかかるなら、そのデータをphpで毎回生成すること自体に問題あり。
いずれにしても、比較用の数千個のデータ作成が、毎回作らなきゃならないのかの方を再検討の必要あり。ランダム取り出しってだけなら、MySQLにやらせることもできるんだし。

回答No.5

IDii24

サイズを１Gに？なぜにそのようなデカイテーブルにする必要があるのでしょうか？データーって数百万件もあるのでしょうか？
サイズは10万件でも数メガで十分です。そしてMAXを５００Mぐらいにすれば殆どは十分だと思いますけど？

回答No.4

piroin654

No2の説明も反対になっていました。

＞でbbbに存在しないaaaのidを取り出すしかないのでは？

は

でaaaに存在しないbbbのidを取り出すしかないのでは？

です。

つまり、

select id from bbb where not exists(select id where aaa.id=bbb.id)

を使って、aaaに存在しないbbbのidを取り出す、ということです。

回答No.3

piroin654

No2です。
No2はテーブル名が反対になっていました。

＞select id from aaa where not exists(select id where aaa.id=bbb.id)

select id from bbb where not exists(select id where aaa.id=bbb.id)

です。

回答No.2

piroin654

要するに、
＞in区で使っている一覧はテーブルには入っておらず、・・・▲
このデータの取り扱いをどうするかなのでは？

メモリは使いたくない
数千回のループ処理はしたくない

何が残っているかといえば、
CreateTableで▲のデータをテーブルに取り込み、テーブルをbbbとして、

select id from aaa where not exists(select id where aaa.id=bbb.id)

でbbbに存在しないaaaのidを取り出すしかないのでは？
上記のSQLで「数千回のループ処理」のかわりになりますが。

回答No.1

IDii24

Tempテーブルをプログラムで作成して、そこにinsertして、既存のテーブルとOuter Joinで結びつかない値を取得すればよいだけです。

お礼

ありがとうございます。
tempテーブルを作ったのですが、なぜかtempテーブルの作成に妙に時間がかかってしまい実用に耐えられないという問題が発生しています。
tmp_table_sizeとmax_heap_table_sizeはどちらも１Gに設定しているため明らかに足りているはずなのですが…