I really appreciate your help :)
I did some cleanup of my indexes(there are a couple of them left to clean out but it takes so long time):
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 | 542437 | NULL | NULL | | BTREE | |
| art | 1 | date | 1 | date | A | 542437 | NULL | NULL | YES | BTREE | |
| art | 1 | lst | 1 | lst | A | 216 | NULL | NULL | YES | BTREE | |
| art | 1 | batch | 1 | batch | A | 183 | NULL | NULL | YES | BTREE | |
| art | 1 | batch | 2 | lst | A | 1802 | NULL | NULL | YES | BTREE | |
| art | 1 | lst_3 | 1 | lst | A | 216 | NULL | NULL | YES | BTREE | |
| art | 1 | lst_3 | 2 | parent | A | 90406 | NULL | NULL | YES | BTREE | |
| art | 1 | lst_3 | 3 | batch | A | 90406 | NULL | NULL | YES | BTREE | |
| art | 1 | parent | 1 | parent | A | 90406 | NULL | NULL | YES | BTREE | |
| art | 1 | mid | 1 | mid | A | 542437 | NULL | NULL | YES | BTREE | |
| art | 1 | date_2 | 1 | date | A | 542437 | NULL | NULL | YES | BTREE | |
| art | 1 | subc | 1 | subc | A | 54243 | NULL | NULL | YES | FULLTEXT | |
| art | 1 | mf | 1 | mf | A | 54243 | NULL | NULL | YES | FULLTEXT | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
13 rows in set (0.00 sec)
mysql> Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685 order by A.date;
+--------+--------+
| id | parent |
+--------+--------+
| 560685 | 0 |
| 560707 | 560685 |
| 560714 | 560685 |
| 560780 | 560685 |
| 560783 | 560685 |
| 560802 | 560685 |
| 560810 | 560685 |
| 560851 | 560685 |
| 560855 | 560685 |
| 561056 | 560685 |
| 561104 | 560685 |
+--------+--------+
11 rows in set (1 min 12.45 sec)
mysql> explain Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685 order by A.date;
+-------+--------+----------------+---------+---------+-------------------+--------+----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+----------------+---------+---------+-------------------+--------+----------------+
| A | ALL | PRIMARY | NULL | NULL | NULL | 542437 | Using filesort |
| B | eq_ref | PRIMARY,parent | PRIMARY | 4 | mail_archive.A.id | 1 | Using where |
+-------+--------+----------------+---------+---------+-------------------+--------+----------------+
2 rows in set (0.00 sec)
// Fredrik
Donny Simonton wrote:
Frederik, I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower.
For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3
Get rid of id, id_2, and id_3. You need to do this for everything that you have duplicates of.
Next according to your original query, the real query you should try and run should look like this:
Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or B.parent=60
order by A.date
Donny
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
