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
>
>
>
>
