Hall of Fame

[dtiberio]

your databases doesn't have any indexes. this seriously hurts performance.

you need to use a command like this when you set up the database (or anytime thereafter but only 1 time):

alter table vibe_playlist_data add index playlist_idx (playlist);

you build indexes based on select statements. so look at this statement:

Code Select Expand

SELECT id ,title from vibe_videos where id < 1000 and id in(select video_id from vibe_playlist_data where playlist>0 ) order by id desc;

you are selecting data from "vibe_playlist_data where playlist>0" so we want an index on the "playlist" column

you are also selecting "from vibe_videos where id < 1000 " so you need an index on the id column. if it is a 'primary key' then it already has an index.

I will send you all of the commands to add indexes to all of your tables but it will take me a while to find all of them. it will increase performance dramatically.

[dtiberio]

here is another one:

Code Select Expand

alter table vibe_users_friends add index uid_idx (uid);

[dtiberio]

and another:

Code Select Expand

alter table vibe_ads add index ad_type_idx (ad_type);

remember, you just type the command 1x for the database during setup or afterwards, and then never again.

use "show index from vibe_ads" to see the index list

[dtiberio]

also use:

Code Select Expand

alter table vibe_ads add index ad_spot_idx (ad_spot);

[dtiberio]

and add this:

Code Select Expand

alter table vibe_crons add index cron_type_idx (cron_type);

[dtiberio]

this shows you how the indexes work:

Code Select Expand

mysql> explain SELECT id ,title from vibe_videos where id < 1000 and id in(select video_id from vibe_playlist_data where playlist>0 ) order by id desc;
+----+--------------------+--------------------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type        | table              | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+--------------------+--------------------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | PRIMARY            | vibe_videos        | range | PRIMARY       | PRIMARY      | 4       | NULL |  899 | Using where |
|  2 | DEPENDENT SUBQUERY | vibe_playlist_data | range | playlist_idx  | playlist_idx | 4       | NULL |   53 | Using where |
+----+--------------------+--------------------+-------+---------------+--------------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql>

note that without the index, it pulls 899 rows. but with the index I made, it only pulls 53 rows. so it is a lot faster.

[PHPVibe A.]

Hi tiberio,
I'll forward this to the dev team.
Thank you!

[dtiberio]

Code Select Expand

alter table vibe_videos add index views_idx (views);

alter table vibe_videos add index pub_idx (pub);

[dtiberio]

you should change 300 to be whatever value you think is good, I just picked it randomly.

Code Select Expand

alter table vibe_videos add index source_idx (source(300));
alter table vibe_videos add index tmp_source_idx (tmp_source(300));

[dtiberio]

this command will guarnatee that no one can use the same email twice in the user table:

Code Select Expand

alter table vibe_users add unique email_uni (email);

after you run this on the mysql server, you will not be able to add any entries if the email already exists in another entry.

[dtiberio]

this command will prevent the database from having the same username twice:

Code Select Expand

alter table vibe_users add unique username_uni (username);

[dtiberio]

this will prevent the same video from appearing twice in the database:

Code Select Expand

mysql> alter table vibe_videos add unique source_uni (source(255));
ERROR 1062 (23000): Duplicate entry 'http://www.youtube.com/watch?v=vLOjf_VQxd8' for key 'source_uni'
mysql>

you will notice mine gave an error because one of the videos appears twice. after I delete all the duplicate source entries, it will lock them in so no video can be added a second time.

[dtiberio]

this command will show how many duplicate urls (source) are in the db:

Code Select Expand

select source as c1, count(*) as c2 from vibe_videos group by c1 having c2 > 1 order by c2 desc ;

mine has 149 duplicates that need to be cleared out before I can add the unique column to prevent future duplicates.

[dtiberio]

to prevent duplicate category names for the same parent category:

Code Select Expand

alter table vibe_channels add unique cat_uni (child_of, cat_name);

also note that if you add a UNIQUE to a column, you don't need to add an INDEX. the UNIQUE is both for keep the column unique and it is also an index.

[dtiberio]

also use this command:

Code Select Expand

alter table vibe_options add unique option_name_uni (option_name);

[dtiberio]

for the likes table, you want each user id to only like a video id 1 time, so you add a unique uid/vid index:

Code Select Expand

alter table vibe_likes add unique uid_uni (uid, vid);

[dtiberio]

so in summary:

add a UNIQUE index to any column or set of columns that you want to appear only 1 time in the database, such as email address, usernames, embed code, etc. it will also speed up searches on those columns. no second INDEX command is necessary.

add an INDEX index to any column that you might select via a WHERE clause in an SQL select or update or delete, unless it is a PRIMARY KEY. use show index from table_name to see which indexes exist and which have a primary key. call the indexed columns first. if your index is on vid but not playlist, use "where (vid = 1) and (playlist = 10)". if the index were on playlist but not vid, use "where (playlist = 10) and (vid = 1)". that is ho wthey tables are optimized.

use the EXPLAIN SQL command to see if an index is being used. you type EXPLAIN before you type SELECT, such as "explain select * from table_name". it shows you every index it has the ability to use, not the one that it will definitely use.

if a column is text or varchar, you need to put the number of characters in length for the index. if it is too short, it will not index properly. for example, urls have http, so if the index is only 4 characters long, it thinks all urls are the same beause the index would only contain 'http' for each url. if in doubt, make the length equal to the size of the varchar or text.

only enter each INDEX command 1 time on the database and never again. it is possible to put the same index twice and it slows the system down.

if you do a 2 column index such as (uid, vid) you don't need another index on just (uid) but you would need a second index on just (vid) if you want to select using the vid column and not the uid column. so it works from left to right when it goes through the indexes.

if you have any quesitons, let me know

[dtiberio]

as you can see from this test, if I try to add a source url to the video table, and it already exists, the database will refuse to add the duplicate url:

WHAT ALREADY EXISTS:

Code Select Expand

|  1 |     1 |       |   1 |       2 | 2014-05-12 11:35:11 |        0 | http://www.youtube.com/watch?v=WZlwaR87WJA |            | Friday Reads May 9th 2014 | http://i1.ytimg.com/vi/WZlwaR87WJA/mqdefault.jpg |       65 | My Goodreads: http://www.goodreads.com/user/show/6315232                                                                                                                                                                                                                                     |      |        7 |     0 |     0 |        0 |    0 |       |        |     |       0 |

WHAT CAN'T BE ADDED (SAME SOURCE)
mysql> insert into vibe_videos (source) values ("http://www.youtube.com/watch?v=WZlwaR87WJA");
ERROR 1062 (23000): Duplicate entry 'http://www.youtube.com/watch?v=WZlwaR87WJA'; for key 'source_uni'
mysql>

[dtiberio]

this is from vibe_channels:

Code Select Expand

| cat_id   | int(11)      | NO   | PRI | NULL    | auto_increment |

this is what I changed iin vibe_videos:

Code Select Expand

| category    | smallint(5) unsigned  | NO   |     | NULL    |                |

they both hold the same value, so they both should be the same datatype, so the first needs to be change from 'int' to 'smallint unsigned'.

[PHPVibe A.]

Hi, good stuff.
But when it comes to restricting one video source entry, it will generate an huge complain rate if you have an open website. We won't add that in the cms.
But this clearly depends on everyone's tactic of post-acquisition development.

[dtiberio]

no, you need to add this to your database. it is a matter of data integrity.

every username, email address, 'id' column, and url should have a UNIQUE index.

when the database becomes large you will increase the chance of running into problems.