What began as figuring out why some existing reporting procedures were taking longer than I'd like quickly went downhill.

"Gee, why is this one query accounting for 98.5% of the workload?"
Lessons learned:
Making your most selective WHERE conditions part of AND/OR decisions that refer to both sides of your self-join = BAD DEVELOPER, NO BISCUIT. Even more so when every other part of the WHERE clause gives you a selectivity of maybe 80% at best, and your selective column has no index anyway.

Oh, did I mention there's a report page that calls this procedure about 7 times in a row? At least there aren't any cursors.

db2:

Making your most selective WHERE conditions part of AND/OR decisions that refer to both sides of your self-join = BAD DEVELOPER, NO BISCUIT.

Do you mean something like this?

select a.x, b.y
from a, b
where a.m = b.n or a.p = b.q

as opposed to

select a.x, b.y
from a join b on a.m = b.n
union
select a.x, b.y
from a join b on a.p = b.q

Could anyone tell me, why, even though I have indexed each column, and explain select count( distinct visits) from stats says it's going to use it, still select count( distinct visits) from stats takes several seconds (while the result is 442, which in most brain-dead way I could invented leads to 442 btree records being read?). I'm referring to MySQL server. Is it so hard to determine the number of nodes in the btree? Is it due to some parallel issues?

And the query I was really interested in was Select Sum(visits) From stats, which I was sure should take about 441 additions and multiplications, but I'll forgive the optimizer lack of arithmetic knowledge...but still...what are those btrees for, then - just to find a specific row ? What a waste of potential potential

Close. It's sort of like this:

SELECT subtotal
FROM orders AS orders1 LEFT JOIN orders AS orders2
ON orders1.orignum = orders2.ordnum
WHERE
--a few business logic conditions on fields in orders1 with lousy selectivity--
AND ((orders1.orderdate BETWEEN @startDate AND @endDate) OR (orders1.origtype = 0 AND orders1.docdate BETWEEN @startDate AND @endDate)
OR orders2.orderdate BETWEEN @startDate AND @endDate)

That's kind of anonymized and simplified, but you get the idea. Alone, either of these two conditions is perfectly selective, and will go to an index in the optimizer:

(orders1.orderdate BETWEEN @startDate AND @endDate) OR (orders1.origtype = 0 AND orders1.docdate BETWEEN @startDate AND @endDate)

orders2.orderdate BETWEEN @startDate AND @endDate

But when you join the two with an OR... Yeah. The necessity of truth for either condition depends on the truth of the other, so the indexes (if we actually had any on that column) sit and collect dust. Instead, it has to perform a cross-join on the two tables first before it can filter the records. Whee. I'm thinking I'm going to need some indexed view trickery and splitting things into clever UNIONs to pull this one off. Unless anybody's got a better idea that'll work on SQL Server 2000. :)

[Obvious joke about mysql not being a real database]
[Lame recommendation to try postgresql, oracle, sqlite, etc]

Nick:

[Obvious joke about mysql not being a real database]
[Lame recommendation to try postgresql, oracle, sqlite, etc]

[obligatory varchar2 dig]

db2:

Nick:

[Obvious joke about mysql not being a real database]
[Lame recommendation to try postgresql, oracle, sqlite, etc]

[obligatory varchar2 dig]

[varchar2 retort]
[general, unrelated stab at the uselessness of VB, in any situation, and how <programming-language> solves all my problems]

Fred:

db2:

Nick:

[Obvious joke about mysql not being a real database]
[Lame recommendation to try postgresql, oracle, sqlite, etc]

[obligatory varchar2 dig]

[varchar2 retort]
[general, unrelated stab at the uselessness of VB, in any situation, and how <programming-language> solves all my problems]

<generic comment about finding salvation in xml/>

nonDev:

Fred:

db2:

Nick:

[Obvious joke about mysql not being a real database]
[Lame recommendation to try postgresql, oracle, sqlite, etc]

[obligatory varchar2 dig]

[varchar2 retort]
[general, unrelated stab at the uselessness of VB, in any situation, and how <programming-language> solves all my problems]

<generic comment about finding salvation in xml/>

[ob. ref to http://efw.livejournal.com/ followed by meaningless reference to a famous WTF posting]