Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Thanks ALOT! So you would say that this is good database design and that this method and query is the best way to store and retrive product specifications? Quoting [EMAIL PROTECTED]: > Is this good enough? > > SELECT prodid, count(1) matches > FROM PRODUCT_SPECS > WHERE (where SPEC_ID=1 and VALUE=YES) > OR(and SPEC_ID=5 and VALUE>=28) > GROUP BY prodid > HAVING matches=2; > > This query form is flexible enough so that if, for instance, you just > wanted to rank products based on how well they match a set of criteria, > you could leave out the HAVING clause and replace it with ORDER BY > matches > DESC. > > If you only cared about matching a 3 of 4 query terms, your WHERE clause > > would list all 4 terms but your HAVING clause would only check for 3 of > them. > > Normally, these results would be cached into a temporary table and > re-used > in other places (for speed) but it is possible to join in several other > tables if you need them and still get decent results from a single > statement. > > IMHO, Not only is this database design a flexible storage system but the > > ability to easily poll for partial matches and easily determine gross > matching rankings makes it useful for many applications. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > Test USER <[EMAIL PROTECTED]> wrote on 12/19/2005 08:28:36 AM: > > > 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]: > > > > > > > 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 hel
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]: > > > 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 <[EMAIL PROTECTED]> 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 [
IN(INT VS CHAR)
When using IN should i design the database to use int's or is the performance
equal?
WHERE col IN('test','test2','test3')
vs
WHERE col IN(1,2,3)
-
FREE E-MAIL IN 1 MINUTE!
- [EMAIL PROTECTED] - http://www.pc.nu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Yes the ordering is the only problem i have seen so far but i´m concerned that the >= ? might cause problems to? It seems to work but since it orders the results wrong can i be sure that it will always do this correct for me? In the ordering it seems like mysql thinks that 80Gb is larger than 120 and 250. And my concerne is that i might be situations where it thinks that 80 should be returned when doing a >=120. Quoting Michael Stassen <[EMAIL PROTECTED]>: > Test USER wrote: > > 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 supports > DiVX 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 > > some 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 :) > > Is the ordering your only concern? Your value column is a string, so > your > results are ordered alphabetically rather than numerically. If all you > want is > numeric ordering, you need to tell mysql to treat value as a number in > the order by: > >SELECT value FROM tbl WHERE value >=80 ORDER BY value+0 DESC; > > Michael > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
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 1 28 4 4 80 5 4 120 - Thanks again for your help! Quoting [EMAIL PROTECTED]: > 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]: > > > > > 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]: > > > > > > > > > Assuming that your text data is in the column `userinput` and > you > > > want > > > > > the > > > > > integer values to be in the column `numericvalue`, this > statement > > &g
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
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 supports DiVX 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 [EMAIL PROTECTED]: > 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]: > > > > > 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 <[EMAIL PROTECTED]> 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! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
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 [EMAIL PROTECTED]: > 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 <[EMAIL PROTECTED]> 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! > > - [EMAIL PROTECTED] - http://www.pc.nu > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CAST as SIGNE=?ISO-8859-1?Q?D,_bad_id=E9a??=
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 there some way of benchmarking different queries easy? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump dumping a table but not all columns?
i´m using mysqldump to dump some tables and then load it into another mysql server with mysql command. but can i specify what columns in the tables to dump? i´m using something like this now mysqldump -h localhost -u root db tbl | mysql -h xxx.xxx.xxx -u login -pass -w db - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE (url)
Hi thanks for your answer! I would like this to be done via cron 3-4 times a day. Quoting Peter J Milanese <[EMAIL PROTECTED]>: > I have never seen this. Mysql would have to do a wget of the file then > dump it. Last I knew it wasn't a web browser. There may be a way to do > the wget inline though, or at least write something in shell or perl to > do it. Is this cron'd or something, or a one time thing? > > > > - > Sent from my NYPL BlackBerry Handheld. > > > - Original Message - > From: Test USER [EMAIL PROTECTED] > Sent: 12/01/2005 04:55 AM > To: > Subject: LOAD DATA INFILE (url) > > Can't get this to work, but i would like to specify LOAD DATA to use an > INFILE > from an URL. > > For example > LOAD DATA LOCAL INFILE 'http://www.testserver.com/data.csv' > But i get an error message saying file not found. > > Anyone know if this is even possible ? > > - > FREE E-MAIL IN 1 MINUTE! > - [EMAIL PROTECTED] - http://www.pc.nu > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE (url)
Can't get this to work, but i would like to specify LOAD DATA to use an INFILE from an URL. For example LOAD DATA LOCAL INFILE 'http://www.testserver.com/data.csv' But i get an error message saying file not found. Anyone know if this is even possible ? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible ways to sort numbers stored in a varchar column?
Thanks i also found the function CAST which works ok. So now i have three options: CAST LPAD Adding zero Any other sugestions are welcome! Quoting [EMAIL PROTECTED]: > Test USER <[EMAIL PROTECTED]> wrote on 11/23/2005 07:36:43 PM: > > > Is the only way to sort numbers stored in a varchar column to use > lpad? > > Are there any other columntypes that allow both characters and > > numbers that can > > sort numbers correct? > > > > - > > FREE E-MAIL IN 1 MINUTE! > > - [EMAIL PROTECTED] - http://www.pc.nu > > > > You can try adding zero to the column. It's a function acting on a value > > so any chance of using an index is eliminated but you can get your > columns > as numbers that way. To answer your second question, no. Fields are > either > strings or numbers but never both. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible ways to sort numbers stored in a varchar column?
Is the only way to sort numbers stored in a varchar column to use lpad? Are there any other columntypes that allow both characters and numbers that can sort numbers correct? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
