Post #294,813
10/18/07 10:01:39 PM
|
Here we go again
MySQL could not handle simple correlated sub-queries.
Let me rephrase that.
MySQL SUCKS as handling simple correlated sub-queries. Turned what should be a 3 second query into 3 minutes.
So I tried postgresql.
And found it was generally faster in all my particular query cases, and handled the subqueries without problem.
One difference in MySQL's favor was size of data loaded. MySQL is much more efficient, taking far less space on disk. But I don't care about that.
But, on yet another hand, postgresql is STILL too slow.
I can thankfully state that M$ is simply not an option, but anything that runs on Linux (and I can afford, the cheaper the better) can be. Including (drum roll please) SAP-DB, Informix (it went open source, right? Hmm, gotta start googling) and pretty much anything else I can come up with a Perl interface to talk to.
Oh, how I miss Oracle. But even if I spend a couple of months working around not having Oracle, it'll still be FAR cheaper than paying for Oracle.
|
Post #294,815
10/18/07 10:49:57 PM
|
Did you use what version of MySQL.
I told you to use 5.1.
There are specific enhancements in MySQL in 5.1
And exactly what kinds of setting were you using?
If you have lotsa memory... let it use it.
If you have drives that blaze... tweak it stop batching/buffering, if you have both... tell it to use more.
And if you didn;t try 5.1, you missed much.
--
[link|mailto:greg@gregfolkert.net|greg],
[link|http://www.iwethey.org/ed_curry|REMEMBER ED CURRY!] @ iwethey
PGP key: 1024D/B524687C 2003-08-05
Fingerprint: E1D3 E3D7 5850 957E FED0 2B3A ED66 6971 B524 687C
Alternate Fingerprint: 09F9 1102 9D74 E35B D841 56C5 6356 88C0
Alternate Fingerprint: 455F E104 22CA 29C4 933F 9505 2B79 2AB2
|
Post #294,817
10/18/07 11:24:35 PM
10/18/07 11:29:40 PM
|
No you didn't
You said:
Try the pre v6 stuff first, its pure gold.
and
use MySQL v.latest.(stable or devel) or use pre v6 stuff
I did V5.0 1st.
When I tried 5.1, I got into some lib dependancy issues. It's certainly not in stable, which is a bit scary.
If you are telling me there is significant speeds on query, then I'll try again. But if you are referring to all the new whizbang features (and yes, there are LOTS) that I don't care about, then I'll pass.
Edited by crazy
Oct. 18, 2007, 11:29:40 PM EDT
|
Post #294,818
10/18/07 11:35:25 PM
|
This is the 5.1 list of enchancements
[link|http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html|http://dev.mysql.com...sql-nutshell.html]
The only thing referenced is the partitioning and that doesn't help the initial poor SQL optimization for the straight selects of smallish tables.
I think I'm expecting too much since I have my Oracle experience.
|
Post #294,820
10/19/07 12:01:14 AM
|
MySQL has lots of ah incompleteness in it's optimizer.
It's mostly recent stuff, so they clearly have more work to do there.
Sub-selects is one area like that. I remember my web developers practically drooling over sub-selects when we finally upgraded the production databases. Turns out they are way too slow for many things. If you can restructure it as an Inner Join (even with a temporary table), it will be a lot faster. Like hundreds of times faster.
Wade.
Is it enough to love
Is it enough to breathe
Somebody rip my heart out
And leave me here to bleed
| |
Is it enough to die
Somebody save my life
I'd rather be Anything but Ordinary
Please
|
-- "Anything but Ordinary" by Avril Lavigne. | · my ·
· [link|http://staticsan.livejournal.com/|blog] ·
· [link|http://yceran.org/|website] · |
|
Post #294,822
10/19/07 12:25:56 AM
|
I have a tough time restructuring
We are creating a generic table driven GUI (Web 2.0 Goodness) front end. The tables can vary tremendously on the backend. So multi stage selects with temp table are right out, since we would have define some type of templating language to drive the query creation, which I want to avoid.
|
Post #294,844
10/19/07 9:19:44 AM
|
Fair enough.
Is it enough to love
Is it enough to breathe
Somebody rip my heart out
And leave me here to bleed
| |
Is it enough to die
Somebody save my life
I'd rather be Anything but Ordinary
Please
|
-- "Anything but Ordinary" by Avril Lavigne. | · my ·
· [link|http://staticsan.livejournal.com/|blog] ·
· [link|http://yceran.org/|website] · |
|
Post #294,830
10/19/07 6:36:52 AM
|
Sigh... Didn't look too deeply into Firebird, didja?
[link|http://www.janus-software.com/fb_fyracle.html|Fyracle: Oracle-mode Firebird].
There, that's the last I'll care about your problems, you fucking ingrate.
[link|mailto:MyUserId@MyISP.CountryCode|Christian R. Conrad]
(I live in Finland, and my e-mail in-box is at the Saunalahti company.)
Ah, the Germans: Masters of Convoluted Simplification. — [link|http://www.thetruthaboutcars.com/?p=1603|Jehovah]
|
Post #294,832
10/19/07 7:50:50 AM
|
Sigh - This time it's an option
So yes, I'll be happy to.
Thanks for mentioning it.
|
Post #294,866
10/19/07 6:05:16 PM
|
Ehh, you never CARED!
If you cared, you'd tell me what the bulk loading tool for Firebird is. I don't see one in the docs or the utilities that I have.
|
