Bash / MySQL queries…

Reduce connection counts to MySQL by using an array to get many values at once (assuming they’re single ‘word’ values)

I “woke up” and realised I often do this wrong …. (too many connections/individual queries on MySQL) when the query is returning simple values (single word like things) for each field.

As a means of example :

So a before query :

MYSQL="mysql -NB my_database"

# Obviously $VAR1/$VAR2 can contain spaces etc in this example
VAR1=$(echo "SELECT field FROM table WHERE some_key = '${IDENTIFIER}'" | $MYSQL)
VAR2=$(echo "SELECT field FROM table WHERE some_key = '${IDENTIFIER}'" | $MYSQL)

And perhaps a nicer way might look more like :

# Chuck output from a query into an array (retrieve many fields at once)
# coalesce( ... ) copes with a field that may be null.

BITS=( $(echo "SELECT coalesce(field1,200) as field1, field2, field3 FROM some_table WHERE some_key = '${IDENTIFIER}'" | $MYSQL ) )

# and then check we had enough values back
if [ ${#BITS[@] -lt 3 ]; then
echo "handle this error..."
# This will not work if field1/field2/field3 contain spaces.

Finding and killing long running MySQL queries

Sometimes (Woocommerce?) related queries on a MySQL server can seemingly run forever and eventually lead to the server being clogged up (and few free connections).

Something like this can help …. (i.e. kill all MySQL queries which have been running longer than 900 seconds, and aren’t from the ‘root’ user).
Continue reading “Finding and killing long running MySQL queries”

Cleaning up a lot of WordPress _transients

WordPress seems to like hiding a load of ‘transient’ (cacheable) stuff in it’s wp_options table. Unfortunately for one site, it seems it didn’t bother to clean up the transient stuff, leaving behind about 750,000 records… which made a WP version upgrade painful, as MySQL locks the wp_option table which causes all other page loads to get stuck waiting (and the site to stop working).

Fix / hacky solution after the ‘more’…

Continue reading “Cleaning up a lot of WordPress _transients”

MySQL update/write query analysis (query profiling)

Do you have a slow MySQL update/insert/delete query?

Obviously, for ‘SELECT’ queries you can prepend the query with “EXPLAIN ” – however that doesn’t work for the other query types (UPDATE/INSERT/DELETE).

So, one solution which may explain why the query is slow is to turn on MySQL’s profiling functionality, like in the following example :
Continue reading “MySQL update/write query analysis (query profiling)”

Late to the performance party

Everyone else probably already knows this, but $project is/was doing two queries on the MySQL database every time the end user typed in something to search on

  1. to get the data between a set range (SELECT x,y….. LIMIT n, OFFSET m or whatever) and
  2. another to get the total count of records (SELECT count(field) ….).

This is all very good, until there is sufficiently different logic in each query that when I deliberately set the offset in query #1 to 0 and limit very high and find that the of rows returned by both doesn’t match (this leads to broken paging for example)

Then I thought – surely everyone else doesn’t do a count query and then repeat it for the range of data they want back – there must be a better way… mustn’t there?

At which point I found:

See also the comment at the bottom of which gives a good enough example (Search for SQL_CALC_FOUND_ROWS)

A few modifications later, run unit tests… they all pass…. all good.

I also found some interesting code like :

$total = sizeof($blah);
if($total == 0) { … }
elseif ($total != 0) { …. }
elseif ($something) { // WTF? }
else { // WTF? }

(The WTF comment were added by me… and I did check that I wasn’t just stupidly tired and not understanding what was going on).

The joys of software maintenance.