Slow query because of "or"

[pederdin]

I have two queries:

SELECT distinct(m.custid)
FROM member m,
private_friends p
WHERE m.pay=0

SELECT distinct(m.custid)
FROM member m,
private_friends p
WHERE (m.custid=p.friend_id and p.owner_id=1119)

Each of them work fine. But if I combine them with an or statement:

SELECT distinct(m.custid)
FROM member m,
private_friends p
WHERE m.pay=0 or
(m.custid=p.friend_id and p.owner_id=1119)

it gets really slow. Why's that?

[Sxooter]

Well yeah, you're creating an unconstrained join. You need an AND not an OR there, otherwise you'll be joining EVERY row against EVERY other row. i.e. cartesian product explosion.

New query:

SELECT distinct(m.custid)
FROM member m,
private_friends p
WHERE (m.pay=0 or p.owner_id=1119)
AND
m.custid=p.friend_id

[mtimdog]

The above query might be valid if the first query had
m.custid=p.friend_id
in it.

this probly won't work, but here's a shot

Select distinct (m.custid) from member m left join private_friends on m.custid = p.friend_id where (m.pay=0 or p.owner_id=1119).

Since it's a left join, I think that it should work with the where clause.

[Sxooter]

Originally posted by mtimdog
The above query might be valid if the first query had
m.custid=p.friend_id
in it.

Look again, it's the second where clause term in the third query. The fact that he left it out in the first query just points out that he's not real good with joins yet. That first query was still an unconstrained join and needed to be fixed.

but I do admit he proably wants a left join.

[mtimdog]

Sxcooter:
Notice the complete where clause

WHERE m.pay=0 or
(m.custid=p.friend_id and p.owner_id=1119)

precedence with parentheses

a or (b and c) does not equal (a or c) and b

distribution of it would be

(a or b) and (a or c)

I think that he doesn't need the second table in the first query...hence my proposed query shouldwork.

[Sxooter]

mtimdog:

Look again:

WHERE m.pay=0 or
(m.custid=p.friend_id and p.owner_id=1119)

Note that m.custid=p.friend_id

is the join qualifier. The original poster's first query was unconstrained, and this one makes NO SENSE WHATSOEVER. oring a join qualifier will result in a partial cartesian product, which I'm sure the original poster didn't want.

[mtimdog]

Why does it not make any sense?

From the original 2 queries, it looks like he's trying to get a distinct count on customers where they have paid 0
OR
a friend of theirs has an owner id of 1119.

Like I said, the first query does not need the private_friends table as such stated (because there is no new information from the table, and no constraint on the table)

[nippala]

MySQL (nor any other D😎 can't use indexes if you use OR-clause. And sometimes you can't avoid using OR. Once I made a boolean search engine, and using OR-clause in search string forced me to use OR as well in the query. But then I realised that running several queries instead of one OR was a LOT faster! With one OR -query the query took about 1 second, and with several AND queries it took around 0.01 seconds.

Dirty? - maybe, but if it works better, why not make it that way? So, if you are handling big amounts of data it might be useful to test which way is more effective.

[Sxooter]

Originally posted by nippala
MySQL (nor any other D😎 can't use indexes if you use OR-clause.

Sorry, this is absolutely, positively, 110% wrong. It is so wrong it's not even funny. I know of only one database that can't use indexes on ORed where clauses, and it's MySQL, the supposed "fastest" open source database. And it is, until you ask it to do something even marginally challenging, then it starts crawling.

From postgresql:
explain analyze select * from rax where corp_acct_id='0005902554' or corp_acct_id='0005903322';

Index Scan using rax_corp_acct_id_dx, rax_corp_acct_id_dx on rax (cost=0.00..27.15 rows=23 width=714)(actual time=0.06..1.18 rows=38 loops=1)
Total runtime: 1.28 msec

Note the time, on an 80,000 row table there. And this is on 7.2, which is about 2 years old.

PostgreSQL ain't slow no more.

Is obviously using an index. Most databases can use indexes on ORed qualifiers.

[Sxooter]

Originally posted by mtimdog
Why does it not make any sense?

From the original 2 queries, it looks like he's trying to get a distinct count on customers where they have paid 0
OR
a friend of theirs has an owner id of 1119.

Like I said, the first query does not need the private_friends table as such stated (because there is no new information from the table, and no constraint on the table)

Because it makes it an unconstrained join! Look at it. Basically, we'll get every row in the one table multiplied by every row in the other table, plus the join output.

If he uses my query, he gets what you're saying, if he uses your or his query, he gets back xy rows where x = count() from one table, and y = count(*) from the other.

[Sxooter]

I just looked at your query again, and yours and mine are pretty much the same except yours is a left join, which he may or may not (probably would) want. You just use join on syntax and mine is in the where clause, which of course, you can't do for a left join. so, if he wanted a left join he would need your query, which is basically mine converted to join on syntax and using a left join.

but, HIS query is utterly wrong and would produce a cartesian product I'm sure he doesn't actually want.

[csn]

Mysql does appear to use indexes with OR:

The following WHERE clauses use indexes:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
/ index = 1 OR index = 2 /
... WHERE index=1 OR A=10 AND index=2
/ optimized like "index_part1='hello'" /
... WHERE index_part1='hello' AND index_part_3=5
/ Can use index on index1 but not on index2 or index 3 /
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

These WHERE clauses do not use indexes:

/* index_part_1 is not used */

... WHERE index_part2=1 AND index_part3=2
/ Index is not used in both AND parts /
... WHERE index=1 OR A=10

/ No index spans all rows /
... WHERE index_part1=1 OR index_part2=10

Sometimes MySQL will not use an index, even if one is available. One way this occurs is when the optimizer estimates that using the index would require MySQL to access more than 30% of the rows in the table. (In this case, a table scan is probably much faster, because it will require many fewer seeks.) However, if such a query uses LIMIT to only retrieve part of the rows, MySQL will use an index anyway, because it can much more quickly find the few rows to return in the result.

http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html

I guess "index_part2" is the second part in a multi-column index and "index2" would be a single-column index. Mysql's docs are often clear as mud ;P.

[pederdin]

Thank you for all your help.

And a special thanks to mtimdog for solving the problem. I am new to joins and mixed them up when trying to solve it. Your solution works like a charm.

And Sxooter, you're right. I now understand what was wrong in the first solution. I actually hadn't looked in two what actually happended when I made it but now at least I've learned about cartesian product explosion...

Thank you!

[nippala]

Originally posted by Sxooter

From postgresql:
explain analyze select * from rax where corp_acct_id='0005902554' or corp_acct_id='0005903322';

Index Scan using rax_corp_acct_id_dx, rax_corp_acct_id_dx on rax

Is obviously using an index. Most databases can use indexes on ORed qualifiers. [/B]

Sure they can if you are using just the columns that are in the indexes. Of course it does an index scan. But also Oracle tends to use Full Table Scan if you use OR wrongly.

[Sxooter]

Nippala, can MySQL use a multi-column index for these kinds of things? and if it can, can it use them on both myisam and innodb table types?

[ultraslacker]

Sure they can if you are using just the columns that are in the indexes. Of course it does an index scan.

No, it might do an index scan. Depends on the what is deemed the more efficient plan.

[Sxooter]

Ultraslacker, do you know HOW MySQL determines if it's likely to pull enough of the table to make a seq scan a win? Does it use something like PostgreSQL's analyze / stats storage that requires user intervention to run analyze to keep the stats up to date?

[csn]

IIRC mysql has a "cardinality" property for each index. You're supposed to regularly run analyze (and check, optimize, repair, etc.) on tables that are inserted/updated/deleted. LOL, have to point this out next time a mysql zealot tries to deride pgsql for its vacuuming.

Looking at a table in phpmyadmin, there's something under "Row statistic" - "next autoindex - 10". Never heard of it before.