古いMySQL(4.1)と格闘したメモ

以下、大したことじゃないし、正解とも思えないので、よっぽど暇な方以外は読まないで下さい。
２つのテーブル user_listとmailboxがあって、それらのエントリーの関係をまとめた第3のテーブルcheck_listがあるとする。check_list.uid は user_list.idと対応し、check_list.mail_idは mailbox.idと対応する。こんな↓感じ。

CREATE TABLE 'user_list' (
'id'     int,
'user_name'  varchar(256),
'email' varchar(256)
);

CREATE TABLE 'mailbox' (
'id'     int,
'addr' varchar(256),
'content' varcahr(256)
);

CREATE TABLE 'check_list' (
'u_id'     int,
'mail_id'  int,
'relation' varchar(256)
);

ここで、ややこしいのは、user_listもmailboxもメールアドレスを含んでいること。

やりたかったこと

user_list.email = mailbox.addr の関係にある check_listのエントリを探し出し、そのエントリのmail_idに相当する mailboxのエントリが1個しかないケースを検索したい。

やってみたこと

こんなSQL文になった。

SELECT check_list.mail_id, COUNT(*), mailbox.addr 
FROM check_list, mailbox 
WHERE check_list.mail_id = mailbox.id 
AND check_list.mail_id IN 
 (SELECT check_list.mail_id  
  FROM user_list, mailbox, check_list  
  WHERE check_list.u_id = maia_users.id  
  AND check_list.mail_id = mailbox.id
  AND user_list.user_name = mailbox.addr  
  AND check_list.relation ='1') 
GROUP BY check_list.mail_id;

実は「1個しかないケース」というのをちゃんと書けなかったのだが、これでも、サブクエリーの結果がキャッシュされないのか、えらく遅かった。

本当はこんなふうに書きたかった

これもえらく遅くて、結局一度も完了しなかった。

SELECT check_list.mail_id, mailbox.addr
FROM check_list, mailbox 
WHERE check_list.mail_id = mailbox.id 
AND (check_list.mail_id, 1, mailbox.addr) IN 
(SELECT check_list.mail_id, COUNT(*), mailbox.addr 
  FROM check_list, mailbox 
  WHERE check_list.mail_id = mailbox.id 
  AND check_list.mail_id IN 
   (SELECT check_list.mail_id  
    FROM user_list, mailbox, check_list  
    WHERE check_list.u_id = maia_users.id  
    AND check_list.mail_id = mailbox.id
    AND user_list.user_name = mailbox.addr  
    AND check_list.relation ='1') 
  GROUP BY check_list.mail_id);

VIEWが記述できるようなデータベースを使うか、一時テーブルを使えばよかったのだろう。
つまり、最初のクエリの結果を、テーブルに格納して、それに対してシンプルなクエリを実行すればよかったのだと思う。(試してはいない）
さすがにこうすれば、temp1の内容を再計算したりはすまい。
参照：MySQL :: MySQL 4.1 リファレンスマニュアル :: 6.5.3 CREATE TABLE 構文

CREATE TEMPORALY TABLE temp1
SELECT check_list.mail_id AS t_id, COUNT(*) AS t_count, mailbox.addr AS t_addr
FROM check_list, mailbox 
WHERE check_list.mail_id = mailbox.id 
AND check_list.mail_id IN 
 (SELECT check_list.mail_id  
  FROM user_list, mailbox, check_list  
  WHERE check_list.u_id = maia_users.id  
  AND check_list.mail_id = mailbox.id
  AND user_list.user_name = mailbox.addr  
  AND check_list.relation ='1') 
GROUP BY check_list.mail_id;

SELECT t_id, t_addr
FROM temp1 
WHERE t_count = 1;