A small optimization that will happen in 1.0 FINAL

symtab · 2011-04-12 08:49:33

Hi,

While testing RC1 and preparing for the packaging, i've come across the following problem (not really a problem right now, but it can be in the future). The current video table is to large (to many columns)
and while the view view pages work perfectly (very fast), the most recent/most popupar, top rated, top favorited, longest, being watch pages (all with pagination) will be slow once you have > 100.000 videos
and a lot of visitors. So here's what i will do:

Current testing example:

mysql> describe SELECT v.video_id, v.title, v.slug, v.rating, v.rated_by, v.duration, v.thumb, v.thumbs, v.total_views, v.add_time, v.ext, v.premium, u.username FROM video AS v LEFT JOIN user AS u ON (u.user_id = v.user_id) WHERE v.status = 1 ORDER BY v.total_views DESC LIMIT 672,28;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra                       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------------------+
|  1 | SIMPLE      | v     | ref    | status        | status  | 1       | const         | 2252 | Using where; Using filesort |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | asp.v.user_id |    1 |                             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------------------+
2 rows in set (0.00 sec)
mysql>

As you can see mysql has to check 2252 (all the rows) in order to actually do the pagination and fetch the videos. So i will split the
video table in more tables.

Final testing example:

mysql> describe SELECT v.video_id, v.duration, u.username FROM video_views AS vv FORCE INDEX (`total_views`) INNER JOIN video AS v ON (v.video_id = vv.video_id AND v.status = 1) INNER JOIN user AS u ON (u.user_id = v.user_id AND u.status = '1') ORDER BY vv.total_views LIMIT 672,28;
+----+-------------+-------+--------+------------------------+-------------+---------+-----------------+------+-------------+
| id | select_type | table | type   | possible_keys          | key         | key_len | ref             | rows | Extra       |
+----+-------------+-------+--------+------------------------+-------------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | vv    | index  | NULL                   | total_views | 8       | NULL            |  700 |             |
|  1 | SIMPLE      | v     | eq_ref | PRIMARY,user_id,status | PRIMARY     | 4       | asp.vv.video_id |    1 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY,status         | PRIMARY     | 4       | asp.v.user_id   |    1 | Using where |
+----+-------------+-------+--------+------------------------+-------------+---------+-----------------+------+-------------+
3 rows in set (0.00 sec)

mysql>

As you can see mysql has to check only 700 (and this can be optimized even better) in order to actually do the pagination and fetch the videos (the FORCE INDEX will not be required once there are more rows, because mysql will apply it automatically).

pctv · 2011-04-13 11:04:47

hmmmm

mfeat50 · 2011-04-13 11:54:56

Very interesting. I like this.

Pornogoddess · 2011-04-13 12:34:28

Love how you're always finding solutions to problems that don't exist, yet.

Eri · 2011-04-13 13:37:41

nice
my site getting slower and slower by the time, when i change to ASP i will not have any problem

pctv · 2011-04-14 05:46:00

Pornogoddess wrote:

Love how you're always finding solutions to problems that don't exist, yet.

well... technically it already exist! coz he has a customer who has 100,000+ videos already..
lol...

symtab · 2011-04-14 06:54:26

I have a site with 158468 videos :-)

pctv · 2011-04-14 07:06:52

question is.... are those embedded or real videos?

symtab · 2011-04-14 07:08:51

embedded, but that doesnt matter from the mysql optimization point of view

pctv · 2011-04-14 07:37:16

true!

hehe

symtab · 2011-04-18 23:04:14

So i just migrated a site that has > 103000 videos and the Most Rated, Most Popular, Most Downloads are kinda slow, so this optimization needs to happen soon.

Adult Script Pro Community Forums

#1 2011-04-12 08:49:33

A small optimization that will happen in 1.0 FINAL

#2 2011-04-13 11:04:47

Re: A small optimization that will happen in 1.0 FINAL

#3 2011-04-13 11:54:56

Re: A small optimization that will happen in 1.0 FINAL

#4 2011-04-13 12:34:28

Re: A small optimization that will happen in 1.0 FINAL

#5 2011-04-13 13:37:41

Re: A small optimization that will happen in 1.0 FINAL

#6 2011-04-14 05:46:00

Re: A small optimization that will happen in 1.0 FINAL

#7 2011-04-14 06:54:26

Re: A small optimization that will happen in 1.0 FINAL

#8 2011-04-14 07:06:52

Re: A small optimization that will happen in 1.0 FINAL

#9 2011-04-14 07:08:51

Re: A small optimization that will happen in 1.0 FINAL

#10 2011-04-14 07:37:16

Re: A small optimization that will happen in 1.0 FINAL

#11 2011-04-18 23:04:14

Re: A small optimization that will happen in 1.0 FINAL

Board footer