Closed Thread Icon

Topic awaiting preservation: One more reason mysql sucks... Pages that link to <a href="http://ozoneasylum.com/backlink?for=12547" title="Pages that link to Topic awaiting preservation: One more reason mysql sucks..." rel="nofollow" >Topic awaiting preservation: One more reason mysql sucks...\

 
Author Thread
Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 12-08-2002 15:23

"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

InI
Paranoid (IV) Mad Scientist

From: Somewhere over the rainbow
Insane since: Mar 2001

posted posted 12-08-2002 15:55

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.

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 12-08-2002 17:35

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).]

GRUMBLE
Paranoid (IV) Mad Scientist

From: Omicron Persei 8
Insane since: Oct 2000

posted posted 12-08-2002 18:54

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

Perfect Thunder
Paranoid (IV) Inmate

From: Milwaukee
Insane since: Oct 2001

posted posted 12-08-2002 21:44

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?

genis
Paranoid (IV) Inmate

From: Dallas, TX
Insane since: Aug 2002

posted posted 12-09-2002 10:52

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.)

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 12-09-2002 16:58

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

stinx
Bipolar (III) Inmate

From: London, UK
Insane since: Apr 2002

posted posted 12-09-2002 18:29

Another alternative might be

code:
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).]

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 12-09-2002 19:03

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.

Perfect Thunder
Paranoid (IV) Inmate

From: Milwaukee
Insane since: Oct 2001

posted posted 12-09-2002 19:38

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...

genis
Paranoid (IV) Inmate

From: Dallas, TX
Insane since: Aug 2002

posted posted 12-09-2002 23:11

holy cow, I've never heard of this.

Where do you host? Locally in germany?

stinx
Bipolar (III) Inmate

From: London, UK
Insane since: Apr 2002

posted posted 12-10-2002 17:05

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

code:
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

code:
$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!


DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 12-10-2002 18:09

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

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 12-10-2002 18:34

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

Perfect Thunder
Paranoid (IV) Inmate

From: Milwaukee
Insane since: Oct 2001

posted posted 12-10-2002 19:48

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.

« BackwardsOnwards »

Show Forum Drop Down Menu