Very Slow Select Query

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

Re: Very Slow Select Query

On Mar 27, 4:15 pm, "Antoni" wrote:
> 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

Try putting an index on ip and id_count

Re: Very Slow Select Query

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

strawberry ha escrit:
> On Mar 27, 4:15 pm, "Antoni" wrote:
> > 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
>
> Try putting an index on ip and id_count

Re: Very Slow Select Query

Antoni wrote:

> How do I put an index on them? Will I lost any data?
>
> strawberry ha escrit:
>
>>On Mar 27, 4:15 pm, "Antoni" wrote:
>>
>>>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
>>
>>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.

--
Michael Austin
Database Consultant
Domain Registration and Linux/Windows Web Hosting Reseller
http://www.spacelots.com