SQL <=> NoSQL

https://rickosborne.org

MySQL is easier ... hmm ... NO!

It is impossible to hide complexity!

+----+--------------------+----------------------+--------+----------------------------------------+----------------------------------+---------+--------------------------------------------+------+--------------------------+
| id | select_type        | table            | type   | possible_keys                | key                    | key_len | ref                                   | rows | Extra                      |
+----+--------------------+----------------------+--------+----------------------------------------+----------------------------------+---------+--------------------------------------------+------+--------------------------+
|  1 | PRIMARY            | dates_days       | range  | PRIMARY                      | PRIMARY                | 3       | NULL                                  |   31 | Using where; Using index   |
|  1 | PRIMARY            | CLI              | ref    | CLI_DATA_DOC,CLI_COD_BUSN_UN | CLI_DATA_DOC           | 4       | weborder.dates_days.date              |   23 |                            |
|  1 | PRIMARY            | FATT_ROWS        | ref    | FATT_ROWS_DOC_ID             | FATT_ROWS_DOC_ID       | 62      | TCross5_NP.CLI.DOC_ID                 |    4 |                            |
|  1 | PRIMARY            | ART_ANA          | eq_ref | PRIMARY                      | PRIMARY                | 92      | TCross5_NP.FATT_ROWS.COD_ART          |    1 |                            |
|  1 | PRIMARY            | web_categorie    | eq_ref | COD_CAT                      | COD_CAT                | 38      | TCross5_NP.ART_ANA.COD_CAT            |    1 |                            |
|  2 | DEPENDENT SUBQUERY | FATT_ROWS_LOTTI  | ref    | FATT_ROWS_LOTTI_ROW_ID       | FATT_ROWS_LOTTI_ROW_ID | 92      | TCross5_NP.FATT_ROWS.ROW_ID           |    1 |                            |
|  2 | DEPENDENT SUBQUERY | DDT_ROWS_LOTTI   | ref    | DDT_ROWS_LOTTI_COD_LOT       | DDT_ROWS_LOTTI_COD_LOT | 92      | TCross5_NP.FATT_ROWS_LOTTI.COD_LOT    |    1 |                            |
|  2 | DEPENDENT SUBQUERY | DDT_ROWS         | eq_ref | PRIMARY                      | PRIMARY                | 92      | TCross5_NP.DDT_ROWS_LOTTI.ROW_ID      |    1 | Using where                |
|  2 | DEPENDENT SUBQUERY | FATT_FOR_RIGHE   | eq_ref | PRIMARY                      | PRIMARY                | 92      | TCross5_NP.FATT_ROWS_LOTTI.COD_LOT    |    1 | Using where                |
+----+--------------------+----------------------+--------+----------------------------------------+----------------------------------+---------+--------------------------------------------+------+--------------------------+

SELECT SQL_CALC_FOUND_ROWS
    SUM(
          COALESCE(LINES_CONT.DARE_V1, 0)
        - COALESCE((
                SELECT
                    SUM(
                        COALESCE(FATT_ROWS_PROVV.IMPORTO_PROVV_V1, 0)
                      + COALESCE((FATT_ROWS.IMPORTO_V1-FATT_ROWS.QUANT_RIGA*web_product_price.prezzo) * web_product_price.percentuale_provvigione/100, 0)
                    ) as provv
                FROM CLI
                INNER JOIN FATT_ROWS
                    ON CLI.DOC_ID = FATT_ROWS.DOC_ID
                LEFT JOIN FATT_ROWS_PROVV
                    ON FATT_ROWS.ROW_ID = FATT_ROWS_PROVV.ROW_ID
                LEFT JOIN families
                    ON families.COD_ART = FATT_ROWS.COD_ART
                LEFT JOIN web_product_price
                    ON
                        web_product_price.COD_ART = FATT_ROWS.COD_ART
                    AND web_product_price.inizio <= FATT_ROWS.DATA_DOC
                    AND web_product_price.scadenza >= FATT_ROWS.DATA_DOC
                WHERE
                    CLI.COD_BUSN_UN = 'P'
                AND CLI.DATA_DOC >= LINES_CONT.COMP_ECON_DATA_INIZ
                AND CLI.DATA_DOC <= LINES_CONT.COMP_ECON_DATA_FINE
                AND CLI.COD_AGE = web_users.COD_AGE
        ), 0)
    ) as value,
    DATE_FORMAT(dates_days.`date`,  '' ) as date
FROM dates_days
LEFT JOIN LINES_CONT
    ON LINES_CONT.COMP_ECON_DATA_FINE = dates_days.`date` AND LINES_CONT.COD_CONTO = '3.01.62.01' AND LINES_CONT.DARE_V1 > 0
LEFT JOIN HEAD_CONT
    ON HEAD_CONT.HEAD_CONT_ID = LINES_CONT.HEAD_CONT_ID
LEFT JOIN web_users
    ON HEAD_CONT.COD_CF = web_users.code
WHERE
   dates_days.`date` >= '2015-02-02'
AND dates_days.`date` <= '2015-02-02'

Why When we need NoSQL?

Availability

Consistency

Partition tolerance

impossible

MySQL

PostgreSQL

MongoDB

Bigtable

CouchDB

Cassandra

All clients see the same view of data,

even in the presence of updates

All clients find some replica of data even in the presence of failure

The system properly holds even if it is partitioned

MySQL, PostgreSQL, MSSQL, Redis and Neo4j are available and consistent ; they don’t distribute data so they don’t require the partition tolerance

MongoDB, BigTable and Redis are consistent and partition tolerant. In the event of a network partition, they can become unavailable to certain types of queries.

CouchDB, Cassandra, DynamoDB are available and partition tolerant. CouchDB doesn’t guarantee consistency between any two server although they replicate data.

Consistency

What kind of Job require Consistency?

Being in agreement with itself

In agreement; compatible

Having at least one common solution, as of two or more equations or inequalities

Holding true as a group; not contradictory

Describe kind of person which may be doing job consistently?

Availability

What kind of Job require availability?

Capable of being gotten; obtainable

The quality of being at hand when needed

Qualified and willing to serve or assist

Describe kind of person which can be seen as always available?

Partition tolerance

What kind of Job require to be a Partition tolerant?

A division into parts; separation

Describe kind of person which can be seen as Partition tolerant?

The decomposition of a set into a family of disjoint sets

To divide into parts, pieces, or sections