7/26 Hello SQL experts, I have two tables, A and B. A has 1/2 million
rows and B has 2 million rows. The relationship is A.id=B.A_id.
Whenever I have something like:
SELECT A.id, AVG(B.val) FROM A,B WHERE A.date<'2008-01-01' AND
A.id=B.A_id GROUP BY A.id;
It takes about 20-30 seconds. Anyone know why? I've already
indexed all the ids.
\_ Do you have an index on A.date?
\_ Yes I do. I found out something new. I did a
SELECT COUNT(*) FROM ... <rest is the same here>
and I got 63 million rows. I'm guessing something
is really screwy here, do you have a clue?
How about INNER JOIN, is that faster? How do I use it?
\_ The syntax you gave is an inner join by default in most
databases. In mysql or pgsql at least, try adding "explain"
to the beginning of your select. This will tell you which
keys it's trying to use, how many rows each step gets
filtered down to, etc. --dbushong
\_ Hello I'm the op. I've reduced my problem to the following.
Say I have 4 tables, A, B, C, and D. When I do a
SELECT COUNT(*) and I join A and B, it is pretty fast.
When I join A, B, and C, it is twice as slow. And when
I join all of them, it is FOUR times as slow. I've made
sure that all the joint columns are INDEXed. Why is this
happening?
\_ You're specifying 3 join conditions, right? You have non-unique
indexes on the foreign keys and unique primary keys?
\_ Thanks Dave. Basically, column A.id is unique, column B.id
is not unique. A.id maps to B.id. Similarly, C.id is not
not unique, but also maps to B.id. Is this the reason?
I need a one to many mapping and I don't know how to get
around it.
\_ if you're using mysql, mysql can only use one index per table.
if you're joining on multiple columns, that could be your problem |
http://csua.com/feed/