forum.lissyara.su

Код: Выделить всё

mysql> desc counter_segodnya;
+-----------------+-------------+------+-----+------------+----------------+
| Field           | Type        | Null | Key | Default    | Extra          |
+-----------------+-------------+------+-----+------------+----------------+
| id_zapisi       | int(15)     | NO   | PRI | NULL       | auto_increment |
| chislo          | date        | NO   |     | 0000-00-00 |                |
| vremya          | time        | NO   |     | 00:00:00   |                |
| vizit_timestamp | int(16)     | NO   | MUL | 0          |                |
| random_for_jaba | varchar(64) | NO   | MUL | 0          |                |
| ip              | varchar(15) | NO   | MUL |            |                |
| page            | int(5)      | NO   |     | 0          |                |
| user_agent      | text        | NO   |     | NULL       |                |
| referer         | text        | NO   |     | NULL       |                |
| screen_width    | int(6)      | NO   |     | 0          |                |
| screen_height   | int(6)      | NO   |     | 0          |                |
| user_color      | int(6)      | NO   |     | 0          |                |
| word_counter    | int(1)      | NO   | MUL | 0          |                |
+-----------------+-------------+------+-----+------------+----------------+
13 rows in set (0.00 sec)

mysql> explain SELECT COUNT(DISTINCT `ip`) AS `IP_this_day` FROM `counter_segodnya1` WHERE `vizit_timestamp` > '1271361600';
+----+-------------+-------------------+------+-----------------+------+---------+------+--------+-------------+
| id | select_type | table             | type | possible_keys   | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------------+------+-----------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | counter_segodnya1 | ALL  | vizit_timestamp | NULL | NULL    | NULL | 106413 | Using where |
+----+-------------+-------------------+------+-----------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Код: Выделить всё

mysql> explain SELECT COUNT(DISTINCT `ip`) AS `IP_this_day` FROM `counter_segodnya1` WHERE IP='123';
+----+-------------+-------------------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------------------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | counter_segodnya1 | ref  | ip            | ip   | 17      | const |    1 | Using where; Using index |
+----+-------------+-------------------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql>    

Код: Выделить всё

mysql> explain SELECT COUNT(DISTINCT `ip`) AS `IP_this_day` FROM `counter_segodnya` WHERE `vizit_timestamp` > '1271361600 USE INDEX ip';
+----+-------------+------------------+------+-----------------+------+---------+------+--------+-------------+
| id | select_type | table            | type | possible_keys   | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------------+------+-----------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | counter_segodnya | ALL  | vizit_timestamp | NULL | NULL    | NULL | 123195 | Using where |
+----+-------------+------------------+------+-----------------+------+---------+------+--------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT COUNT(DISTINCT `ip`) AS `IP_this_day` FROM `counter_segodnya` WHERE `vizit_timestamp` > '1271361600 USE INDEX vizit_timestamp';
+----+-------------+------------------+------+-----------------+------+---------+------+--------+-------------+
| id | select_type | table            | type | possible_keys   | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------------+------+-----------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | counter_segodnya | ALL  | vizit_timestamp | NULL | NULL    | NULL | 123197 | Using where |
+----+-------------+------------------+------+-----------------+------+---------+------+--------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>    

Код: Выделить всё

mysql> explain SELECT COUNT(DISTINCT `ip`) AS `IP_this_day` FROM `counter_segodnya` WHERE `vizit_timestamp` between '1271361600' and 10000000000;
+----+-------------+------------------+------+-----------------+------+---------+------+--------+-------------+
| id | select_type | table            | type | possible_keys   | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------------+------+-----------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | counter_segodnya | ALL  | vizit_timestamp | NULL | NULL    | NULL | 130678 | Using where |
+----+-------------+------------------+------+-----------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

mysql>   

Код: Выделить всё

# Создаём таблицу логов.
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
  `username` varchar(50) default NULL,
  `action` smallint(6) unsigned NOT NULL default '0',
  `src_email` varchar(50) default NULL,
  `dst_email` varchar(50) default NULL,
  `src_ip` varchar(15) default NULL,
  `hostname` varchar(255) default NULL,
  `helo` varchar(255) default NULL,
  `date` date default NULL,
  `size` int(11) default NULL,
  `time` timestamp NOT NULL default '',
  KEY `size-date` (`size`,`date`),
  KEY `action-date` (`action`,`date`),
  KEY `size` (`size`),
  KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=koi8r COMMENT='Таблица логов';