Topic awaiting preservation: One more reason mysql sucks...

"MySQL doesn't yet optimize when you search on two different keys combined with OR (Searching on one key with different OR parts is optimized quite good):" (straight out of the manual).

Now suppose I have a query like:
"SELECT b.id,b.title,b.parent FROM `thedata` as b, thedata as a WHERE (a.parent = 35) and ((b.parent = a.id) or (b.id = a.id))"

this get's all the children, and their children from a given row.
Only problem with this is, that it compares (no of. children) * (no. of all rows) rows. That can be quite a few.

Now, if I seperate it into two queries, one asking for the children, one asking for the sub children, it only compares (no of. children * no. of subchildren) for the subchildren, and only no. of children ones for the children.

Does anybody have an idea how I could get that behaviour doing just one query?
Thank you.
TP

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

well, I think that would return more rows than are intentend.

(checked, it does. thanks anyway, InI.
It's just that mysql sucks... any decent sql implementation would handle the indexes correctly... but that's what I have to work with ;-( )


edit: Now, if I had at least mysql 4.0 available... I could do a 'union' select (also commonly know as sub selects, I think). But that's still in beat. *sigh*.

[This message has been edited by Tyberius Prime (edited 12-08-2002).]

yes, mysql is very limited. but do we have an alternative? no!
mysql is a standard meanwhile on most webhosts.

The book I learned MySQL from said that MySQL is limited in its abilities, but that it was a tradeoff for maximum stability and speed. Is that correct? And if so, is it a good trade?

Its the best in speed when it comes to a wide range of queries, and it can handle large databases while staying quite stable, but I think the key to real speed is to have a good schema in place for whatever application in the first place.

I'd say MySQL is the best free database, for sure, but as I don't have time to test them all, I could be wrong. (this wasn't an invite for Postgre peeps to challenge, thanks.)

Well, all fine and dandy, and it is free, at least.

But it sucks when you can not set up a shema in a way to get the maximum possible speed, just because mysql is stupid at some query optimizations...
( Seems, I can have all the indexes I want, it won't handle the query correctly.)

so long,
Tyberius Prime

Another alternative might be

SELECT a.*, b.*

FROM thedata AS a, OUTER thedata AS b

WHERE a.id = b.parent

but I haven't tested it, and have no clue as to how it would compare against your existing query.

It also means a bit of messing around with the data once you get it out of the db.

Apparently MySQL 4 supports "union" - if you can wait that long



[This message has been edited by stinx (edited 12-09-2002).]

stynx, thanks for your help!

(I found the union myself, only to be disappointed... there seem to be quite a nice set of new features in mysql 4...)

A for your query, I think it will only return the children, not the subchildren (as a.id would be a fixed value).
I was trying to get both in one query, and while my early query works, two seperate ones would currently be more efficent (since then, mysql would use indexes for finding.)

But then, the hosting provider is charging 'by the query'... bad luck for them.

Charging by the query instead of by total processor time? Silly host.

I hope they've got reasonable pricing... I once made a commercial site that was supposed to have a message board, but the host charged by "CGI resource units" -- essentially, each CGI operation cost money. The message board (Ikonboard, specifically) would clunk out half way through each month, as we kept overrunning the limit. This is after the client upgraded to the $100/month hosting. Idiocy, no? Naturally, the client refused to switch hosts, since they'd signed a big long contract with them, had all their email and everything set up on that host...

holy cow, I've never heard of this.

Where do you host? Locally in germany?

Oops, sorry - I missed a bit out...

SELECT a.id as child_id, a.title as child_title,

b.id as subchild_id, b.title as subchild_title

FROM thedata AS a, OUTER thedata AS b

WHERE a.id = b.parent

AND a.parent = 35

It will return all the info you want - one row per subchild - with it's parent info in that row (duplicated parents if there is more than one subchild), and one row per subchildless child.

It comes out in a messy format, with a fair amount of duplicate data... but it does work off the keys

e.g. for formatting the data

$arrChildren = array();

$arrSubChildren = array();

while($row = @mysql_fetch_assoc($result)) {

    $arrChildren[$row['child_id']] = $row['child_title'];

    if($row['subchild_id']) {

        $arrSubChildren[$row['subchild_id']] = $row['subchild_title'];

    }

}

It might be ugly, but it's a way of getting the info at the cost of only one query!

Charge by the query??
Or by the processor time? Is this a common practice?

stinx, you may be named after the river of lost memories,
but you sure have some great ideas for mysql!


DL: I wouldn't call it 'common' but at least dreamhost does it.
They 'charge' by Connections * 25 + Queries * 1 for the mysql use, but you get... hm, like 20 million/month with the 20 USD package... (and 5 USD an additional million)

Now, that sounds alot, but just have a look at the numbers of the asylum, to take a representative site...
an average of 13,274 page requests per day for the asylum and 2,391 for the faq... makes about 15665 pages a day.
Multiply that by 31 = 485615.
Divide the 20 million by that and you get around 41 'conuries' (as dreamhost calls them) that you have per page.
So, say we need an average of 2 queries per page, and 3/4 of a connection (since connection can be cached somewhat)... that'd be 21.
So a site that was twice as big as the asylum (which I'd not consider a 'big' site, though it sure makes up in quality) would better be hosted else where.

so long,

Tyberius Prime

Styx is the river of death that circles Hades -- Lethe is the river of forgetfulness, which runs through it. Fun trivia fact: Mnemosyne is the river of remembrance.