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 27 Mar, 16:18, "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

Why do you need the count? If they have viewed the video then a row
will be found so just:

SELECT id from views_date WHERE id_count = $videoid AND ip = '$ip'
LIMIT 1;

If it returns anything then they have viewed it. (Dunno why the video
id column should be named id_count, most strange)

Add an INDEX on id_count, ip and you're laughing.

Re: Very Slow Select Query

Hello,

How do I add an index to id_count & ip? Will any data be lost?

Thanks!

Antoni

Captain Paralytic ha escrit:
> On 27 Mar, 16:18, "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
>
> Why do you need the count? If they have viewed the video then a row
> will be found so just:
>
> SELECT id from views_date WHERE id_count = $videoid AND ip = '$ip'
> LIMIT 1;
>
> If it returns anything then they have viewed it. (Dunno why the video
> id column should be named id_count, most strange)
>
> Add an INDEX on id_count, ip and you're laughing.

Re: Very Slow Select Query

> Captain Paralytic ha escrit:
>> On 27 Mar, 16:18, "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
>>
>> Why do you need the count? If they have viewed the video then a row
>> will be found so just:
>>
>> SELECT id from views_date WHERE id_count = $videoid AND ip = '$ip'
>> LIMIT 1;
>>
>> If it returns anything then they have viewed it. (Dunno why the video
>> id column should be named id_count, most strange)
>>
>> Add an INDEX on id_count, ip and you're laughing.

Antoni wrote:
> Hello,
>
> How do I add an index to id_count & ip? Will any data be lost?
>
> Thanks!
>
> Antoni
>
Please do not top post.
Look at the ALTER TABLE syntax. No data is lost you are ADDING an index, not
taking anything away