Very Slow Select Query

[Antoni]

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

[strawberry]

Post by Antoni
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
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

Try putting an index on ip and id_count

[Antoni]

How do I put an index on them? Will I lost any data?

Post by strawberry

Post by Antoni
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
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

Try putting an index on ip and id_count

[Michael Austin]

Post by Antoni
How do I put an index on them? Will I lost any data?

Post by strawberry

Post by Antoni
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
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

Try putting an index on ip and id_count

see the docs for create index statement. And no you will not lose any data.

And BTW, you cannot be guaranteed that the person using that IP address has not
already viewed that particular movie. An unfortunate side-affect of DHCP.