slow query using <> (not equal)

[Edward Lipchus]

I have a table with about 500,000 rows. There are multiple rows per patient. Each row has the patient's sex. I want to know where a patient's gender is not the same on all their records. A simple query, such as "SELECT * FROM ecg WHERE pid < 100000 LIMIT 100;" executes almost instantly. But this query...

SELECT a.pid, a.sex, b.sex
FROM ecg a, ecg2 b
WHERE a.pid = b.pid AND a.sex <> b. sex
LIMIT 10;
+---------+------+------+
| pid | sex | sex |
+---------+------+------+
| 0014931 | 0 | 1 |
| 0779796 | 0 | 1 |
| 0355518 | -1 | 1 |
| 0529857 | -1 | 0 |
| 2726540 | 1 | 0 |
| 2918733 | -1 | 0 |
| 0059461 | -1 | 1 |
| 0059461 | -1 | 1 |
| 0059461 | -1 | 1 |
| 0628151 | -1 | 0 |
+---------+------+------+
10 rows in set (8.02 sec)

...literally takes days for the full table. It takes over 8 seconds for just 10 rows, and I figure almost a week for the entire table.

If I make the simple change of making the <> an =, it executes in a flash.

I originally tried this as a self join (i.e. ...FROM ecg a, ecg b ...) and it takes about the same time as the above query, which is against twin tables.

Any ideas what I'm doing wrong?

mysql> describe ecg; /* exact same description, exact same data for ecg2 */
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| empty01 | char(1) | YES | | NULL | |
| db_name | char(5) | YES | | NULL | |
| dept | smallint(6) | YES | | NULL | |
| inst | smallint(6) | YES | | NULL | |
| pid | char(16) | YES | MUL | NULL | |
| ecg_datetime | char(20) | YES | | NULL | |
| pname | varchar(30) | YES | | NULL | |
| last_nm | varchar(30) | YES | MUL | NULL | |
| frst_nm | varchar(30) | YES | MUL | NULL | |
| frst_init | char(1) | YES | | NULL | |
| new_pname | varchar(30) | YES | | NULL | |
| age | smallint(6) | YES | | NULL | |
| sex | smallint(6) | YES | | NULL | |
| department | varchar(8) | YES | | NULL | |
| current_age | smallint(6) | YES | | NULL | |
| fixed | char(1) | YES | | NULL | |
| row_nbr | int(11) | NO | PRI | NULL | auto_increment |
+--------------+-------------+------+-----+---------+----------------+

mysql> explain select a.pid, a.sex, b.sex from ecg a, ecg2 b where a.pid = b.pid
and a.sex <> b. sex limit 10;
+----+-------------+-------+------+---------------+---------+---------+--------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+--------------+--------+-------------+
| 1 | SIMPLE | b | ALL | pid_idx | NULL | NULL | NULL | 522863 | |
| 1 | SIMPLE | a | ref | pid_idx | pid_idx | 17 | tm_arc.b.pid | 1 | Using where |
+----+-------------+-------+------+---------------+---------+---------+--------------+--------+-------------+
2 rows in set (0.02 sec)