Fredrik, I haven't read all of the earlier messages, but it looks like your problem is that a query such as the following takes more than a minute to return 11 rows.
Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685; Why? Well, your explain says that no key is used, but the entire table is scanned (type='ALL'). In particular, MySQL is reading every record of A and looking to see if either A.id=560685 or B.parent=560685. Not good. Your query is equivalent to the following: Select id from art where id=560685 or parent=560685; I'd be surprised if the simpler query weren't slightly faster than the original--MySQL has more work to do on the original. It appears that, with the more complex query, you are trying to fool MySQL into using indexes for both parts of the OR. It didn't work. In an earlier mail, your explain had type='index' and key='id_3'. In that case, you evidently had an index, id_3, that contained both id and parent. So, MySQL could get all of the information form the id_3 index; therefore, it read the index instead of the table. It still read the entire index, looking to see if either A.id=560685 or B.parent=560685. Better than reading the full table, but still not good. What to do? Well, you can get the information you want in TWO queries: Select id from art where id=560685; Select id from art where parent=560685; In each of these, MySQL will surely use the appropriate index and return the result in a few milliseconds. You should be able to combine them into one query and get the same behavior: Select id from art where id=560685 UNION ALL Select id from art where parent=560685; I'd be surprised if MySQL didn't do fine on this. (You may have tried this and failed--as I said, I didn't try to read all of the earlier mail. But I'd be astonished if it weren't fast, though I suppose MySQL might try to do something fancy, knowing that the two queries are on the same table. In any event, check the two individual queries. If necessary, use a temporary table.) Then, you get to add your ORDER BY clause; presumably, it will still be fast. There was some mention in earlier mail of joins being faster than unions. That may be, but the difference should be too small to notice, unless different indexes are used. In your query above, with the inner join, MySQL is going to first consider the join and then consider the use of indexes for the where clause--so it ends up with the full table scan. HTH, Bill ===== original message follows ===== From: Fredrik Carlsson <[EMAIL PROTECTED]> To: Donny Simonton <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com Subject: Re: Max connections being used every 10-12 day. 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]
