next »

[thedarkness]

Yeah, like so much of this shit it's "a little from category A and a little from category B" 

Cheers,
td

[perkiset]

I personally try to keep as much data manipulation off the SQL just because i like my SQL to do the one thing its good at, lookups. But thats really just personal preference. obviously the less data you push across a network, the better. so it really depends on the situation. in this case, pulling the manipulation off the SQL box is probably better, if... they are on seperate boxes.

Hooboy. dbDog is gonna take it up with you. He's a big "Put it all behind the database as stored procedures" kind of guy. And he's huge, and he's mean and he's continually cracked out and ready for a fight.

:checks to see if dbDog is back from Hawaii yet: 

/p

[nutballs]

i will probably agree, as will he with me in some respects.

let me clarify, using two extremes.

it would be dumb to do a Select * from table, then find the record you want by looping through the records until you find the one you want on the client/application side. Obviously you want to sort and filter and output, ONLY the data your app/client needs.

however.

for something like the IP lookup. transforming your filter value BEFORE you send the request to the SQL server makes sense. Not quite an extreme example, but good enough.

another reason to keep the "code" out of the database is to keep it system agnostic. That may just be a side effect of alot of the work I do which is relocation type projects.

[dirk]

I have often used the MySQL functions for the conversion:

SELECT INET_ATON('255.255.255.255');
-> 4294967295

SELECT INET_NTOA(4294967295);
-> 255.255.255.255

If an INT UNSIGNED column is used it's not necessary to use BIGINT.
So only 4 bytes are required instead of 16 (VARCHAR).

[nutballs]

ah yes, mysql has signed and unsigner. i forgot about that. so unsigned int works. in MSsql, bigint works. which is still only 8bytes compared to the 16 for varchar.(32 if your doing nvarchar i think)

@perk- where are you getting 20 bytes for bigint?
the following is MSsql, i doubt though that mysql is any different, except for the signed/unsigned capability.

bigint - Integer data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). Storage size is 8 bytes.
int - Integer data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes.
smallint - Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.
tinyint - Integer data from 0 through 255. Storage size is 1 byte.

[perkiset]

SELECT INET_ATON('255.255.255.255');
SELECT INET_NTOA(4294967295);

  sweetness.

@perk- where are you getting 20 bytes for bigint?
the following is MSsql, i doubt though that mysql is any different, except for the signed/unsigned capability.

lol I just created one and looked at the size of it. Dorkometer has pegged the needle. Here is the actual storage sizes from the MySQL site:

[m0nkeymafia]

I always thought mysql was much faster at most calcs than php, plus if you have caching on and the query isnt too big itll save it for you.

Also with regards to indexes i think you have to do them in a particular order

i.e. if you index
col 1, col2

then you need to use them in that order in your query for the index to take full effect

At least thats what I read and it seemed to work.

[nutballs]

Also with regards to indexes i think you have to do them in a particular order
i.e. if you index
col 1, col2
then you need to use them in that order in your query for the index to take full effect

i hope not... lol

[perkiset]

I always thought mysql was much faster at most calcs than php, plus if you have caching on and the query isnt too big itll save it for you.

That may be true (I don't know, honestly) for raw processing because MySQL will be completely compiled and PHP is scripted... but if you add the overhead of getting from a web call in <x> language and getting PHP to do a calc for you as opposed to doing it the language itself that might be different. Also, if you employ script cacheing ala APC and such, I think the calc-speed benchmarks start to get a lot closer

Also with regards to indexes i think you have to do them in a particular order
i.e. if you index
col 1, col2
then you need to use them in that order in your query for the index to take full effect

Perhaps that is true with really complicated WHERE clauses, but for the most part, I'd wager that MySQL is smart enough to see that it can use <the most appropriate index> on the query regardless of usage order...

[m0nkeymafia]

Ah yeah I hadnt figured that, the queries im talking about are biiiig lol
Actually thats a good point, I class these as big cos theyre about 40 lines long and join like 10 tables and so various calcs and groupings on the data etc.
Is that what you guys would call "big" too, or is that childs play? lol

[perkiset]

10 joins? That's a pretty healthy query.

I'd definitely EXPLAIN that SQL to make sure that MySQL has a good attack plan.

/p

[nutballs]

lol a 10 way join. thats pretty hefty. largest i ever have done was i think a 16 way.

if you are having execution timing issues, you might consider breaking it up. I have found some weird issues with joins, where even though it should not be the case, multiple lookups is signifcantly faster. this also could be bad indexes on my part, but it has happened enough times to me to no longer be a fluke.

[m0nkeymafia]

Well I think I optimised it quite well
Its for a league table
So needs to look up the league itself, the league players [its a simple joining table to show whose in what league]
then the players themselves
then the game table [thers 50k+ in there]
then the team table
then some other stuff

then it needs to do all calculations like figure out whose top, goal difference, who won etc etc
took me ages to write lol

[thedarkness]

Would a view help in this situation?

Or maybe it's time to redesign/rationalise that particular database schema?

Cheers,
td