A PHP function to sum values in associative arrays

For a new project, I needed to combine two or more associative arrays and sum the values of any keys that exist in common. I was a little surprised to find that there wasn’t a built-in function to do this in PHP. So I wrote my own. It can accept any number of arrays as arguments, and goes through each array one key at a time, comparing the keys to those in an output array. Where the keys match, the values are summed. If the key does not exist in the output array, it’s appended to it.

The function returns an array that contains all of the unique keys in the input arrays.

<?php
function array_mesh() {
// Combine multiple associative arrays and sum the values for any common keys
// The function can accept any number of arrays as arguments
// The values must be numeric or the summed value will be 0

// Get the number of arguments being passed
$numargs = func_num_args();

// Save the arguments to an array
$arg_list = func_get_args();

// Create an array to hold the combined data
$out = array();

// Loop through each of the arguments
for ($i = 0; $i < $numargs; $i++) {
$in = $arg_list[$i]; // This will be equal to each array passed as an argument

// Loop through each of the arrays passed as arguments
foreach($in as $key => $value) {
// If the same key exists in the $out array
if(array_key_exists($key, $out)) {
// Sum the values of the common key
$sum = $in[$key] + $out[$key];
// Add the key => value pair to array $out
$out[$key] = $sum;
}else{
// Add to $out any key => value pairs in the $in array that did not have a match in $out
$out[$key] = $in[$key];
}
}
}

return $out;
}
$a = array('abc' => '100.000', 'def' => '50', 'ghi' => '25', 'xyz' => '10');
$b = array('abc' => '100.333', 'def' => '75', 'ghi' => '50', 'jkl' => '25');
$c = array('abc' => '100.111', 'def' => '75', 'ghi' => '50', 'uvw' => '5');

echo "<pre>";
print_r(array_mesh($a, $b, $c));
echo "</pre>";
?>
OUTPUT:

Array
(
    [abc] => 300.444
    [def] => 200
    [ghi] => 125
    [xyz] => 10
    [jkl] => 25
    [uvw] => 5
)

PHP array_insert_after() & array_insert_before()

I need to insert a key/value at a certain position in an associative array. It seems like a common issue. I was surprised to discover there wasn't a straightforward answer. Here is the method I created. If you know of a more efficient method, please let me know in the comments.

/*
 * Inserts a new key/value before the key in the array.
 *
 * @param $key
 *   The key to insert before.
 * @param $array
 *   An array to insert in to.
 * @param $new_key
 *   The key to insert.
 * @param $new_value
 *   An value to insert.
 *
 * @return
 *   The new array if the key exists, FALSE otherwise.
 *
 * @see array_insert_after()
 */
function array_insert_before($key, array &$array, $new_key, $new_value) {
  if (array_key_exists($key, $array)) {
    $new = array();
    foreach ($array as $k => $value) {
      if ($k === $key) {
        $new[$new_key] = $new_value;
      }
      $new[$k] = $value;
    }
    return $new;
  }
  return FALSE;
}
 
/*
 * Inserts a new key/value after the key in the array.
 *
 * @param $key
 *   The key to insert after.
 * @param $array
 *   An array to insert in to.
 * @param $new_key
 *   The key to insert.
 * @param $new_value
 *   An value to insert.
 *
 * @return
 *   The new array if the key exists, FALSE otherwise.
 *
 * @see array_insert_before()
 */
function array_insert_after($key, array &$array, $new_key, $new_value) {
  if (array_key_exists($key, $array)) {
    $new = array();
    foreach ($array as $k => $value) {
      $new[$k] = $value;
      if ($k === $key) {
        $new[$new_key] = $new_value;
      }
    }
    return $new;
  }
  return FALSE;
}

MySQL Indexes

For sometime now we have been looking at MySQL database. Today we will continue to dig deep in MySQL database, but we will be discussing MySQL Indexes.

Indexes allows a MySQL database to be searched more quickly and faster. Although a MySQL database can still be searched without an index but as the database begin to grow large there will be a need for an index which will be used to identify each rows in a table and hence makes searching the table a very smooth and fast one.

Types Of Indexes

INDEX
PRIMARY KEY
FULLTEXT
Creating an Index

The way to achieve fast searches is to add an index, either when creating a table or at any time afterwards. But the decision is not so simple. You must decide which columns require an index, a judgement that requires you to predict whether you will be searching any of the data in those columns. Indexes can also get complicated, because you can combine multiple columns in one index. And even when you’ve gotten to grips with all of that, you still have the option of reducing index size by limiting the amount of each column to be indexed. You can add an index to an existing table with the command below;


ALTER TABLE staff ADD INDEX(employee(20));
1
ALTER TABLE staff ADD INDEX(employee(20));
Also you can add index while creating a table using CREATE INDEX. The two options are equivalent, except that CREATE INDEX cannot be used to create an index of type PRIMARY KEY.


CREATE TABLE staff (
employee VARCHAR(65),
position VARCHAR(65),
department VARCHAR(50),
INDEX(employee(20)),
INDEX(position(16))) ENGINE MyISAM;

CREATE TABLE staff (
employee VARCHAR(65),
position VARCHAR(65),
department VARCHAR(50),
INDEX(employee(20)),
INDEX(position(16))) ENGINE MyISAM;
Primary key

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key. You can add a primary key to a table using either the commands below.


ALTER TABLE staff ADD position VARCHAR(20) PRIMARY KEY;

ALTER TABLE staff ADD position VARCHAR(20) PRIMARY KEY;
OR


CREATE TABLE staff (
employee VARCHAR(65),
position VARCHAR(65),
department VARCHAR(50),
PRIMARY KEY (position(20))) ENGINE MyISAM;

CREATE TABLE staff (
employee VARCHAR(65),
position VARCHAR(65),
department VARCHAR(50),
PRIMARY KEY (position(20))) ENGINE MyISAM;
FULLTEXT

Unlike a regular index, a FULLTEXT index in MySQL allows super-fast searches of entire columns of text. What it does is store every word in every data string in a special index that you can search using “natural language,” in a similar manner to using a search engine.

Below  are some things that you should know about FULLTEXT indexes:

FULLTEXT indexes can be used only with MyISAM tables, the type used by MySQL’s default storage engine (MySQL supports at least 10 different storage engines). If you need to convert a table to MyISAM, you can usually use the MySQL command ALTER TABLE tablename ENGINE = MyISAM; .
FULLTEXT indexes can be created for CHAR , VARCHAR , and TEXT columns only.
A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE (or CREATE INDEX ).
For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index than it is to load data into a table that has an existing FULLTEXT index.
You can create a FULLTEXT index with the command below:

MySQL

ALTER TABLE staff ADD FULLTEXT(employee,position);

ALTER TABLE staff ADD FULLTEXT(employee,position);

MYSQL - The future of ALTER IGNORE TABLE syntax

"IGNORE is a MySQL extension to standard SQL. It controls how ALTER
TABLE works if there are duplicates on unique keys in the new table
or if warnings occur when strict mode is enabled. If IGNORE is not
specified, the copy is aborted and rolled back if duplicate-key
errors occur. If IGNORE is specified, only the first row is used of
rows with duplicates on a unique key. The other conflicting rows are
deleted. Incorrect values are truncated to the closest matching
acceptable value."

This creates several issues for the MySQL server team:

1. IGNORE could remove rows from a parent table when using a foreign key relationship.
2. IGNORE makes it impossible to use InnoDB Online DDL for several operations, for example adding a PRIMARY KEY or UNIQUE INDEX.
3. IGNORE has some strange side-effects for replication. For example: DDL is always replicated via statement-based replication, and since SQL does not imply ordering, it's not clear which rows will be deleted as part of the ignore step. I also see cross-version replication problematic if future MySQL versions were to introduce more strictness, since a slave may de-duplicate more rows.

The most common case

We believe that the most common use case for IGNORE is to be able to add a UNIQUE INDEX on a table which currently has duplicate values present. i.e.

ALTER IGNORE TABLE users ADD UNIQUE INDEX (emailaddress);

In this scenario, a novice user manages to avoid auditing each entry in the users table, and simply lets MySQL pick a row to be kept, with all duplicates automatically removed.

There are two other ways to be able to do that:

Hand removal

Using the same an example as above, return a list of email addresses and PRIMARY KEY values for records that conflict:

SELECT GROUP_CONCAT(id), emailaddress, count(*) as count FROM users 
GROUP BY emailaddress HAVING count >= 2;

/* delete or merge duplicate from above query */

ALTER TABLE users ADD UNIQUE INDEX (emailaddress);

Note: This method will be the fastest way, since when not usingIGNORE, MySQL is able to use InnoDB's Online DDL.

New table + INSERT IGNORE

While this method looks very similar, internally it's semantics are quite different:

CREATE TABLE users_new LIKE users;
ALTER TABLE users ADD UNIQUE INDEX (emailaddress);
INSERT IGNORE INTO users_new SELECT * FROM users;
DROP TABLE users;
RENAME TABLE users_new TO users;

By creating a table first, the MySQL server will not have to manage rows in a foreign key relationship. The rows will also be re-sent to the slave using row-based replication, so issue (3) I mentioned above does not come into play.

Switching off referential integrity in MySQL

If you are loading very large quantities of data into MySQL, it sometimes makes sense to switch off foreign key constraints to enable the data to load faster. There are two commands for doing this:


mysql> SET foreign_key_checks = 0;

That will switch off foreign key checks for the current MySQL session, and:


mysql> SET GLOBAL foreign_key_checks = 0;

That will switch off foreign key checks at a MySQL server level.

Finally, to check the current setting of foreign_key_checks use this command:

mysql> SHOW Variables WHERE Variable_name='foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)

Killing all running queries on MySQL

On occasion you might need to kill all currently running queries against your MySQL server, without having to restart the service.

I do a lot of work on ETL (Extract Transform Load) applications, where you may have very long-running queries hanging around locking resources required by your data loader, that you will want to kill before starting a new data load.

Another scenario is where you have an application that is misbehaving by issuing many queries that are impacting other users of the MySQL server: so long as each application is connecting using a different MySQL user account (this should be a given), you can kill the queries for just that user.

MySQL greater than version 5.1, it will store the process ID of each client connection running a query in the information_schema database. We can query this database, then build up a series of KILL statements dynamically to kill each running query. Here is the main statement:


mysql> SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') FROM information_schema.processlist WHERE user <> 'system user' INTO OUTFILE '/tmp/killqueries.sql';

One you run that, check the contents of the /tmp/killqueries.sql in another terminal you should see something like this:


-bash-4.1$ cat /tmp/killqueries.sql
KILL QUERY 7; KILL QUERY 6;

Back at your MySQL prompt, you can now run that script directly:


mysql> SOURCE /tmp/killqueries.sql

Finally, if you only want to kill the queries belonging to a specific MySQL user account, you can modify the original query like so:


mysql> SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') FROM information_schema.processlist WHERE user = 'baduser' INTO OUTFILE '/tmp/killqueries.sql';

Technical screening questions for a MySQL DBA

In the past year, I have interviewed dozens of DBAs with a view to hiring a MySQL DBA. I have found that while many DBAs from other backgrounds, for example MS SQL or Oracle, claim to also have MySQL experience, a lot of them fail the screening once we get into the technical nuances of MySQL specifics.

For example, here are a few typical screening questions I ask, along with the approximate answers I expect to get back:

What are the two main storage engines in MySQL, and when would you use either?

MyISAM and InnoDB. InnoDB is transaction-safe, has row-level locking (MyISAM has table level locking), and supports foreign keys. MyISAM does not support foreign keys and is not ACID compliant, so you should only consider using MyISAM for new projects if you have specific reasons to do so (e.g. to use full-text support in MyISAM). This is basic MySQL stuff, but I have had candidates struggle to list off a few.

What kind of replication can you have with MySQL?

Here I am looking for experience in setting up master-slave and master-master configurations, along with using load balancers. It would be good if a candidate also knows what types of replication there are (row level versus query level, which is based on the query log). Another gotcha: trouble-shooting replication lag.

What are the different levels of transaction isolation level? What are they trying to prevent?

There are four levels, ranging from READ UNCOMMITED (not safe as it allows dirty reads) to SERIALIZABLE. READ COMMITED prevents dirty reads, REPEATABLE READ prevents non-repeatable reads, and SERIALIZABLE prevents phantom reads. A candidate should know what these are.

How would you make a default MySQL install secure?

Set the root user password, remove remote root user access, remove test database, remove anonymous user, set up new application-specific accounts with strong passwords and tight host and grant access, enable SSL etc.

I have a longer list that I work through, but this is a good sample. To be honest, given the widespread use of MySQL and the amount of highly-paid work available, I am surprised that there are not more DBAs building a career around it. If your are interested in pursuing this career however, the above areas are well worth studying.