Programmer's Patented Dream - Part II

A brief background: my previous article described an approach to storing and retrieving data, on which you can build an application designer - an alternative to modern development platforms, but without the need for programming. The invention of this could potentially turn the whole IT world, as we know it.

I conducted a patent search and publicized the result in public to ensure that there are no architectural counterparts. After that, he received a patent and published an article with explanations, which contained several bold remarks about the volume, scalability, performance, and other things.

Of course, the article caused a large number of questions that need to be addressed separately: the difference from existing solutions and a comparative analysis of performance and plans for constructing queries to the database. And also to answer the question: what is it all about and why?

Since the topic is painful for many, and the claimed benefits are quite ambitious, the comments were rather sharp. The reason is clear - everyone in the article immediately sees “EAV”, about which serious people still write research, while the problem of productivity is generally not solvable. As I was kindly told in the comments, EAV has a lot of flaws, this is well known, and anyone who claims to have a final solution to EAV problems should receive generous kicks in order to do it.

There is only one subtlety: the article does not present the EAV per se

The most popular question was: “how does this differ from EAV, KV, Magento ...”.

It would seem that the differences lie in everything that does not fit into the listed properties:

1. Structure - in the table 5 columns and 3 indexes
2. Sampling method - data types and their interrelations (meta-data) and the data itself are described in one table

But this answer did not suit many readers, so I will try to explain in more detail.

The described structure uses the EAV directory supplemented with the ID attribute, since any attribute is also an independent entity that can have its own attributes, as well as be used as a reference value. That is, the structure is intended not only as an EAV-directory, why, in fact, I can not call it EAV.

Most importantly, EAV cannot be a self-sufficient solution; it is just a reference book, one of the elements of the system. I’m talking about a complete, self-contained solution that does not require anything extra to create the structure, data and management.

To explain how a solution differs from Datomic, Magento and tens of thousands of other solutions and products, we need tens of thousands of times to compare them. Therefore, I would venture to suggest a simple method by which in a couple of minutes you can make a comparison with any system and identify the difference, if there is one.

It is necessary to check the fulfillment of the following conditions for the compared system:

1. All data is stored in one table (see also p.3.), Containing at least the following fields: ID, Parent ID, Type ID, Value

2. For the table, the following indices are at least constructed: ID; Type ID + Value; Parent ID + Type ID.

3. There may be several tables, but they all contain the minimal structure from item 1 and the indices, differing in the type of the Value field.

4. Table contains description of data types.

5. The table contains a description of the data type attributes (from the set of types described in it)

6. The table contains data objects with a link to their type (from the set of types described in it)

7. The table contains details of the objects with a link to the parent and type

8. The selection of objects is made with the obligatory indication of the type of object.

9. A sample of the object's details is made with the obligatory indication of the parent and type

10. A sample of the object according to its details is made with the obligatory indication of the type of details.

11. (optional) Objects are linked through details containing the associated object ID type as the type.

If all the mandatory conditions are met, then you have a system that falls under the description of the note discussed here.

More significant questions cast doubt on the performance statement

For the comparative tests, two identical bases were made, one of which was built in the usual tables of a relational database, and the other using the declared methods. Below are the results of tests of two databases with query texts, time measurements and analysis of execution plans.

In the comments to the last note, a simple structure of related data was proposed for testing. I took it as a basis: this is a list of 1,048,552 books by 142,654 authors generated from random data.

CREATE TABLE `author` (
  `id` int(11) NOT NULL,
  `author` varchar(128) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `books` (
  `id` int(8) NOT NULL,
  `name` varchar(256) NOT NULL,
  `author` int(8) NOT NULL,
  `pages` int(4) NOT NULL,
  `year` int(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `author`
  ADD PRIMARY KEY (`id`),
  ADD KEY `author` (`author`);

ALTER TABLE `books`
  ADD PRIMARY KEY (`id`),
  ADD KEY `name` (`name`(255)),
  ADD KEY `author` (`author`);

ALTER TABLE `books`
  ADD CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author`) REFERENCES `author` (`id`);

:

: 1 @2.4GHz, 1GB RAM, SSD.

207 289 .

, , .

: , . .

:

SELECT author.author, books.name, books.pages, books.year 
FROM books, author 
WHERE author.author LIKE '%aro%' AND books.author=author.id 
LIMIT 1000

( ):

«aro», 465 .

193 :

:

266 :

, 266 6 ( , - , ). 264 .

:

SELECT a225.val v1_225,a217.val v2_217,a223.val v3_217,a219.val v4_217
FROM test a225
 LEFT JOIN (test r217 JOIN test a217 USE INDEX (PRIMARY)) ON r217.up=a217.id AND a225.id=r217.t AND a217.t=217
 LEFT JOIN test a223 ON a223.up=a217.id AND a223.t=223
 LEFT JOIN test a219 ON a219.up=a217.id AND a219.t=219
WHERE a225.up!=0 AND a225.t=225 AND a225.val LIKE '%aro%'
LIMIT 1000

:

, , «», , , .

, , ( ).

, , . .

, :

, . . Magento, , .

,

, , .

:

SELECT author.author, books.name, books.pages, books.year 
FROM books, author 
WHERE author.author LIKE 'lac%' AND books.author=author.id 
LIMIT 1000

3.1 :

8.4 , 6 :

, , .

, ( ):

, , . , .

—

: . :

SELECT author.author, books.name, books.pages, books.year 
FROM books, author 
WHERE books.name LIKE '% %' AND books.author=author.id 
LIMIT 50

:

: 148 2490 . 17 !

, , , , . :

: 181 25 . , , 7 .

:

SELECT author.author, books.name, books.pages, books.year 
FROM books INNER JOIN author ON books.author=author.id
WHERE books.name LIKE '% %' 
LIMIT 50

: 62 47 . . , , . , .

, , , .

, , , . , .

: () . :

SELECT author.author, books.name, books.pages, books.year 
FROM books, author 
WHERE books.pages=150 AND books.year=1972 AND books.author=author.id 
LIMIT 50

:

, 30 :

, , . .

, : , .

: DDL DML .