How you can build a "Hello World" PHP extension in five steps

Taken from https://stackoverflow.com/questions/3632160/how-to-make-a-php-extension

A Debian based OS is required, because we need to fetch some tools and dependencies with apt-get.

Step 1 - Setup Build Environment / Requirements

A PHP Extension is compiled C code. We need a shell (should already be installed), an editor (your choice), a compiler (here we'll use GCC), PHP itself and PHP development dependencies for the build.

sudo apt-get install build-essential php7.0 php7.0-dev

Step 2 - Config

We need to describe our extension and the files forming it in a basic configuration file:

File: config.m4

PHP_ARG_ENABLE(php_helloworld, Whether to enable the HelloWorldPHP extension, [ --enable-helloworld-php Enable HelloWorldPHP])

if test "$PHP_HELLOWORLD" != "no"; then
    PHP_NEW_EXTENSION(php_helloworld, php_helloworld.c, $ext_shared)
fi

As you can see, the NEW_EXTENSION contains a C file: php_helloworld.c.

Step 3 - Code

Let's create the C code for our extension.

Firstly, we create a header file:

php_helloworld.h

// we define Module constants
#define PHP_HELLOWORLD_EXTNAME "php_helloworld"
#define PHP_HELLOWORLD_VERSION "0.0.1"

// then we declare the function to be exported
PHP_FUNCTION(helloworld_php);

Secondly, we create the source file:

php_helloworld.c

// include the PHP API itself
#include <php.h>
// then include the header of your extension
#include "php_helloworld.h"

// register our function to the PHP API 
// so that PHP knows, which functions are in this module
zend_function_entry helloworld_php_functions[] = {
    PHP_FE(helloworld_php, NULL)
    {NULL, NULL, NULL}
};

// some pieces of information about our module
zend_module_entry helloworld_php_module_entry = {
    STANDARD_MODULE_HEADER,
    PHP_HELLOWORLD_EXTNAME,
    helloworld_php_functions,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    PHP_HELLOWORLD_VERSION,
    STANDARD_MODULE_PROPERTIES
};

// use a macro to output additional C code, to make ext dynamically loadable
ZEND_GET_MODULE(helloworld_php)

// Finally, we implement our "Hello World" function
// this function will be made available to PHP
// and prints to PHP stdout using printf
PHP_FUNCTION(helloworld_php) {
    php_printf("Hello World! (from our extension)\n");
}

Step 4 - Build

Now, we are ready to build the extension.

First we prepare the build environment for a PHP extension:

phpize

Then we configure the build and enable our extension:

./configure --enable-php-helloworld

Finally, we can build it:

make
sudo make install

Step 5 - Test

To test our PHP extension, lets load the helloworld_php.so extension file and execute our function helloworld_php():

php -d extension=php_helloworld.so -r 'helloworld_php();'

Done :)

Faster Pagination in Mysql – Why Order By With Limit and Offset is Slow?

Taken from https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/

Queries with LIMITs and OFFSETs are common in application that require pagination and in some cases might work well for a while.

In many cases though, they become slow and painful once the OFFSET has a high value.

Why OFFSET is so slow?

Well, in most cases, low offset queries are not slow. The problem starts with high OFFSET values.

If your query is using the following limit clause: “LIMIT 50000, 20”, it’s actually requesting the database to go through 50,020 rows and throw away the first 50,000. This action can have a high cost an impact response time.

We tested the following OFFSET values with the following query, to present the performance deterioration as the OFFSET grows.

The query was executed on a table that holds user performed events (an analytics table) with 150,000 records. The data is real user information and not auto generated.

SELECT
    *
FROM
    events
WHERE
    date > '2010-01-01T00:00:00-00:00'
        AND event = 'editstart'
ORDER BY date
LIMIT 50;

Offset Query Duration (ms)
0 1
50 1
1000 13
10000 150
25000 500
50000 930
100000 1750

How to optimize slow OFFSET queries?

To optimize slow OFFSET queries, you can either limit the amount of permitted pages in a pagination view, or simply just not use OFFSET.

A good alternative for using OFFSET will be the Seek Method, which is also highly recommended by both Lukas Eder and Markus Winand in their blogs.

In simple words, the seek method is all about finding a unique column or set of columns that identifies each row. Then, instead of using the OFFSET clause, we can just use that unique value as a bookmark that presents the position of the last row we’ve fetched and query the next set of rows by starting from this position in the WHERE clause.

For example, looking at the queries we executed before, assuming the last event id in offset 999,999 was ‘111866’, the query will be:

SELECT    *
FROM
    events
WHERE
    (date,id) > ('2010-07-12T10:29:47-07:00',111866)
        AND event = 'editstart'
ORDER BY date, id
LIMIT 10

Please note that you need to make sure to order by the unique columns, so that the order is always kept the same between pages, otherwise you might get an unexpected behavior.

This is a comparison of the performance between both methods. The interesting observation here is not only that the performance of the Seek method is better, but that it’s also more stable no matter how far you paginate into the table.

Spiral Matrix Problem - My solution

I read about this problem while practicing for an interview at Facebook.

You need to write a function that creates a matrix of n*n; where every number from 1 to n*n goes around the square matrix like a spiral:

print(matrix(10));

Result:

[ 1,  2,  3,  4,   5,  6,  7,  8,  9, 10]
[36, 37, 38, 39,  40, 41, 42, 43, 44, 11]
[35, 64, 65, 66,  67, 68, 69, 70, 45, 12]
[34, 63, 84, 85,  86, 87, 88, 71, 46, 13]
[33, 62, 83, 96,  97, 98, 89, 72, 47, 14]
[32, 61, 82, 95, 100, 99, 90, 73, 48, 15]
[31, 60, 81, 94,  93, 92, 91, 74, 49, 16]
[30, 59, 80, 79,  78, 77, 76, 75, 50, 17]
[29, 58, 57, 56,  55, 54, 53, 52, 51, 18]
[28, 27, 26, 25,  24, 23, 22, 21, 20, 19]

These are the notes I took to break down the problem:

After I solved it I researched other solutions but (modesty apart) I think my solution is prettier :-p.

Solution code in Javascript:

function print(m) {
  for (let i = 0; i < m.length; i++) {
    console.log(m[i]);
  }
}

function matrix(n) {
  if (n < 0) return [];
  let p = 0; // pivot
  let r = n + n - 1;
  let m = [];
  for(let i=0; i<n; i++) {
      m[i] = new Array(n);
  }
  
  let i = 0, j = 0, last = n * n;
  for (let k = 1; k <= last; k++) {
    // turn every last - floor(r^2/4)
    m[j][i] = k;
    let s = (last - Math.floor((r*r)/4));
    if(k === s) {
      p++;
      r--;
      if (p > 3) p = 0;
    }
    if (p === 0) i++;
    else if (p === 1) j++;
    else if (p === 2) i--;
    else if (p === 3) j--;
  }
  return m;
}