mysql> describe art; +---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | parent | int(11) | YES | MUL | NULL | | | bodyid | int(11) | YES | | NULL | | | lst | varchar(80) | YES | MUL | NULL | | | mf | varchar(80) | YES | | NULL | | | mt | varchar(80) | YES | | NULL | | | subc | varchar(200) | YES | MUL | NULL | | | sdate | varchar(45) | YES | | NULL | | | batch | varchar(80) | YES | MUL | NULL | | | mgid | varchar(90) | YES | | NULL | | | date | datetime | YES | MUL | NULL | | +---------+--------------+------+-----+---------+----------------+ 11 rows in set (0.12 sec)
mysql> show index from art;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| art | 0 | PRIMARY | 1 | id | A | 405011 | NULL | NULL | | BTREE | |
| art | 1 | id | 1 | id | A | 405011 | NULL | NULL | | BTREE | |
| art | 1 | date | 1 | date | A | 405011 | NULL | NULL | YES | BTREE | |
| art | 1 | lst | 1 | lst | A | 213 | NULL | NULL | YES | BTREE | |
| art | 1 | id_2 | 1 | id | A | 405011 | NULL | NULL | | BTREE | |
| art | 1 | id_2 | 2 | parent | A | 405011 | NULL | NULL | YES | BTREE | |
| art | 1 | lst_2 | 1 | lst | A | 213 | NULL | NULL | YES | BTREE | |
| art | 1 | lst_2 | 2 | parent | A | 57858 | NULL | NULL | YES | BTREE | |
| art | 1 | lst_2 | 3 | date | A | 405011 | NULL | NULL | YES | BTREE | |
| art | 1 | batch | 1 | batch | A | 141 | NULL | NULL | YES | BTREE | |
| art | 1 | batch | 2 | lst | A | 1177 | NULL | NULL | YES | BTREE | |
| art | 1 | lst_3 | 1 | lst | A | 213 | NULL | NULL | YES | BTREE | |
| art | 1 | lst_3 | 2 | parent | A | 57858 | NULL | NULL | YES | BTREE | |
| art | 1 | lst_3 | 3 | batch | A | 67501 | NULL | NULL | YES | BTREE | |
| art | 1 | id_3 | 1 | id | A | 405011 | NULL | NULL | | BTREE | |
| art | 1 | id_3 | 2 | parent | A | 405011 | NULL | NULL | YES | BTREE | |
| art | 1 | id_3 | 3 | date | A | 405011 | NULL | NULL | YES | BTREE | |
| art | 1 | parent | 1 | parent | A | 57858 | NULL | NULL | YES | BTREE | |
| art | 1 | subc | 1 | subc | A | 40501 | NULL | NULL | YES | FULLTEXT | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
19 rows in set (0.04 sec)
The query:
Select A.id, A.parent, B.id, B.parent from art A inner join art B using (id)
order by A.date;
Would return several hundered thousend answers so i added a where option, i dont know if this was what you had in mind.
The way i used your query it should be the same thing as:
Select A.id, A.parent from art A where A.id=60 or A.parent=60 order by A.date;
mysql> explain Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or A.parent=60 order by A.date;
+-------+--------+----------------------------------+---------+---------+-------------------+------+------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+----------------------------------+---------+---------+-------------------+------+------------------------------------------+
| A | index | PRIMARY,id,id_2,id_3,parent | id_3 | 18 | NULL | 37 | Using where; Using index; Using filesort |
| B | eq_ref | PRIMARY,id,id_2,id_3 | PRIMARY | 4 | testdb.A.id | 1 | Using index |
+-------+--------+----------------------------------+---------+---------+-------------------+------+------------------------------------------+
2 rows in set (0.00 sec)
The table and index structure are from the production database but the query was executed on my test database (same structure but not the same amount of rows)
// Fredrik
Donny Simonton wrote:
The inner join should ALWAYS return a faster result than the union, if you have the indexes correctly.
Can you send me the explain of the inner join version and also the full table structure and indexes on the table?
This should be fairly easy to solve.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
