Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>Brief Summary:</strong></p> <p><em>(just my opinions)</em></p> <ol> <li>for email address - <code>VARCHAR(255)</code></li> <li>for username - <code>VARCHAR(100)</code> or <code>VARCHAR(255)</code></li> <li>for id_username - use <code>INT</code> (unless you plan on over 2 billion users in you system)</li> <li>phone numbers - <code>INT</code> or <code>VARCHAR</code> or maybe <code>CHAR</code> (depends on if you want to store formatting)</li> <li>posts - <code>TEXT</code></li> <li>dates - <code>DATE</code> or <code>DATETIME</code> (definitely include times for things like posts or emails)</li> <li>money - <code>DECIMAL(11,2)</code></li> <li>misc - see below</li> </ol> <p>As far as using InnoDB because <code>VARCHAR</code> is supposed to be faster, I wouldn't worry about that, or speed in general. Use InnoDB because you need to do transactions and/or you want to use foreign key constraints (FK) for data integrity. Also, InnoDB uses row level locking whereas MyISAM only uses table level locking. Therefore, InnoDB can handle higher levels of concurrency better than MyISAM. Use MyISAM to use full-text indexes and for somewhat less overhead.</p> <p>More importantly for speed than the engine type: put indexes on the columns that you need to search on quickly. Always put indexes on your ID/PK columns, such as the id_username that I mentioned.</p> <p><strong>More details:</strong></p> <p>Here's a bunch of questions about MySQL datatypes and database design (warning, more than you asked for):</p> <ul> <li><p><a href="https://stackoverflow.com/questions/3085038/what-datatype-should-i-pick">What DataType should I pick?</a></p></li> <li><p><a href="https://stackoverflow.com/questions/3243409/table-design-question">Table design question</a></p></li> <li><p><a href="https://stackoverflow.com/questions/2955613/enum-datatype-versus-table-of-data-in-mysql">Enum datatype versus table of data in MySQL?</a></p></li> <li><p><a href="https://stackoverflow.com/questions/1846254/mysql-datatype-for-telephne-number-and-address">mysql datatype for telephne number and address</a></p></li> <li><p><a href="https://stackoverflow.com/questions/1828068/best-mysql-datatype-for-grams-milligrams-micrograms-and-kilojoule">Best mysql datatype for grams, milligrams, micrograms and kilojoule</a></p></li> <li><p><a href="https://stackoverflow.com/questions/2049418/mysql-5-star-rating-datatype">MySQL 5-star rating datatype?</a></p></li> </ul> <p>And a couple questions on when to use the InnoDB engine:</p> <ul> <li><p><a href="https://stackoverflow.com/questions/20148/myisam-versus-innodb">MyISAM versus InnoDB</a></p></li> <li><p><a href="https://stackoverflow.com/questions/1082194/when-should-you-choose-to-use-innodb-in-mysql">When should you choose to use InnoDB in MySQL?</a></p></li> </ul> <p><em>I just use <code>tinyint</code> for almost everything (seriously).</em></p> <p><strong>Edit - How to store "posts:"</strong></p> <p>Below are some links with more details, but here's the short version. For storing "posts," you need room for a long text string. <code>CHAR</code> max length is 255, so that's not an option, and of course <code>CHAR</code> would waste unused characters versus <code>VARCHAR</code>, which is variable length <code>CHAR</code>.</p> <p>Prior to MySQL 5.0.3, <code>VARCHAR</code> max length was 255, so you'd be left with <code>TEXT</code>. However, in newer versions of MySQL, you can use <code>VARCHAR</code> or <code>TEXT</code>. The choice comes down to preference, but there are a couple differences. <code>VARCHAR</code> and <code>TEXT</code> max length is now both 65,535, but you can set you own max on <code>VARCHAR</code>. Let's say you think your posts will only need to be 2000 max, you can set <code>VARCHAR(2000)</code>. If you every run into the limit, you can <code>ALTER</code> you table later and bump it to <code>VARCHAR(3000)</code>. On the other hand, <code>TEXT</code> actually stores its data in a <code>BLOB</code> (1). I've heard that there may be performance differences between <code>VARCHAR</code> and <code>TEXT</code>, but I haven't seen any proof, so you may want to look into that more, but you can always change that minor detail in the future.</p> <p>More importantly, searching this "post" column using a Full-Text Index instead of <code>LIKE</code> would be much faster (2). However, <strong>you have to use the MyISAM engine to use full-text index because InnoDB doesn't support it</strong>. In a MySQL database, you can have a heterogeneous mix of engines for each table, so you would just need to make your "posts" table use MyISAM. However, if you absolutely need "posts" to use InnoDB (for transactions), then set up a trigger to update the MyISAM copy of your "posts" table and use the MyISAM copy for all your full-text searches.</p> <p><em>See bottom for some useful quotes.</em></p> <ul> <li><p><a href="http://www.htmlite.com/mysql003.php" rel="nofollow noreferrer">MySQL Data Type Chart (outdated)</a></p></li> <li><p><a href="http://dev.mysql.com/tech-resources/articles/visual-basic-datatypes.html" rel="nofollow noreferrer">MySQL Datatypes (outdated)</a></p></li> <li><p><a href="http://dev.mysql.com/doc/refman/5.0/en/data-types.html" rel="nofollow noreferrer">Chapter 10. Data Types (better details)</a></p></li> <li><p><a href="http://dev.mysql.com/doc/refman/5.0/en/blob.html" rel="nofollow noreferrer">The BLOB and TEXT Types (1)</a></p></li> <li><p><a href="http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html" rel="nofollow noreferrer">11.9. Full-Text Search Functions (2)</a></p></li> <li><p><a href="http://dev.mysql.com/doc/refman/5.0/en/char.html" rel="nofollow noreferrer">10.4.1. The CHAR and VARCHAR Types (3)</a></p></li> </ul> <blockquote> <p>(3) "Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.</p> <p>Before MySQL 5.0.3, if you need a data type for which trailing spaces are not removed, consider using a BLOB or TEXT type.</p> <p>When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.</p> <p>Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values."</p> </blockquote> <p><strong>Lastly, here's a great post about the pros and cons of VARCHAR versus TEXT. It also speaks to the performance issue:</strong></p> <ul> <li><a href="http://people.planetpostgresql.org/dfetter/index.php?/archives/24-VARCHARn-Considered-Harmful.html" rel="nofollow noreferrer">VARCHAR(n) Considered Harmful</a></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POHow to choose optimized datatypes for columns [innodb specific]?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USJohnB
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to choose optimized datatypes for columns [innodb specific]?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COhow about the post ? 1 for = "thelongpost" ? , 2= "the2ndlongpost" :).
      singulars
      1. PO
      1. USAdam Ramadhan
    2. COSorry Adam, I thought I had included another link that answered your question. Well, please see my edit for storing "posts."
      singulars
      1. PO
      1. USJohnB
    3. COShoot, I forgot to mention than InnoDB doesn't support full-text index. You have to use MyISAM. Please re-read my section on that.
      singulars
      1. PO
      1. USJohnB
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload