Hall of Fame

[dtiberio]

this is a poor way to write an SQL query:
Select count(*) as nr from vibe_videos where source  like '%youtube.com/watch?v=8TFlBqt46cU'

because of the % sign. when you use the % sign at the beginning of the query, it has to scan the entire table. it has to go through every single listing. you want to only use % signs as far to the right as possible, such as in the code 8TF1Bqt...

all of the source urls will begin the same (http://youtube.com/watch?v=...) so if you put the % anywhere in that part, it has to look at every single record, which will be very slow. you will either have to standardize the domain name and put the full URL in the search, or put the embed code in a separate column, or maybe use MATCH AGAINST which is probably the best way.

[dtiberio]

mysql> explain Select count(*) as nr from vibe_videos where source  like '%youtube.com/watch?v=8TFlBqt46cU';
+----+-------------+-------------+-------+---------------+------------+---------+------+-------+--------------------------+
| id | select_type | table       | type  | possible_keys | key        | key_len | ref  | rows  | Extra                    |
+----+-------------+-------------+-------+---------------+------------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | vibe_videos | index | NULL          | source_uni | 767     | NULL | 25807 | Using where; Using index |
+----+-------------+-------------+-------+---------------+------------+---------+------+-------+--------------------------+
1 row in set (0.92 sec)

mysql> explain Select count(*) as nr from vibe_videos where source  like 'http://www.youtube.com/watch?v=8TFlBqt46cU';
+----+-------------+-------------+-------+-----------------------+------------+---------+------+------+--------------------------+
| id | select_type | table       | type  | possible_keys         | key        | key_len | ref  | rows | Extra                    |
+----+-------------+-------------+-------+-----------------------+------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | vibe_videos | range | source_uni,source_idx | source_uni | 767     | NULL |    1 | Using where; Using index |
+----+-------------+-------------+-------+-----------------------+------------+---------+------+------+--------------------------+
1 row in set (0.98 sec)


Note that in the first query, with the % sign, it says it scanned 25,807 rows. in the second query which I added the full href, it says it scanned only 1 row, which is way faster. (check after it says NULL).

on a big site with hundreds of thousands of sites you will have to get rid of that % sign and find another way.

[dtiberio]

instead of doing 30 separate searches to do the counts, you should do this instead:

mysql> select count(*), source from vibe_videos where (source like '%youtube.com/watch?v=uNEyGiCW11U') or (source like '%youtube.com/watch?v=F4ZzamS15Ao') group by source limit 2;
+----------+--------------------------------------------+
| count(*) | source                                     |
+----------+--------------------------------------------+
|        1 | http://www.youtube.com/watch?v=F4ZzamS15Ao |
|        1 | http://www.youtube.com/watch?v=uNEyGiCW11U |
+----------+--------------------------------------------+
2 rows in set (3.25 sec)

you can use 1 single command to get all of the records. also, always use a LIMIT, it makes the command faster. if you are search for 1 url, use LIMIT 1 and it will stop immediately when it finds the url. if you don't it will keep checking for more of the same URL. if duplicate urls are not allowed, it is a waste to search for more beyond the first

[dtiberio]

also try this command:

select count(*) as nr, source from vibe_videos group by source;

it will give you the counts for all of the urls much faster than looking at each one individually.