Hi,
My original query was a UNION query :) and that one is really fast. The problem i had was that every 8-12 day mysql sad that all of my max_connections was in use. I think i will stick with my UNION query it seems faster.
// Fredrik.
Bill Easton wrote:
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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
