Databases
NoSQL vs SQL
SQL <=> NoSQL
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