Re: Re: Speed of DECIMAL - Dan Buettner - com.mysql.lists.mysql

7 messages in com.mysql.lists.mysqlRe: Re: Speed of DECIMAL

From	Sent On	Attachments
im...@u-tx.com	26 Oct 2006 01:36
Brent Baisley	26 Oct 2006 07:08
Jerry Schwartz	26 Oct 2006 07:25
im...@u-tx.com	26 Oct 2006 23:09
Chris	26 Oct 2006 23:52
Dan Buettner	27 Oct 2006 05:55
Jerry Schwartz	27 Oct 2006 08:16

Subject:	Re: Re: Speed of DECIMAL
From:	Dan Buettner (drbu...@gmail.com)
Date:	10/27/2006 05:55:58 AM
List:	com.mysql.lists.mysql

I'll second what Chris said, which is that all the joined columns should be of the same type for speed.

Also, your substring and LIKE comparisons are going to be problematic, as those are string operations, not numeric, and MySQL is having to convert all the decimal values to strings before comparing them. It works but as you've found can be very slow, since you a) have to convert every row, and b) have to do an unindexed comparison.

Dan

On 10/27/06, Chris <dmag...@gmail.com> wrote:

im...@u-tx.com wrote:

From: Jerry Schwartz [mailto:jsch...@the-infoshop.com]

What is going slower, INSERT / UPDATES or SELECTS?

Complex SELECTs

CHAR should make for quite efficient processing, since to a large degree nobody cares what's in there: it just slams the data in, or does a simple byte-by-byte comparison. There is probably hardware support for that kind of operation. Decimal arithmetic, on the other hand, requires more data manipulations. The size of the column probably is outweighed by the more complex data handling.

I am doing the following operations:

- Joins based on indexed columns

Did you change *all* columns involved in the joins?

Otherwise you have:

decimal joining to char

which mysql will need to convert internally to be the same type..