FEATURE: Allows field to be indexed in multiple compound indexes

[splio-aleroux]

This PR slightly modifies the 'index' argument of a field definition. It allows to define compound index on fields that are already in other index scope.

usage :

<?php
namespace SpotTest\Entity;

use Spot\Entity;

/**
 * Entity with fields in compound index and in non-compound index
 *
 * @package Spot
 */
class MultipleIndexedField extends \Spot\Entity
{
    protected static $table = 'test_multipleindexedfield';

    public static function fields()
    {
        return [
            'id'            => ['type' => 'integer', 'primary' => true],
            'companyGroup'  => ['type' => 'integer', 'required' => false, 'index' => true],
            'company'       => ['type' => 'integer', 'required' => false, 'index' => [true, 'employee']],
            'user'          => ['type' => 'string', 'required' => false, 'index' => [true, 'employee']],
        ];
    }
}

tests (mysql):

$ phpunit -c phpunit_mysql.xml                                                                                                            
PHPUnit 4.8.27 by Sebastian Bergmann and contributors.

...............................................................  63 / 199 ( 31%)
............................................................... 126 / 199 ( 63%)
............................................................... 189 / 199 ( 94%)
..........                                                      199 / 199 (100%)

Time: 2.9 seconds, Memory: 10.00MB

OK (199 tests, 424 assertions)

[marcelloh]

Would be nice to understand what it does without trying it out. What I miss, is a description, and documentation for the website.

Where is the 'employee' in the example refer too? I assume it's another entity, or table.

• What happens if that doesn't exist?

[splio-aleroux]

Let's say i have a table as follows:

CREATE TABLE `my_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `is_valid` int(1) DEFAULT NULL,
  `type` varchar(8) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_is_valid` (`is_valid`),
  KEY `index_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

For different use case, i need to get either rows with constraints on is_valid, on type or both:

SELECT SUM(`my_table`.`quantity`) FROM `my_table` WHERE `my_table`.`is_valid` = 1 and `my_table`.`type` = "orange"

or

SELECT SUM(`my_table`.`quantity`) FROM `my_table` WHERE `my_table`.`is_valid` = 1

or

SELECT SUM(`my_table`.`quantity`) FROM `my_table` WHERE `my_table`.`type` = "virtual"

So, i would like to setup an index for both is_valid and type in addition to the existing indexes for performances considerations:

CREATE TABLE `my_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `is_valid` int(1) DEFAULT NULL,
  `type` varchar(8) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_is_valid_type` (`is_valid`,`type`),
  KEY `index_is_valid` (`is_valid`),
  KEY `index_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This PR allows such setup by defining the entity's fields as follows:

    // myTable.php

    public static function fields()
    {
        return [
            'id' => [
                'type' => 'integer',
                'primary' => true
            ],
            'is_valid'      => [
                'type' => 'bool',
                'required' => false,
                'index' => [
                    true,
                    'is_valid_type'
                ]
            ],
            'type' => [
                'type' => 'string',
                'length' => '8',
                'required' => false,
                'index' => [
                    true,
                    'is_valid_type'
                ]
            ],
            'quantity' => [
                'type' => 'integer',
                'required' => false,
                'index' => true
            ],
        ];
    }

I hope this is easier to understand now.

[marcelloh]

This is a far better explanation (for sure because you explained also what it would be in plain SQL) If this is merged, we should definitely add this to the normal documentation as well.

[splio-aleroux]

Any news here ?

[vlucas]

Yeah - sorry. I love this feature. Great job. 👍

[splio-aleroux]

Thanks !