Posted by Antoni on 03/27/07 15:18

Hello,

I have a table with more than 1,000,000 rows.

mysql> describe views_date;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| id_count | bigint(20) | YES | | NULL | |
| date | datetime | YES | | NULL | |
| type | int(3) | YES | | NULL | |
| ip | varchar(15) | YES | | NULL | |
| user | bigint(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

This table is used to log info on which video the visitor has viewed
and its IP address.

Everytime a visitor views a video a new row is inserted.

Then I use the following query to know if the visitor has already
viewed the video:

mysql> SELECT count(*) from views_date WHERE id_count = $videoid AND
ip = '$ip';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (6.19 sec)

mysql> explain SELECT count(*) from views_date WHERE id_count = 8731
AND ip = '121.97.245.124';
+----+-------------+------------+------+---------------+------
+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------
+---------+------+---------+-------------+
| 1 | SIMPLE | views_date | ALL | NULL | NULL |
NULL | NULL | 1089103 | Using where |
+----+-------------+------------+------+---------------+------
+---------+------+---------+-------------+
1 row in set (0.04 sec)

Can anyone give me tips on how to optimize the table to run faster
queries?

Thanks!


Antoni

[Back to original message]