MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Test USER Date:December 19 2005 1:28pm
Subject:Re: CAST as SIGNED, bad idéa?
View as plain text  
Great, :)
But do you know how to write a good select query using this design?
For example if i want to select all TV with widescreen and inch greater than 28?

select * from PRODUCT_SPECS (where SPEC_ID=1 and VALUE=YES)
(and SPEC_ID=5 and VALUE>=28)

this doesnt feel right...



From: SGreen at unimin dot com
Date: December 10 2005 3:29am
Subject: Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=

--=_alternative 000D3711852570D3_=
Content-Type: text/plain; charset="US-ASCII"

MUCH BETTER!! Sorry I doubted you. However you have to remember that 
unless you declare a second numeric column in your PRODUCT_SPECS table 
then everything will be treated as strings. Sorting will be as strings, 
comparisons will be as strings, and any attempt to use them as numbers 
while they are strings will invalidate any indexes.

I would suggest a second DECIMAL column on your PRODUCT_SPECS table or be 
prepared for performance hits whenever you need numeric ordering.  If you 
compare them alphabetically, "8" comes after "1" so "8" is greater than 
"10", "100", "1000", "20", "30", or any other "word" that starts with a 
"letter" smaller than "8".

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Test USER <testuser@stripped> wrote on 12/09/2005 05:47:41 PM:

> Oh, is it really such a bad design? Here is some more.
> What is substring matches, and why do i need them?
> 
> 
> TBL_PRODUCTS
> ID   PRODUCTNAME
> 1   SAMSUNG TV
> 2   PHILIPS DVD-PLAYER
> 3   PHILIPS TV
> 4   MAXTOR DMAX
> 5   LaCie HARDDIVE
> 
> ---------------------------------------------
> 
> TBL_SPECS
> ID   DETAIL
> 1   Widescreen
> 2   VCD
> 3   DiVX
> 4   Capacity
> 5   Inch
> 
> 
> ---------------------------------------------
> 
> PRODUCT_SPECS
> PRODID      SPECID      VALUE
> 1      1      YES
> 1      5      32
> 2      2      NO
> 2      3      3.11
> 3      1      NO
> 3      5      28
> 4      4      80
> 5      4      120
> 
> -----------------------------------------
> 
> Thanks again for your help!
> 
> Quoting SGreen@stripped:
> 
> > This sounds like a simple case of bad design. 
> > 
> > You need to be able to locate specific values for various product 
> > descriptions but they are all mangled together into just one field. 
You 
> > end up trying to do substring matches and all hell breaks loose and 
> > performance hits the skids.
> > 
> > My suggestion is to somehow re-process your "value" column into 
separate
> > 
> > specific columns or child tables, one for each distinct value held in
> > the 
> > "value" field. I can identify the potential values of `hdd_size`, 
> > `monitor_size`, `monitor_resolution`,`hw_port`,`cd_supp_format`....
> > 
> > Your data is unmanageable in its present format and you need to scrub
> > and 
> > massage it into shape before what you have will be marginally useful.
> > 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> > 
> > 
> > Test USER <testuser@stripped> wrote on 12/09/2005 04:56:21 PM:
> > 
> > > Hi again :)
> > > 
> > > The table contains a column named value and is in the format 
> > varchar(255).
> > > This column contains specifications for different computer products.
> > > There is also a id column so i know which product it belongs to.
> > > 
> > > value(varchar)
> > > 80
> > > 17"
> > > 1024x768
> > > USB
> > > DiVX
> > > 
> > > For example, the first value 80 tells me with som joins that the 
> > > product maxtor 
> > > diamondmax has 80Gb capacity. And that a Philips DVD-player
> > supportsDiVX 
> > for 
> > > the last value in this example.
> > > 
> > > Now i want to select all harddrvies with a capacity greater or equal
> > to 
> > 80.
> > > Doing a "select value from tbl where value >=80 order by value
> > DESC"will 
> > give 
> > > som unexpected results.
> > > 
> > > If you have 80, 120, 250 in the database the result will be:
> > > 80
> > > 250
> > > 120
> > > 
> > > I don't really know how to solve this other than to use CAST(value 
as 
> > SIGNED).
> > > Maybe i could rebuild the database but i don't know how a good 
> > databasedesign 
> > > for this would look like :)
> > > 
> > > Thanks for you help!
> > > 
> > > Quoting SGreen@stripped:
> > > 
> > > > I misunderstood, I thought you were looking for a way of 
converting 
> > your
> > > > 
> > > > numbers-as-strings into a native numeric format. 
> > > > 
> > > > Please describe you situation better: What language are you using 
to
> > > > build 
> > > > your application. Are you composing the SQL statement client-side 
or
> > 
> > > > server-side? What kind of SQL statement are you trying to execute?
> > > > 
> > > > Your table structures (the output of SHOW CREATE TABLE ...  works
> > very 
> > 
> > > > well) and some sample data would also help.
> > > > 
> > > > Sorry for the confusion!
> > > > 
> > > > Shawn Green
> > > > Database Administrator
> > > > Unimin Corporation - Spruce Pine
> > > > 
> > > > 
> > > > Test USER <testuser@stripped> wrote on 12/09/2005 04:28:44 PM:
> > > > 
> > > > > Hello, thanks for your help!
> > > > > I dont really get it :)
> > > > > 
> > > > > You suggestion is to have a seperate column with the name 
> > numericvalue
> > > > 
> > > > and 
> > > > > insert userinput into that and add a zero, right?
> > > > > 
> > > > > Could you explain more, why when how will this help me :)
> > > > > 
> > > > > Quoting SGreen@stripped:
> > > > > 
> > > > > > Assuming that your text data is in the column `userinput`
> and
> > you
> > > > want
> > > > > > the 
> > > > > > integer values to be in the column `numericvalue`, this
> > statement
> > > > will 
> > > > 
> > > > > > populate the `numericvalue` column all at once:
> > > > > > 
> > > > > > UPDATE `odd_data_table` SET `numericvalue` = `userinput` +
> 0;
> > > > > > 
> > > > > > You are better off checking for type-correctness before you
> > enter
> > > > data 
> > > > 
> > > > > > into the database than you are trying to correct it after
> the 
> > input.
> > > > 
> > > > > > However, I have had to do just this kind of conversion on
> many 

> > > > occasions
> > > > > > 
> > > > > > (old data, bad batch inputs, text file bulk loads, etc.)  so
> I 

> > know 
> > > > > > techniques like this still have their place.
> > > > > > 
> > > > > > Shawn Green
> > > > > > Database Administrator
> > > > > > Unimin Corporation - Spruce Pine
> > > > > > 
> > > > > > Test USER <testuser@stripped> wrote on 12/09/2005
> 03:30:17 PM:
> > > > > > 
> > > > > > > in an application i have written there is the need to
> do a 
> > search 
> > > > from
> > > > > > 
> > > > > > mysql 
> > > > > > > using numbers that are stored in a varchar column. it
> is not 

> > > > > > > possible to store 
> > > > > > > only the results with numbers in a seperate column.
> > > > > > > so i was looking at CAST(), is this a big performance
> loss? 
is
> > > > > > theresome 
> > > > > > way 
> > > > > > > of benchmarking different queries easy?
> > > > > > > 
> > > > 
> > > 
> > > 
> > > 
> > > 
> > > 
> > > -------------------------------------------------
> > > FREE E-MAIL IN 1 MINUTE!
> > >  - your.name@stripped - http://www.pc.nu
> > 
> 
> 
> 
> 
> 
> -------------------------------------------------
> FREE E-MAIL IN 1 MINUTE!
>  - your.name@stripped - http://www.pc.nu

--=_alternative 000D3711852570D3_=--



-------------------------------------------------
FREE E-MAIL IN 1 MINUTE!
 - your.name@stripped - http://www.pc.nu
Thread
CAST as SIGNED, bad idéa?Test USER9 Dec
  • Re: CAST as SIGNED, bad idéa?SGreen9 Dec
    • Re: CAST as SIGNED, bad idéa?Test USER9 Dec
      • Re: CAST as SIGNED, bad idéa?SGreen9 Dec
        • Re: CAST as SIGNED, bad idéa?Test USER9 Dec
          • Re: CAST as SIGNED, bad idéa?SGreen9 Dec
            • Re: CAST as SIGNED, bad idéa?Test USER9 Dec
              • Re: CAST as SIGNED, bad idéa?SGreen10 Dec
            • Re: CAST as SIGNED, bad idéa?Test USER19 Dec
              • Re: CAST as SIGNED, bad idéa?SGreen19 Dec
                • Re: CAST as SIGNED, bad idéa?Test USER19 Dec
                  • Re: CAST as SIGNED, bad idéa?SGreen19 Dec
          • Re: CAST as SIGNED, bad idéa?Michael Stassen9 Dec
            • Re: CAST as SIGNED, bad idéa?Test USER9 Dec