Posted by Captain Paralytic on 03/27/07 15:45

On 27 Mar, 16:18, "Antoni" <antonimassom...@gmail.com> 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.

[Back to original message]