<pedrocorreia.net ⁄>
 

<MySQL: TEXT vs. VARCHAR Performance ⁄ >




clicks: 3095 3095 2011-06-12 2011-06-12 goto programacao myNews programacao  Bookmark This Bookmark This


Starting with MySQL 5.0.3, the maximum field length for VARCHAR fields was increased from 255 characters to 65,535 characters. This is good news, as VARCHAR fields, as opposed to TEXT fields, are stored in-row for the MyISAM storage engine (InnoDB has different characteristics). TEXT and BLOB fields are not stored in-row - they require a separate lookup (and a potential disk read) if their column is included in a SELECT clause. Additionally, the inclusion of a TEXT or BLOB column in any sort will force the sort to use a disk-based temporary table, as the MEMORY (HEAP) storage engine, which is used for temporary tables, requires. Thus, the benefits of using a VARCHAR field instead of TEXT for columns between 255 and 65k characters seem obvious at first glance in some scenarios: potentially less disk reads (for queries including the column, as there is no out-of-row data) and less writes (for queries with sorts including the column).

Following a review of my MySQL server instance's performance metrics (mysqltuner.sh and mysql tuning-primer.sh point out interesting things), I found that approximately 33% of the server's temporary tables used on-disk temporary tables (as opposed in-memory tables). On-disk temporary tables can be created for several reasons, most notably if the resulting table will be larger than the minimum of MySQL's tmp_table_size/max_heap_table_size variables OR when a TEXT/BLOB field is included in the sort.



este é só um excerto do artigo, para aceder ao artigo completo, clique no link em baixo:
this is just a small excerpt from the article, to access the full article please click in the link below:

http://nicj.net/2011/01/20/mysql-text-vs-varchar-performance




Subscribe News RSS  Subscribe News Updates by E-mail





myNews <myNews show="rand" cat="programacao" ⁄>

RouterJs: easy routing for your ajax Web applications new ...

RouterJs is a simple router for your ajax web apps. It's build upon History.js which means that Rout (...)

clicks: 14983 14983 2012-05-14 2012-05-14 goto url (new window) haithembelhaj.g... goto myNews programacao


Backbone computed properties new ...

This gist shows one way to implement read- and write-enabled computed properties on a Backbone Model (...)

clicks: 14728 14728 2012-05-13 2012-05-13 goto url (new window) https://gist.gi... goto myNews programacao


Android Query new ...

Android-Query (AQuery) is a light-weight library for doing asynchronous tasks and manipulating UI el (...)

clicks: 14986 14986 2012-05-12 2012-05-12 goto url (new window) code.google.com... goto myNews programacao


Create Instagram Filters With PHP new ...

In this tutorial, I'll demonstrate how to create vintage (just like Instagram does) photos with PHP (...)

clicks: 14429 14429 2012-05-12 2012-05-12 goto url (new window) net.tutsplus.co... goto myNews programacao


HTML5 jQuery Paint Plugin new ...

Websanova Paint is a HTML5 canvas based jQuery plugin. It allows you to free paint on a canvas area (...)

clicks: 25373 25373 2012-05-12 2012-05-12 goto url (new window) websanova.com/t... goto myNews programacao


Real-time Applications With Node.js and Socket.IO new ...

Hey everyone! Sorry about the long pause since the last blog post, life has been quite hectic for th (...)

clicks: 14902 14902 2012-05-11 2012-05-11 goto url (new window) codingcookies.c... goto myNews programacao


Sass vs. LESS vs. Stylus: Preprocessor Shootout new ...

CSS3 preprocessors are languages written for the sole purpose of adding cool, inventive features to (...)

clicks: 14203 14203 2012-05-11 2012-05-11 goto url (new window) net.tutsplus.co... goto myNews programacao


15 Handpicked jQuery Drop Down Menus Tutorials new ...

Here we are presenting another brilliant collection of 15 jQuery navigation menu that you can downlo (...)

clicks: 14001 14001 2012-05-10 2012-05-10 goto url (new window) smashingapps.co... goto myNews programacao