MySQL

Use SQL to show MySQL tables in decreasing size order

Nov 28 '18Nov 29 '18
Blogging, Technology

Earlier today, I noticed that the robservatory database was massive—over 1GB in size, which it shouldn't be anywhere near (it's usually around 100MB). This is over the database size limit at my host, so I couldn't add any new content (nor could visitors comment, create accounts, etc.)

My host offers phpmyadmin acccess, so I connected to the database to try to figure out what was going on. Using phpmyadmin, you can browse tables, perform SQL commands, and export and import data—it's a must-have tool for managing remote databases.

The first challenge was to figure out which table was causing the problem. To help with that, I wanted to see which WordPress tables were the largest, as that should be a good hint. A web search found lots of possible solutions, but I liked this one the best. Within that thread, I slightly modified one of the queries to do what I wanted:

SELECT 
    table_name AS "Table",  
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
    FROM information_schema.TABLES
    WHERE table_schema = 'YOUR_DATABASE_NAME_HERE'
    ORDER BY (data_length + index_length) DESC;

SELECT

table_name AS "Table",

ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"

FROM information_schema.TABLES

WHERE table_schema = 'YOUR_DATABASE_NAME_HERE'

ORDER BY (data_length + index_length) DESC;

[continue reading…]

Install and configure Apache, PHP, and MySQL on macOS Sierra

Feb 5 '17
Mac OS X Hints, Unix

Fair warning: Today's tip is over-the-top geeky.

After many years of not doing anything with web serving on my local Mac, I recently had a need to look at some mySQL/PHP-based web packages. While I could install these on robservatory as tests, I generally like to install things locally first, because I'll install a handful of packages until I find the one I like. But it'd been so long, I didn't have anything configured. (Remember when enabling Apache—i.e. web sharing—was a feature of the System Preferences' Sharing panel? Ah, good times!)

I could have opted to use the built-in Apache, but I wanted something that I could more easily keep up to date, and that, if I chose, would be easy to remove. The good news is that Homebrew has packages available for Apache, PHP, and MySQL. Homebrew installs everything in its own directory tree, and adding and removing packages is simple—exactly the setup I wanted.

So in theory, installation was as easy as three brew tap commands. The reality, though, is that the installation is a bit more complex. OK, it's a lot more complex. The good news is that, this being the age of the internet, help is but a search away. Or the advice of a friend away, which is what I used in this case.

A friend pointed me to this excellent installion guide that walks through the entire process, including installing two versions of PHP with the ability to switch between them on the fly.

It was this on-the-fly switching, though, that gave me troubles: I couldn't get a site to load unless I specified "index.php" or "index.html" as part of the URL. (Apache is configured to grab those files automatically.) Solving that one took a bit of time…

[continue reading…]