Back to MySQL again...

[Gayle29uk]

Ok, I stole someone else's PA dump script and adapted it to mysql and now I'm onto cluster/para ranks. The code I'm using to generate the planetary cluster ranks is...

$i=1;
while($i <= $cluster){
  $rank = 1;
  $query = 'SELECT x,y,z FROM plandump WHERE tick='.$tick.' 
    AND x='.$i.' ORDER BY size DESC';
  $result=$database->openConnectionWithReturn($query);
  while(list($x, $y, $z) = mysql_fetch_array($result)){
    $query = 'UPDATE plandump SET croidrank='.$rank.' 
      WHERE x='.$x.' AND y='.$y.' AND z='.$z.' AND tick='.$tick;
    $database->openConnectionNoReturn($query);
    $rank++;
  }
  $i++;
}

...but it takes forever and a day. There must be a quicker more efficient way to do it but I can't think of one, any ideas?

[Mit]

It will take a while, a suggestion, that was actually given to me, was to work in RAM. Save teh entire universe in an array, change stuff and update once per plent instead of 8 times it would have been for me and the official tools. One hell of a lot quicker.

Each planet has an idividual ID, thats how i reference it.

[Pitchfork]

Originally posted by Mit
It will take a while, a suggestion, that was actually given to me, was to work in RAM. Save teh entire universe in an array, change stuff and update once per plent instead of 8 times it would have been for me and the official tools. One hell of a lot quicker.

Each planet has an idividual ID, thats how i reference it.

That's it, I read the planetfile into memory, then do a few qsorts, then use postgresql's COPY FROM stdin function (similar to mysql's read data infile or sumthing), pretty fast

[Gayle29uk]

Originally posted by Pitchfork
That's it, I read the planetfile into memory, then do a few qsorts, then use postgresql's COPY FROM stdin function (similar to mysql's read data infile or sumthing), pretty fast

My bash scripting sucks in a real big way otherwise I'd do it like that

The initial dump handling is done using LOAD DATA INFILE and it's almost instantaneous but I'm having to use PHP to do the rank processing and it's slooooow

[Pitchfork]

Write in C
(to the tune of the Beatles' "Let it Be")

When I find my code in tons of trouble,
Friends and colleagues come to me,
Speaking words of wisdom:
"Write in C."

As the deadline fast approaches,
And bugs are all that I can see,
Somewhere, someone whispers:
"Write in C."

Write in C, Write in C,
Write in C, oh, Write in C.
LOGO's dead and buried,
Write in C.

I used to write a lot of FORTRAN,
For science it worked flawlessly.
Try using it for graphics!
Write in C.

If you've just spent nearly 30 hours
Debugging some assembly,
Soon you will be glad to
Write in C.

Write in C, Write in C,
Write in C, yeah, Write in C.
Only wimps use BASIC.
Write in C.

Write in C, Write in C
Write in C, oh, Write in C.
Pascal won't quite cut it.
Write in C.

Write in C, Write in C,
Write in C, yeah, Write in C.
Don't even mention COBOL.
Write in C.

[xtothez]

Updating as you go will always be too slow for this sort of thing, no matter how you write it. My first attempt at a planets parser took about a minute to do the full DB, and that was without advanced stats like roid losses and score history. Either make an array in PHP then load/unload with that, or better yet - use C.

[queball]

Originally posted by Mit
It will take a while, a suggestion, that was actually given to me, was to work in RAM. Save teh entire universe in an array, change stuff and update once per plent instead of 8 times it would have been for me and the official tools. One hell of a lot quicker.

Each planet has an idividual ID, thats how i reference it.

MySQL will do this if it's appropriate, and it will do it much better and faster than you could, while providing any transaction semantics you have specified.

The bonus of only having two queries (select *, copy from stdin) is only there because Gayle is doing one query per planet, which is what she's (I assume) asking for a clever idea for.

As for using C... I never understand how people can look and a program that spends something like 0.1% of its time in code and 99.9% of its time waiting on external processes, and say that another language should be used.

Gayle, a few ways I've gone about doing this sort of thing in MySQL are:
- insert each sorted cluster into a temporary table with an autoincrement, then load back
- use a variable so select x,y,z,@a=@a+1 (sorry, can't remember the exact syntax offhand)
- use (cluster, score) as an index and calculate the rank dynamicly each time
One of these might work for you. If that still doesn't give you enough speed, you could try the optimising tips in the manual, or upgrade to MSSQL.

[Gayle29uk]

weeeeeeh, it works in under a second now for all the planets

Indexing by x,y,z helps a bit

[edit]Timed at 7 seconds for universe/cluster/para/galaxy rankings for both planets and gals. Much faster than the unindexed version [/edit]

[Starbucks]

am i the only one that uses SAP.net as a database system????















thought so

[Pitchfork]

Originally posted by Starbucks
am i the only one that uses SAP.net as a database system????















thought so

I thought about trying it, but I stuck to postgreSQL

[Raging.Retard]

Your original script looks slow and ive not even tried it . The only way your going to get extreme speed is to not pull any data back from the DB, as queball said.

Hoewever, as a quick performance change to your current script, open your database connection outside the loop, and only issue the changing query inside the loop (this removes the overhead of repeated connection negotiation, be it named pipe/tcp - I dont know how MySQL works).

As for your indexing ... of course it woud help Though to be honest, it would have been better to have made x,y,z your primary key from the start which impiles an index on them anyway.

All said.. You should really try to not pull data back from the DB at all. Databases are there for storing and processing data. Theres little point in you trying to do it, when thats its job. As quey said, it will most likely do it quicker (and more importantly, with less code to write!).

SELECT plandump.*, (SELECT COUNT(*) FROM plandump AS planindexer WHERE planindexer.size > plandump.size) + 1 AS Rank
FROM plandump

(No idea in MySQL but in TSQL 'Size' is a keyword btw).

That statement will return your data with a Rank field showing its associated rank. Im sure you can work the UPDATE statmenet from it, should that be your goal. That statement took < 1 sec to Rank 8,000s plants on a cache hit on my old machine (p3/600). Approx 1.5 secs on cache miss.

Incidentally ive posted the execution plan which can be found (here). You can see that the query spends 67% (the majority of the query) of its time in the Index Spool / Eager Spool Stage... without your pre orderd index, that has to be done on the fly, which is why your performance sufferd orginally (Although query is diff, it still data spools in same way).

[Pitchfork]

Originally posted by Raging.Retard
...
SELECT plandump.*, (SELECT COUNT(*) FROM plandump AS planindexer WHERE planindexer.size > plandump.size) + 1 AS Rank
FROM plandump
...

No Subqueries in MySQL unfortunately

[Raging.Retard]

Originally posted by Pitchfork
No Subqueries in MySQL unfortunately

If thats true... this just confirms that MySQL is a joke . Its about as much of a DB server as notepad.

MySQL DOES support subqueries.

kthxbye

[pablissimo]

http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

[Pitchfork]

Originally posted by ItotheDtotheI
MySQL DOES support subqueries.

kthxbye

http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html
Subqueries are supported in MySQL version 4.1.

http://www.mysql.com/press/release_2003_10.html
MySQL AB Confirms Version 4.0.12 Ready for all Application Areas

http://lists.mysql.com/list.php?list=announce&post=161
MySQL 4.1.0 has been released
...
As this code is currently labelled "Alpha", we
do not recommend that this version be used in production environments yet!

So indeed it does more or less. But did you ever tried to use a mySQL Alpha? I did once...

[Edit] I have to apologize for bein outdated six days, but heh, I don't use mySql [/Edit]

Originally posted by Pitchfork
So indeed it does more or less. But did you ever tried to use a mySQL Alpha? I did once...

yes

nothing bad happened to me.

[Pitchfork]

Originally posted by ItotheDtotheI
yes

nothing bad happened to me.

hmz, mine kept crashing about once per hour, was a 3.something alpha though

[MT]

Originally posted by Raging.Retard
If thats true... this just confirms that MySQL is a joke . Its about as much of a DB server as notepad.

You think you are so cool with your MSQL server licence...

MySQL is fast at selects and inserts. Thats about it. Its a SQL front end to flat file databases, and a damn good one at that, but for advanced SQL manipulations, you will always be better off with something else.

Not Filemaker PRO either!

[Raging.Retard]

Originally posted by MT
You think you are so cool with your MSQL server licence...

Its not about being 'cool'. Its about showing people not all MS products are bad, despite what they may think. That one just happens to be one of their better, and a pretty competetive application at that. I wouldnt do it as much if people didnt worship MySQL as much, like some magnificent database engine sent from god. (Yeah its free... blah blah.. PostgreSQL anyone?).

Originally posted by MT
MySQL is fast at selects and inserts. Thats about it. Its a SQL front end to flat file databases, and a damn good one at that, but for advanced SQL manipulations, you will always be better off with something else.

When I consider even MS Access supports subqueries, which is.... 'a SQL front end to flat file database'... , it cant help but bring a smile to my face. Subqueries are not an 'advanced SQL manipulations' they are trivial operations, in the same category as table joins. Pretty important in a relational database, infact I really cant see why anyone in their right mind would leave them out.

Innit.

:debian-legal: dont like MSSQL, so neither do I.

[Gayle29uk]

The DB is MySQL 3.x. Having learnt a lot about handling 'significant' sized databases during this project I would happily trade it for postgresql and it's nested SQL statements any day

My hosting provider doesn't do psql though.

[queball]

Originally posted by Raging.Retard

When I consider even MS Access supports subqueries, which is.... 'a SQL front end to flat file database'... , it cant help but bring a smile to my face. Subqueries are not an 'advanced SQL manipulations' they are trivial operations, in the same category as table joins. Pretty important in a relational database, infact I really cant see why anyone in their right mind would leave them out.

Hardly the same category as table join, since without table joins it would be a useless language.

Are you lot really so lazy that a feature like subselects constrains your choice of database? Something like the lack of a non-blocking API is understandable, but subselects?

I know it's naive to apply an academic view to everything, but really the fundamentals of using an relational database are an understanding of tuples, joins, indeces and transactions. Subselects are a common idiom so it's nice that MSSQL provides a simple way to deal with them, which lets MSSQL do its query optimisation magic, but they are hardly fundamental. And I know DB admins like to rant about referential integrity, and whatever, but foreign keys can be done outside too.

Things like a query analyser are nice. You can get at least one for MySQL, too, but it turns out they are rarely used: the expert chooses his operations well. If optimisation is needed, tools are fine, but something more flexible is usually required anyway.

I only hear complaints like this from people learning MySQL and PHP and writing a k-rad dynamic website. I've never found MySQL's missing features a problem in my own apps. Not that this really matters anymore with the release of 4.1.

[Raging.Retard]

Originally posted by queball
Hardly the same category as table join, since without table joins it would be a useless language.

All joins can be expressed as a single or series of subqueries, not all subqueries can be expressed as table joins. So if for some bizare reason you only implemented subqueries, you have not lost any functionality of your system. I would indeed rank subselects with the same importance as table joins.

Obviously there is the issue of performance, there is usually no performance difference between a query that includes a subquery and a semantically equivalent version using table joins that doesnt. Only in cases where existence must be checked, a join will give better performance (Otherwise the nested query must be processed for each result of the outer query to ensure elimination of duplicates, which is obviously slower).

Not only are subselects 'a common idiom', they are also part of the ANSI SQL 92 standard. I know .. I know.. that this goes back to the old argument of just how many DB's are actually SQL92 complient, but as its my view that subqueries are of such fundamental importance to the language, I cant understand why they chose to leave it out for so long. Still, like you said, not that it matters now.

[queball]

Originally posted by Raging.Retard
All joins can be expressed as a single or series of subqueries

I'm intrigued, how would this work?
Like how would you do "SELECT * FROM a, b" with subqueries.

[Raging.Retard]

Originally posted by queball
I'm intrigued, how would this work?
Like how would you do "SELECT * FROM a, b" with subqueries.

I suspected I would be asked to prove it

Personally I dont like the join implied by 'SELECT * FROM a, b' I prefer the equivilent (yet in my opinion, semantically clearer) version below:

SELECT * FROM a INNER JOIN b ON 1=1

Still I guess thats just a taste thing.

That query can expressed as a subquery as follows:

SELECT *, (SELECT *FROM b) FROM a

The flaw in the plan, is that although this is valid SQL, a fair few (relational anyway) DB servers (my favourd MS included), do not support using subqueries as expressions directly in the SELECT when the subquery returns more than one row (ie, a non atomic result). This is obviously only restrictive to expressions, not applying to a subselect as part of an IN clause (as that would be silly).

[queball]

Indeed, and as far as I can see that would just be an implicit join. Can you come up with an expression with subqueries that can't be expressed with a few joins?

[Gayle29uk]

Originally posted by Raging.Retard
As for your indexing ... of course it woud help Though to be honest, it would have been better to have made x,y,z your primary key from the start which impiles an index on them anyway.

Re-reading this thread I have to query that. How on Earth can x,y,z be primary keys when they're not unique?

[queball]

Originally posted by Gayle29uk
Re-reading this thread I have to query that. How on Earth can x,y,z be primary keys when they're not unique?

(x,y,z) IS unique though, if you have one row per planet... the key is a combination ("tuple") of the relevant fields. Just like if you had a primary key of CONCAT(x, ",", y, ",", z), except better.

[Gayle29uk]

Originally posted by queball
(x,y,z) IS unique though, if you have one row per planet... the key is a combination ("tuple") of the relevant fields. Just like if you had a primary key of CONCAT(x, ",", y, ",", z), except better.

Didn't know you could have a multi field primary key, you learn something new every day

Although as the current dump is in it's own table with an auto_increment ID as primary key (indexed by x,y,z) I'm not sure it's much use to me. Certainly not in the history file as x,y,z is not unique in there.

[queball]

Originally posted by Gayle29uk
Didn't know you could have a multi field primary key, you learn something new every day

Although as the current dump is in it's own table with an auto_increment ID as primary key (indexed by x,y,z) I'm not sure it's much use to me. Certainly not in the history file as x,y,z is not unique in there.

I don't believe there's any difference between a primary key and a unique key in terms of speed. It really depends on the kind of queries you'll do but if I had a history database I'd go for a primary key on (x,y,z,t), keys on (x,y,z), (score,t) and (size,t), just as a guess (and I'd have a profiler and ALTER ready as soon as some PHP is sorted out). Insert speed be damned .