indicizzare data

[sandroacchiardi]

ho una tabella con 400mila righe
una query è molto lenta perchè lavora su un campo data che no è indicizzato
la query è del tipo > di

la mia domanda è
essendo le date praticamente tutte diverse perchè mi memorizza anche il secondo, ha un senso indicizzare il campo data?

[deleted_29]

Non è chiaro a quale db ti riferisci.

Quindi in generale...

Originariamente inviato da sandroacchiardi
ho una tabella con 400mila righe
una query è molto lenta perchè lavora su un campo data che no è indicizzato
la query è del tipo > di

la mia domanda è
essendo le date praticamente tutte diverse perchè mi memorizza anche il secondo, ha un senso indicizzare il campo data?

la domanda non mi è chiarissima, in verità

il "> di" ha senso se questo partiziona in modo efficiente il dataset (altrimenti puoi provare NOT <= di).

Se hai X righe, ma di queste solo X-2 sono "> di", allora un indice non ti serve a nulla (in questo caso in realtà, come detto, faresti il complemento per prendere le 2).

ATTENZIONE questo vale solo se hai metodi "magici" per avere il numero di righe di una tabella, per mysql (ad esempio) ciò vale per myisam ma NON per innodb

esempio

SELECT COUNT(*) FROM tabella WHERE data > qualcosa;

può essere più convenientemente scritta (a seconda dei casi, ossia selettività, db ed engine) in

SELECT (SELECT COUNT(*) FROM tabella) - COUNT(*) FROM tabella WHERE ID <= qualcosa;

In questo esempio il punto dolente è il "count della tabella": con myisam funziona bene e, talvolta, ti consente di fare una query tipo quella che hai messo in tempi estremamente più ridotti
Esempio di conteggio SENZA where su myisam

mysql> explain select count(*) from connessioni;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

Ecco invece su una tabella innodb

mysql> explain select count(*) from documenti;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | documenti | index | NULL          | PRIMARY | 4       | NULL | 8331 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Guarda ora questo esempio

mysql> explain select count(*) from documenti where anno>2000;
+----+-------------+-----------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | SIMPLE      | documenti | range | iAnno         | iAnno | 5       | NULL | 4165 | Using where; Using index |
+----+-------------+-----------+-------+---------------+-------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

e questo

mysql> explain select count(*) from documenti where anno<=2000;
+----+-------------+-----------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | SIMPLE      | documenti | range | iAnno         | iAnno | 5       | NULL | 2240 | Using where; Using index |
+----+-------------+-----------+-------+---------------+-------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

La seconda query è il doppio più veloce rispetto alla prima
-------
Sempre con riferimento a situazioni db-specifiche, ricordo che una query RANGE "disattiva" tutti gli altri indici (se ci sono) nei vari altri "filtri".

... where (anno=2000) and (tipo='p') and (peso>20) and (data>qualcosa) significa che hai DUE range filter (peso e data), dei quali UN SOLO indice sarà usato.
attenzione sempre alla massima selettività, altrimenti ti ritrovi con qualcosa di simile ad un fullscan

-------------
Ed infine un suggerimento "generale": in questo caso è generalmente più efficiente memorizzare le date come UNIX_TIMESTAMP (quindi in campi interi).
In questo caso puoi ridurre la dimensione dell'indice, come giustamente hai segnalato, "potando" i secondi e lasciando solo la data

[sandroacchiardi]

sei stato molto preciso
io lavoro cn phpmyadmin che mi gestisce il mysql
le righe no le devo contare ma selezionare

la select è pressapoco cosi
select righe where vedi = 1 and data < oggi

[deleted_29]

Originariamente inviato da sandroacchiardi
la select è pressapoco cosi
select righe where vedi = 1 and data < oggi

in questo caso la situazione non è buona, in quanto immagino che lo "stock" di righe (con data precedente ad oggi) sia di gran lunga maggioritaria.

Ricorda che l'rdbms non è "magia", è solo "comodità".

Se hai 10.000 righe, e ne devi ritornare 9.998, questa query sarà lenta qualsiasi metodo ti possa venire in mente.

L'unica cosa che puoi fare (o meglio provare) è un covering index se non ti servono tutte le colonne (o comunque te ne bastano poche).

Se invece ti serve proprio un "select TUTTO tranne pochissimo" => non c'è molto da fare.

A meno che, in realtà, non "storicizzi" i dati ed utilizzo solo i delta [se sono statistiche, ad esempio], appoggiandoti ad una tabella riassuntiva