Subqueries bogging me down

[noyearzero]

I have 2 tables (a one to many relationship) the first table has 50 or so rows the second has about 2.6 million.

i'm doing some queries that pull all 50 rows of the first table and generates some stats on them based on info from the second table. my basic queries look like this

SELECT t1.*, (SELECT COUNT(t2.column) FROM t2 WHERE t2.column2 = '1') as stat1 FROM t1 ORDER BY stat1

I find that when stat1 is around 50 i get great performance, however when stat1 is much larger (50000) the query takes forever to run. i would have thought that the performance would be the same because in both cases the query loops though 2.6 million rows for each of the 50 rows in t1 and the only thing that gets returned is an integer.

what am i missing?

[eeek]

Have you done an EXPLAIN on the query?

[LifeinAsia]

Do you have an index on t2.column2?

[eeek]

Do you have an index on t2.column2?

While that might help, it's best to look at what EXPLAIN says before you go throw indices at a table.

[noyearzero]

i did do an EXPLAIN, but it was the first time and couldn't really make heads or tails of it.

[eeek]

couldn't really make heads or tails of it.

So do some reading and learn to understand what it is saying.

[LifeinAsia]

While that might help, it's best to look at what EXPLAIN says before you go throw indices at a table.

Might? Um, if you're frequently doing a WHERE on a field in a table that has 2.6 million records, you don't need EXPLAIN to tell you that you SHOULD have an index on it.

[whoisgregg]

Sometimes writing the query in a different way can have dramatic results. noyearzero, If you'd like to post the results of the EXPLAIN here, we'd be happy to look at it.

When dealing with tables with millions of rows, it can take some time to add new a new index. Plus the additional storage requirements make it worth investigating before acting.

[eeek]

Might?

Yes, might. It depends on the query plan that gets generated.

if you're frequently doing a WHERE on a field in a table that has 2.6 million records, you don't need EXPLAIN to tell you that you SHOULD have an index on it.

That depends on what else is in the WHERE and what sorts are involved. The COUNT(t2.column) could also be expensive depending on what database system he's using. EXPLAIN doesn't take much effort; it's a lot faster than creating an index that might not be useful.