MySQL Table Size

Ever wondered which database or tables are taking up disk space on a MySQL/MariaDB server?

This query will provide the size of each table:

ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS `Size (MB)`,
ROUND((data_free / 1024 / 1024), 2) AS `Reclaimable Size (MB)`
FROM information_schema.TABLES
-- WHERE `TABLE_SCHEMA` = 'database_name'

Shimming MySQL Functions into SQLite for Laravel CI/CD Testing

Colin DeCarlo presented a talk at Laracon Online where among other useful tips, he demonstrated how to shim MySQL functions in an SQLite database (e.g., add functions that MySQL has but SQLite does not).

Here are two examples that I just needed in a project (FLOOR and DATEDIFF):

use Illuminate\Support\Facades\DB;

DB::getPdo()->sqliteCreateFunction('floor', fn ($value) => floor($value));
DB::getPdo()->sqliteCreateFunction('datediff', fn ($date1, $date2) => Carbon::parse($date1)->diff(Carbon::parse($date2))->days);

Counting Distinct Values in a Single Field

A quick MySQL snippet to count how many times a value appears in a single field—much easier to grok than multiple JOINs.

COUNT(CASE WHEN meta_value = 'value1' THEN 1 END) AS value1,
COUNT(CASE WHEN meta_value = 'value2' THEN 1 END) AS value2
FROM wp_post_meta;

/** Results

| value1 | value2 |
| 75     | 56     |

Infusionsoft to Shopify Customer History Migration

A helper utility to copy Infusionsoft contacts and their order history to Shopify, ignoring Infusionsoft contacts who do not have orders associated with their contact ID.

It also optionally pulls info from a WooCommerce installation.

Get the code from the GitHub repository. Continue reading “Infusionsoft to Shopify Customer History Migration”