SQL that runs, indexes that earn their keep, and migrations that don't take the site down.
Practitioner reference for Elasticsearch 9.x: index and document operations, Query DSL, aggregations, vector / kNN search, ESQL, cluster management, version compatibility notes, and the gotchas that bite first-time operators.
MySQL cheat sheet covering CLI commands, database and table operations, joins, indexes, backups, user management, and transactions, with version notes for 5.7, 8.0, and 8.4.
MySQL 8.4 is the new LTS branch, with Premier Support through April 2029 and Extended Support through April 2032. The 8.0 to 8.4 upgrade is much smaller than 5.7 to 8.0, but removed options and the new authentication_policy variable still bite. Full procedure with rollback.
MySQL 5.7 has been past its EOL since October 2023. Here is the migration to 8.0: prerequisites, dry-run with mysqlcheck, in-place upgrade, the authentication-plugin change that breaks old clients, and rollback if it goes sideways.
Format a MySQL DATETIME in a SELECT using DATE_FORMAT, common patterns for ISO-8601 and human-readable output, and CONVERT_TZ for timezone-correct dashboards.
MariaDB started as a MySQL fork and is binary-compatible up to a point. Where the wire protocol stays the same, where the SQL dialect diverges, and how to migrate a real database without losing anything.
Change a MySQL column type with ALTER TABLE MODIFY or CHANGE. Covers data preservation, CAST behavior, INT to BIGINT, VARCHAR widening, and how ALGORITHM applies.
Find rows in one MySQL table that do not exist in another using LEFT JOIN IS NULL, NOT EXISTS, or NOT IN. Each method's NULL behaviour and performance trade-off.
Reset a forgotten MySQL root password using --skip-grant-tables, an init-file, or my.cnf. Step-by-step for MySQL 5.7, 8.0, and the changes that landed in 8.4.
Add a column to a MySQL table with ALTER TABLE ADD COLUMN. Covers DEFAULT values, NOT NULL on existing rows, AFTER positioning, and ALGORITHM=INSTANT on MySQL 8.0.12+.
The alias-swap pattern I use in production to change Elasticsearch mappings without taking the search down. Walks through the five steps, gives you a parameterized bash script that runs the reindex and rolls back if needed, and verifies the technique against Elasticsearch 8.x and 9.x.
Delete duplicate rows in MySQL while keeping one per group, using DELETE JOIN, ROW_NUMBER with CTE, or the safe temp-table swap. With dry-run, transactions, and rollback.
Find duplicate rows in MySQL with GROUP BY HAVING, a ROW_NUMBER window function, or a self-join. Includes NULL behaviour, soft duplicates, and the right index.
MySQL 8.0 made many ALTER TABLE operations instant. For the heavy ones that still rebuild the table — column type changes, charset conversion, primary-key changes — native online DDL, gh-ost, and pt-online-schema-change each fit a different situation. Compared on a real production schema.
JOIN two MySQL tables with INNER, LEFT, and RIGHT JOIN. With a real example, when each one matches, and the duplicate-row multiplication mistake everyone makes once.
Never store money as a BSON double. Use the Decimal128 type for exact decimal amounts, or store integer cents in a 64-bit NumberLong for high-volume ledgers, and always keep the ISO 4217 currency code in its own field. Comparison, worked documents, and the NumberDecimal string gotcha.
How to store a UUID in MongoDB the right way: as a 16-byte BSON Binary of subtype 4, not a 36-character string. Covers mongosh UUID(), driver representations, the legacy subtype 3 byte-order mess, and ObjectId vs UUID for _id.
When to embed related data as subdocuments and when to reference it by _id in MongoDB. The 16 MB document limit, $lookup joins, access patterns, and the established schema-design patterns.
Import a CSV into MySQL using LOAD DATA INFILE, LOAD DATA LOCAL INFILE, or the mysqlimport command. Covers header rows, encoding, the FILE privilege, and broken CSVs.
WordPress stores a one-way password hash in wp_users.user_pass, a VARCHAR(255) column. Historically a phpass $P$ portable hash (MD5-based but stretched), and bcrypt for new hashes since WordPress 6.8 in 2025.
![How to store an array in PostgreSQL: native array column types declared with [], the curly-brace and ARRAY[...] literal forms, containment and overlap queries with @> and &&, ANY() membership, and a GIN index.](https://images.techearl.com/store-array-postgresql/store-array-postgresql-1536.webp?v=2026-05-30T05%3A00%3A00Z "Store an Array in PostgreSQL: text[], @>, and GIN Indexing (2026)")
PostgreSQL has native array types: any base type can be an array, declared with []. How to insert with the curly-brace literal or ARRAY[...], query with @>, &&, and ANY(), index with GIN, and when an array beats a junction table or jsonb.
Export a MySQL table to CSV using SELECT INTO OUTFILE, mysqldump --tab, the mysql client with a piped redirect, or a no-privilege one-liner that works on hosts where FILE is disabled.
MySQL utf8 is a 3-byte alias for utf8mb3 that cannot store emoji or many CJK characters. utf8mb4 is the real UTF-8 and has been the default since MySQL 8.0. The migration looks like ALTER TABLE but bites on index length and ROW_FORMAT.
Store an Argon2id password hash in MySQL or MariaDB the right way: VARCHAR(255), never a fixed-width column. The encoded format, why its length varies, computing it in PHP / Python / Node, OWASP parameters, and a worked users schema.
How to store a UUID in PostgreSQL: use the native 16-byte uuid type, not text or varchar(36). Covers gen_random_uuid(), uuid-ossp, why random v4 primary keys hurt index locality, and UUIDv7.
TEXT vs BLOB in MySQL: TEXT is for character data and has a charset, BLOB is for raw bytes and does not. The size families, the row-limit and indexing gotchas, and why files belong in object storage, not a BLOB.
MyISAM has no transactions, no foreign keys, and corrupts on crash. InnoDB has been the MySQL default since 5.5 and is the only engine that gets new features. Here is how to convert every MyISAM table in a MySQL database with a single SQL script.
How to store a UUID in MySQL or MariaDB: the readable CHAR(36) string or the BINARY(16) you should usually reach for. Storage cost, index size, UUID_TO_BIN/BIN_TO_UUID, the swap_flag trick, and why random UUID primary keys wreck InnoDB inserts.
MySQL ENUM is compact and fast, but adding a value is a schema change and there is nowhere to hang metadata. When to use ENUM, when to use a lookup table with a foreign key, and where VARCHAR + CHECK fits.
PostgreSQL has two JSON types. json keeps an exact text copy and reparses on every read; jsonb stores a decomposed binary format you can index with GIN. When to use each, the operators, and a worked products schema with a containment index.
DATETIME vs TIMESTAMP in MySQL: one is time-zone naive, the other converts to UTC and back. How to store instants safely, when to keep a separate IANA zone name, and why MySQL has no timestamptz.
MySQL has a native JSON column type that validates on insert, stores a parsed binary format, and gives you path operators like ->>. When to reach for it over TEXT, how to index it with a generated column, and the MariaDB difference.
Store money in PostgreSQL as numeric (decimal), not the locale-dependent money type and never a float. numeric(19,4) for exact amounts, bigint cents for high-volume ledgers, plus a separate char(3) ISO 4217 currency. Comparison and worked schema.
Store a phone number in MySQL as a string, never an integer. Normalize to E.164 and use VARCHAR(16), index it for lookups, and keep the raw input in a second column. Worked schema for MySQL and MariaDB.
Never use FLOAT or DOUBLE for money. Use DECIMAL(19,4) for exact fixed-point amounts, or BIGINT integer cents for high-volume ledgers, and always store the ISO 4217 currency code separately. Comparison, worked schema, and the rounding rules.
The right column for an email in MySQL is VARCHAR(255), utf8mb4, with a UNIQUE index. Why 255, why not TEXT, how to enforce case-insensitive uniqueness, and a worked users schema.
PostgreSQL has native network types: inet for a host address, cidr for a subnet. Both validate on insert, hold IPv4 and IPv6, and support real containment operators. Why text is the wrong default, plus a worked schema.
MySQL has no real boolean type. BOOL and BOOLEAN are just aliases for TINYINT(1). Here is why TINYINT(1) is the idiomatic choice, how BIT(1) trips up ORMs, and a worked is_active schema.
The best way to store an IP address in MySQL: INT UNSIGNED for IPv4-only, VARBINARY(16) for IPv6 (or both). Why VARCHAR(45) is the wrong default, the INET conversion functions, and a worked schema.
A bcrypt hash is a fixed 60-character string. In PostgreSQL the right column is text (varchar(255) is equivalent). Why text over char(60), app-side hashing, the pgcrypto crypt() option, and a worked users schema.
The column type for a password in MySQL is VARCHAR(255). You store the encoded output of a slow password hash (bcrypt, argon2id, scrypt), never a raw MD5 or SHA-256, and you never query the table by password.
A bcrypt hash is always 60 characters. The right MySQL column is VARCHAR(255) (the future-proof default) or CHAR(60) (the exact fit). Why, plus the app-side hashing and a worked schema for MySQL and MariaDB.
Back up every MySQL database in one command with mysqldump, including --single-transaction for InnoDB consistency, gzip compression, restore steps, and the modern alternatives (MySQL Shell, Percona XtraBackup).
How to store an MD5 hash in PostgreSQL: the raw 16-byte bytea you should usually reach for, the readable text/char(32) hex string, and the uuid trick. Storage cost, encode()/decode() instead of HEX/UNHEX, and the pgcrypto digest() function.
How to store a SHA-256 hash in MySQL or MariaDB: CHAR(64), VARCHAR(64), or the BINARY(32) you should usually reach for. Storage cost, index size, the SHA2() function, and indexed generated columns.
Four ways to store an MD5 hash in MySQL or MariaDB: CHAR(32), VARCHAR(32), BINARY(16), and the two-BIGINT split. Storage cost, index speed, and when to use each (plus when to use a stronger hash instead).
Every MySQL data type with exact storage size, value range, and when to use it. Numeric, string, date/time, JSON, spatial, binary, and the new VECTOR type added in MySQL 9.0.