MySQL: TEXT and VARCHAR Speed and Size

MySQL

Does varchar and Text affect the database size?
Also, do they differ in speed?

I have a Database that will have at least 4000 to 8000 new rows per day.
I'm quite worried about how it performs. This is my first time setting up an Enterprise-grade system and database though.

Best Answer

Use what you need and don't worry about performance. But use only what you need. There are minor inefficiencies in saying TEXT when VARCHAR(255) will suffice. Ditto for VARCHAR(255) when VARCHAR(20) will suffice.

Here's why: If a SELECT needs build a tmp table (eg, for ORDER BY), it first tries to use a MEMORY tmp table (better), but may have to fall back on a MyISAM table (slower). Any kind of TEXT or BLOB forces MyISAM. VARCHAR turns into CHAR (for the tmp table); if the tmp table gets too big for MEMORY, it converts to MyISAM.

TINYTEXT is probably never a good idea.

A related issue: CHAR(...) is useful only if the field is (1) constant length, and (2) declared with the appropriate CHARACTER SET. A common mistake is putting a fixed-length hex string in a utf8 CHAR field.

VARCHAR and TEXT (all sizes) have a small length field prefixing them. This is relatively insignificant.

Related Solutions

Mysql – Storing a large number of small size entries in MySQL

It depends on exactly what you're trying to do with the data -- if it's only being used for graphing, and you don't need high resolution the further out it goes (eg, you're not trying to plot a graph with full temporal resolution for a day from 1 year ago), you might actually want to look at RRDTool rather than a relational database.

If you're going to ever need to do analysis of the values (how often does a place get above 80°F?), you'll want to store discrete values, not an XML structure; but you could also use flat file stuctures that are meant for dealing with this type of data (eg, CDF, NetCDF ... maybe even HDF )

update :

I'd store each time as a separate record, as it makes it easier to adjust the granularity when graphing. For instance, to extract the high/low/mean for each hour:

SELECT   min(date_obs),min(temp),max(temp),avg(temp)
FROM     observations
WHERE    date_obs between ...
GROUP BY floor(unix_timestamp(date_obs)/3600)

Also, it allows you to change the cadence for the measurements without needing to change the table structure.

Mysql – Logging only theSQL logins on heavily used system

I really wish I could take credit for this, but I can't. I chanced across this while thinking about your question:

http://www.fromdual.com/mysql-logon-and-logoff-trigger-for-auditing

It never occurred to me to call a stored procedure from init_connect... but I (somewhat enthusiastically, I might add) tested this solution before posting it here, and found that it works exactly as described, on off-the-shelf MySQL 5.5.30.

It logs every login of every user who lacks the SUPER privilege. This is a bit of a limitation, except, of course, for the fact that a SUPER user could manipulate the audit table anyway.

To capture the logouts with similar logic, you have to modify the server source, since init_connect does not have a built-in counterpart that gets executed at the end of the session.

Best Answer

Related Solutions

Mysql – Storing a large number of small size entries in MySQL

Mysql – Logging only theSQL logins on heavily used system

Related Question