

SQL queries kill website scalability - jonthepirate
https://coderwall.com/p/euos7a

======
gregjor
PHP can already store session information in the file system, memcache, or
redis. There's no reason to reinvent the wheel.

Adding a UUID to every table as a key is a terrible idea. You bloat your
primary key and every index from 4 bytes for the usual INT UNSIGNED to 32
bytes for the UUID, and replace a fast integer comparison with a slow string
comparison, for every key match. That might explain the JOIN problems you're
having.

If you need to expose a database row ID in your URLs but want to make it
tamper-proof it's better to use a salted SHA1 or even MD5 hash along with the
ID. This is a fairly common practice to protect HTML FORM fields from
tampering. Even better is using PHP's session storage so you aren't exposing
raw database IDs in the browser.

Throwing out JOIN means not using a relational model (it's called unnormalized
data). It may be appealing at first but it will cause duplicate and
inconsistent data, and you'll be writing application code to make your app
ACID-compliant. You are unlikely to get that right -- RDBMSs have a 30 year
head start on you.

Writing that RDBMSs are not scalabale or that SQL queries "were the leading
cause of website fatalities" in 2012 (where did you get that from?) just
advertises inexperience. Every big enterprise application and almost every big
web site runs on top of RDBMSs, leveraging several decades of reliability,
redundancy, optimization, and scalability experience. Do you think your bank
uses Mongo or some home-rolled UUID-based system without JOINs? No, they use
Oracle.

In my considerable experience working with broken web sites and databases
(that's what I do for a living) I'd say the leading cause of website
fatalities is bad programming. If your database server is slow or crashing you
should be looking at your database design and queries, not coming to the
conclusion that RDBMSs suck. If I crash on my motorcycle it's alway my own
fault, and I don't conclude that I know more about building motorcycles than
Kawasaki does.

------
venomsnake
From the absolutely non telling data point of 1 I can say that caching
solutions and their over aggressive use break down at least as frequent as the
databases.

First thing - break with the or die(mysql_error()) pattern

Second see if everything is properly and workingly indexed.

Third - take a look if you are doing something that is not needed. There are a
lot of parasitic queries left in any big project.

Fourth - a very successful approach that I have used is to create the majority
of slow content in static files that are generated from a cron. Disk based io
is easier to scale and you leave to the filesystem to do the caching. Rule of
thumb for me - if 3 minute delay is not fatal and it will be common for all
users - it goes in the static.

