[geeklog-devel] Geeklog Story Size Limits

[Randy Kolenko]

You're 100% correct in that the MySQL text fields are not in line with
what the MSSQL schema I put together uses.  
 
I had to use the varchar 5000 fields out of necessity so that huge
swaths of GL code didn't have to be rewritten to support the PHP support
for text blob vs varchar fields for SQL server.  
MySQL + PHP correctly handles large text fields and can return the
entire value of a fetched text blob into a variable correctly.  However
PHP + MSSQL support, there are "difficulties" in dealing with varchar
fields vs. TEXT fields (varchars would be truncated at 256 chars if I
remember correctly).
 
So the obvious choice would be to go to the TEXT field then - however
this too posed an issue with SQL Server as you can't do "like '%...%'"
where clause searches and thus GL's search would break.
 
The immediate lesser-of-the-2-evils was to use 2 varchar 5000 fields -
breaking the soft rule for row size, but at the same time at least
allowing for most stories to work.
 
Some of these issues could potentially be resolved by tweaking the
PHP.ini settings for textlimit and textsize.  However some people just
don't have access to their INI files.  Alternatives would be to set the
textsize before any query and/or doing the appropriate cast to text or
varchar when necessary.  


-randy


> 
> 
> Hi,
> 
> Regarding MSSQL: 
> ================
> When I looked at the schema for the MS-SQL database, upon 
> first glance, it appears to define story intro and body 
> contents as VARCHAR(5000).  Surely that's not right, am I 
> misreading the Schema in mssql_tableanddata.php?
> 
> I would have perhaps expected VARCHAR(65536) to be compatible 
> with current/past MySQL TEXT limits.
> 
> For SQL Server, perhaps we should be thinking about changing 
> varchar() to text and using full text search index instead?  
> Having intro(5000) and
> body(5000) already puts us over the 8060 byte row "limit" for 
> updating. Problem there is that it may work fine now, but 
> fail later from what I understand (but have not personally 
> experienced).  SQL Server 2005 may not have quite the same 
> soft 8060 byte row updating limit.  It's possible I'm wrong 
> on this however as I'm not a seasoned SQL Server vet.
> 
> -Jeff
> 
> -----Original Message-----
> From: geeklog-devel-bounces at lists.geeklog.net
> [mailto:geeklog-devel-bounces at lists.geeklog.net] On Behalf Of 
> Alexander Hybel
> Sent: Saturday, November 24, 2007 2:09 PM
> To: Geeklog Development
> Subject: Re: [geeklog-devel] Geeklog Story Size Limits
> 
> Hello all,
> 
> Dirk Haun wrote:
> > Jeff Hare wrote:
> >> What's the chance of bumping the story body type up to MEDIUMTEXT?
> >>     
> >
> > I'd like to pass this question on to our database experts: 
> What would 
> > be the downsides? Would it, for example, require more space even if 
> > not used?
> >   
> While I'm not one of the DB experts ;-) my fat MySQL book 
> (for MySQL 4) 
> and the online documentation at 
> http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.ht
ml (here 
for MySQL 5.1) tell me that the space requirements for MEDIUMTEXT are 
minimal above TEXT.
To be precise it's 1 byte per entry!

With this you have room for 16.777.215 characters - not taking space 
requirements for Unicode characters into account. These can take up to 
three bytes space.

Other than that there doesn't seem to be a big downside.
Of course larger DB entries won't exactly lead to better performance, 
but this is only in effect if one really uses that much space.
Apparently the current TEXT size with up to 65k chars is sufficient for 
the average GL user. So these people shouldn't see or feel any 
difference at all.

-- 
Regards,
Alexander Hybel

_______________________________________________
geeklog-devel mailing list
geeklog-devel at lists.geeklog.net
http://eight.pairlist.net/mailman/listinfo/geeklog-devel

_______________________________________________
geeklog-devel mailing list
geeklog-devel at lists.geeklog.net
http://eight.pairlist.net/mailman/listinfo/geeklog-devel