Accelerating this Big Intro

Question

EDIT : An error occurred in the next question that explains the observations. I could remove the question, but it might be useful to someone. The error was that the actual request being executed on the server was SELECT * FROM t(which was stupid) when I thought it was working SELECT t.* FROM t(which makes all the difference). See tobyobrian's answer and comments thereto.

---

My query is too slow in a circuit situation as follows. The table thas rows of data indexed with t_id. tIt adjoins tables xand ythrough connection tables t_xand t_y, each of which contains only foreign keys necessary for JOIN:

CREATE TABLE t (
  t_id INT NOT NULL PRIMARY KEY,
  data columns...
);
CREATE TABLE t_x (
  t_id INT NOT NULL,
  x_id INT NOT NULL,
  PRIMARY KEY (t_id, x_id),
  KEY (x_id)
);
CREATE TABLE t_y (
  t_id INT NOT NULL,
  y_id INT NOT NULL,
  PRIMARY KEY (t_id, y_id),
  KEY (y_id)
);

t, , .

SELECT t.* FROM t
LEFT JOIN t_x ON t_x.t_id=t.t_id
LEFT JOIN t_y ON t_y.t_id=t.t_id
WHERE t_x.t_id IS NULL OR t_y.t_id IS NULL
INTO OUTFILE ...;

t 21 M , t_x t_y 25 M . , , , .

MyISAM, , , t_x t_y. t_x.MYI t_y.MYI 1,2 , , PRIMARY- LOAD INDEX INTO CACHE'ed.

, mysqld 1% CPU, 5, mysqld - 250 k. , IO - mysqld, t_x.MYI t_x.MYD.

:

• mysqld .MYD?

• mysqld t_x t_y?

- t_x t_y PRIMARY, ?

EDIT: :

| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows     | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-----------+----------+-------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL    | NULL    | NULL      | 20980052 |             | 
|  1 | SIMPLE      | t_x   | ref  | PRIMARY       | PRIMARY | 4       | db.t.t_id |   235849 | Using index | 
|  1 | SIMPLE      | t_y   | ref  | PRIMARY       | PRIMARY | 4       | db.t.t_id |   207947 | Using where | 
+----+-------------+-------+------+---------------+---------+---------+-----------+----------+-------------+

Answer 1

1 , , EXPLAIN:

t. *, MYD - , , , .

, , , , .

, , , - t_x, 3 x_y t. * 3 . , where , , , , . select distinct , .

Answer 2

- - , "" " " .

SELECT t.* FROM t a
Where not exists (select 1 from t_x b
                  where b.t_id = a.t_id)
or not exists (select 1 from t_y c
                where c.t_id = a.t_id);

Answer 3

This might be a little more efficient:

SELECT * 
FROM t
WHERE t.id NOT IN (
  SELECT DISTINCT t_id
  FROM t_x
  UNION
  SELECT DISTINCT t_id
  FROM t_y
);