RE: SQL Beginner's Tutorial: What if the World IS NOT NULL? and IN the Galaxy? by stormriderstudio

View this thread on: hive.blog | peakd.com | ecency.com

Viewing a response to: @dbzfan4awhile/sql-beginner-s-tutorial-what-if-the-world-is-not-null-and-in-the-galaxy

sql-forbeginners · @stormriderstudio · Jan 3 '18

$0.36

Ah SQL, so simple and yet when you want to do that ONE thing, you have to take 47 detours and test it at every step. Admittedly that's because I'm usually building complex queries from PHP and all sorts of fun things can happen.

I'd be very interested in learning more about database/table optimisation and query optimisation - things like additional keys, foreign keys and optimal datatype sizes (for some reason I always pick binary-friendly data sizes like varchar fields at 128 or 256 characters with no idea whether it's actually helpful!).

Nice work :) Posts like this could bring Krillin back to life for the 4'000th time ;)

👍 dbzfan4awhile

`author`	stormriderstudio
`permlink`	re-dbzfan4awhile-sql-beginner-s-tutorial-what-if-the-world-is-not-null-and-in-the-galaxy-20180103t211925158z
`category`	sql-forbeginners
`json_metadata`	{"tags":["sql-forbeginners"],"community":"busy","app":"busy/2.2.0"}
`created`	2018-01-03 21:19:27
`last_update`	2018-01-03 21:19:27
`depth`	1
`children`	3
`last_payout`	2018-01-10 21:19:27
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.358 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	633
`author_reputation`	1,082,215,996,176
`root_title`	"SQL Beginner's Tutorial: What if the World IS NOT NULL? and IN the Galaxy?"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	26,871,724
`net_rshares`	42,380,306,039
`author_curate_reward`	""

properties (23)vote details (1)

voter	weight	wgt%	rshares	pct	time
dbzfan4awhile	0 B		42,380,306,039	35%

@dbzfan4awhile · Jan 3 '18

Wow, you are very familiar with SQL... you may be above my level of expertise, honestly. I like putting these Tutorials together because I think they are set up in a low-level way so that novices can begin to learn and understand it.

Optimization is king... I use Crystal Reports for Document generation at my job and if the query is too slow the report will not generate or will not generate properly.

properties (22)

`author`	dbzfan4awhile
`permlink`	re-stormriderstudio-re-dbzfan4awhile-sql-beginner-s-tutorial-what-if-the-world-is-not-null-and-in-the-galaxy-20180103t213431376z
`category`	sql-forbeginners
`json_metadata`	{"tags":["sql-forbeginners"],"app":"steemit/0.1"}
`created`	2018-01-03 21:34:33
`last_update`	2018-01-03 21:34:33
`depth`	2
`children`	2
`last_payout`	2018-01-10 21:34:33
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	403
`author_reputation`	8,411,661,872,775
`root_title`	"SQL Beginner's Tutorial: What if the World IS NOT NULL? and IN the Galaxy?"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	26,874,175
`net_rshares`	0

@stormriderstudio · Jan 3 '18

$0.59

Yea, I've been playing with MySQL since.... around 2000 I think. I tend to run everything through HeidiSQL which is a lovely interface once you get to find all the tips and tricks in there.

I read back to your guide on JOINS and it's very different in MySQL. Inner joins by default, with just left join or right join. Seems foreign keys are used differently too, in MySQL they are more of an optimisation tool and everything is handled though the ON keyword. A much more informal layout but probably where I'm coming across some issues with performance on a site I inherited (getting close to 1 million records which join to themselves and repeat data all over the place - nightmare!)

On my list for 2018 is more optimisation for MySQL as I'm going from releasing purely bespoke sites (generally "awkward" ecommerce needs, package builders and the like) to my first general release CMS/online shop package with theme support - finally a code-base I can come back to and work on in iterations! :)

👍 dbzfan4awhile

`author`	stormriderstudio
`permlink`	re-dbzfan4awhile-re-stormriderstudio-re-dbzfan4awhile-sql-beginner-s-tutorial-what-if-the-world-is-not-null-and-in-the-galaxy-20180103t214117533z
`category`	sql-forbeginners
`json_metadata`	{"tags":["sql-forbeginners"],"community":"busy","app":"busy/2.2.0"}
`created`	2018-01-03 21:41:18
`last_update`	2018-01-03 21:41:18
`depth`	3
`children`	1
`last_payout`	2018-01-10 21:41:18
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.590 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	997
`author_reputation`	1,082,215,996,176
`root_title`	"SQL Beginner's Tutorial: What if the World IS NOT NULL? and IN the Galaxy?"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	26,875,321
`net_rshares`	70,633,843,398
`author_curate_reward`	""

properties (23)vote details (1)

voter	weight	wgt%	rshares	pct	time
dbzfan4awhile	0 B		70,633,843,398	60%

@dbzfan4awhile · Jan 3 '18

That's alot of work. I work in shipping/logistics connecting clients to carriers for rating, shipping, and labeling purposes. The custom needs of the client are what really makes the queries begin to balloon and become convoluted... or because they might use the same database that another client uses (or WMS if you prefer) while using different fields or storing different data in the same fields. Then they expect that everyone is doing things the same way as they are.

Creating SQL queries can be fun to develop and super-challenging to get right, especially if you're joining like 16 tables together.

properties (22)