Discussion: Mysql Db Performance

[madppiper <pa...@mutschler.ch>]

Hey everyone,

while taking a closer look at the fieldtypemysql.xml file i noticed that we
are using predominantly varchars, which got me thinking. I know out of my
own experience (and from several authors), that the use of dynamic datatypes
results in increased query times. A well formatted database table may in
times be up to 6x the original speed on update  & delete functions... 

>From my point of view, there is really no need for us to stick to varchars
and blobs anyway. We are already delimiting the size of the varchar field
(20 chars, 100m or 255) and could do the very same with chars as well. I
know that this may result in bigger overall filesizes for the database and I
am fully aware of that downfall, but I think that we could gain alot from
this change... 


Also: I know that this only takes effect if we change ALL the dynamic fields
to static ones, a combination of both will not really have an effect at all. 


So what are your thoughts on this?

Cheers,
Paul
-- 
View this message in context: http://www.nabble.com/Discussion%3A-Mysql-Db-Performance-tp21909393p21909393.html
Sent from the OFBiz - Dev mailing list archive at Nabble.com.

Re: Discussion: Mysql Db Performance

[David E Jones <da...@hotwaxmedia.com>]

The most important thing I know about performance is that until you  
know with proof from tests you're just guessing, and even with lots of  
experience acting on a guess can hurt as much as help. In fact, ask  
anyone with a lot of profiling experience and they'll know based on  
lots of wrong guesses that it is useless to try to optimize without  
tests, and also without tests you never know if you did any good or  
not (or did any harm...).

So, if this is something that interests you I would invite you to do  
some testing and report the differences in performance and storage  
impact for a few different scenarios. Based on that we can have a real  
discussion about it and make some decisions.

-David


On Feb 9, 2009, at 2:22 AM, madppiper wrote:

>
> Hey everyone,
>
> while taking a closer look at the fieldtypemysql.xml file i noticed  
> that we
> are using predominantly varchars, which got me thinking. I know out  
> of my
> own experience (and from several authors), that the use of dynamic  
> datatypes
> results in increased query times. A well formatted database table  
> may in
> times be up to 6x the original speed on update  & delete functions...
>
> From my point of view, there is really no need for us to stick to  
> varchars
> and blobs anyway. We are already delimiting the size of the varchar  
> field
> (20 chars, 100m or 255) and could do the very same with chars as  
> well. I
> know that this may result in bigger overall filesizes for the  
> database and I
> am fully aware of that downfall, but I think that we could gain alot  
> from
> this change...
>
>
> Also: I know that this only takes effect if we change ALL the  
> dynamic fields
> to static ones, a combination of both will not really have an effect  
> at all.
>
>
> So what are your thoughts on this?
>
> Cheers,
> Paul
> -- 
> View this message in context: http://www.nabble.com/Discussion%3A-Mysql-Db-Performance-tp21909393p21909393.html
> Sent from the OFBiz - Dev mailing list archive at Nabble.com.
>