current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

Im planning my database scheme design for my project website.
This is video table:

enter image description here

In "car" tag page, I retrieve from table in this way (PHP query):

sql = "SELECT * FROM `video` WHERE keyword='car'";
$result = $db->query($sql);
if (!$result) {
}

<?php     while ($row = $result->fetch_assoc()) { ?>

<?php echo  $row['website']; ?><br>

<?php echo  $row['url']; ?><br><br>

<?php } ?>

This is question: if I store milions of rows in video table, for i.e. 50 milions of rows, is this table scheme design good or I need create something different?

thanks for any suggestion

share|improve this question
1  
What if the video is on multiple sites? How do you handle more than a single 'keyword'? –  jeff Nov 24 '13 at 15:07
    
I'd suggest you to use: datatables.net –  A. Wolff Nov 24 '13 at 15:10

2 Answers 2

up vote 2 down vote accepted

With a proper index on the keyword column this design should be fine. But if you intend to be storing such a huge volumes of data in the table, you should definitely consider introducing paging in your SQL queries.

share|improve this answer
    
Thanks for your suggest. What do you mean for 'proper index' on the keyword column? –  Vincenzo Lo Palo Nov 24 '13 at 15:17
    
Just create an index on this column: stackoverflow.com/a/2955470/29407 If you don't know what an index is in SQL and intend to store 50M records in a table, I would more than strongly recommend you reading about indexes before doing that :-) –  Darin Dimitrov Nov 24 '13 at 15:18
    
thanks again! :) –  Vincenzo Lo Palo Nov 24 '13 at 15:19
up vote 1 down vote

Good question. Your observation about storing a large number of similar values is valid.

A solution to optimise your design is to use relational table schema. With relational tables, you take common values in fields and move them to another table, then create a link to the table.

As an (unrelated) example, instead of storing the country name in a field a million times, create a field that points to a list of countries. The benefits will be that you will store (a million times) a few bytes for a number compared to (a million times) a few hundred bytes for the name.

The benefits are greater than storage, as it is more efficient to compare numbers than strings. For example, it takes a computer ONE comparison to check if ($val == 10), compared to one check for every letter in a string (imagine running check if ($val = 'a very long string') a million times.

http://en.wikipedia.org/wiki/Relational_database

As an example. assume you have a limited number of keywords.

    CREATE TABLE video
    (
      video_id int(11) NOT NULL auto_increment,
      keyword_id int(11),
      website  varchar(255),
      url   string,
      PRIMARY KEY (video_id)
    );

    CREATE TABLE keywords
    (
     keyword_id int(11) NOT NULL auto_increment,
     keyword_name varchar(255),
     PRIMARY KEY (keyword_id)
    );

Notice that the video table has a field *keyword_id*, instead of *keyword_name*, so this will store a number, instead of a string. Examples of your data will be

 - video
id    keyword_id      url
1     1               http://domain1/path1/
2     2               http://domain2/path1/
3     2               http://domain3/path4/
   : 
 - keywords
keyword_id       keyword_name
1                short keyword
2                a long key that has many, many, many characters

If you know the keyword id, then it is easy to search for the videos.

SELECT keyword FROM keywords WHERE keyword_name = 'car';
    : 
SELECT * FROM video where keyword_id = ':keyword_id';

or

SELECT keyword_name, website, url
  FROM video
  JOIN keyword ON keyword_id
 WHERE keyword_name = 'car'

I also came across a few posts on SO.

Relational Database Design Patterns?

Relational Database and Normalization for Relational Tables

Best way with relation tables

share|improve this answer
    
thanks, very interesting suggestion! –  Vincenzo Lo Palo Nov 24 '13 at 18:36
    
Can you please show me an example about? –  Vincenzo Lo Palo Nov 24 '13 at 20:33
    
thanks for your important help! –  Vincenzo Lo Palo Nov 25 '13 at 10:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.