Re: Need for distinct sum
Many thanks for your reply Shawn - I have some comments below.
Hello,
I need to be able to sum over distinct values but I can't seem to do it
unless I use sub-selects (which I want to avoid doing).
To see what I mean, I've constructed a toy DB:
DROP TABLE IF EXISTS spell;
CREATE TABLE spell (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
power FLOAT,
time_casted DATETIME
);
DROP TABLE IF EXISTS wizard;
CREATE TABLE wizard (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
age INT UNSIGNED,
name VARCHAR(255)
);
DROP TABLE IF EXISTS spellcast;
CREATE TABLE spellcast (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
type ENUM ('fire', 'air', 'water', 'earth'),
spell_id INT UNSIGNED,
wizard_id INT UNSIGNED
);
INSERT INTO wizard (name, age) VALUES
('alan', 20),
('bill', 23),
('chris', 21);
INSERT INTO spell (power, time_casted) VALUES
(400, '2006-02-02 12:00'),
(432, '2006-02-04 12:00'),
(123, '2006-02-03 12:00'),
(543, '2006-02-08 12:00'),
(320, '2006-02-01 12:00'),
(102, '2006-02-12 12:00'),
(732, '2006-02-14 12:00'),
(948, '2006-02-18 12:00'),
(932, '2006-02-21 12:00'),
(842, '2006-02-26 12:00');
INSERT INTO spellcast (type, spell_id, wizard_id) VALUES
('fire', 1, 1),
('air', 1, 1),
('water', 1, 1),
('earth', 2, 1),
('water', 2, 1),
('fire', 3, 1),
('water', 3, 1),
('water', 4, 1),
('fire', 4, 1),
('air', 5, 1),
('fire', 6, 1),
('water', 7, 1),
('water', 1, 2),
('fire', 1, 2),
('air', 2, 2),
('earth', 3, 2),
('water', 3, 2),
('earth', 4, 2),
('fire', 4, 2),
('air', 4, 2),
('water', 1, 3),
('earth', 1, 3),
('air', 1, 3),
('water', 5, 3),
('fire', 5, 3),
('earth', 5, 3),
('water', 6, 3),
('air', 7, 3);
A spell is an individual spell that's been cast. A spellcast is the
action of casting the spell by a particular wizard (or a group of
wizards). When casting a spell, a wizard can contribute various
essenses
(fire, earth, air, water). So for example, Alan cast a spell (id=1) and
contributed three essences (fire, air water) - this means that there
are 3 spellcast rows for this contribution to this spell.
Let's say I want to find the total power of all the spells cast by each
wizard that involve fire air. At first I thought the following
might work:
SELECT wizard.name,
SUM(spell.power)
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
GROUP BY wizard.id;
But this is wrong. The above query will count some spells more than
once, so the resulting sum is greater than it should be.
The only way I can think of doing this correctly is to use sub-queries:
SELECT DISTINCT wizard.name,
sub.s
FROM
(SELECT SUM(inner_sub.power) AS s,
inner_sub.wiz_id
FROM
(SELECT DISTINCT
spell.id,
spell.power,
wizard.id AS wiz_id
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
) AS inner_sub
GROUP BY inner_sub.wiz_id
) AS sub,
wizard,
spellcast
WHERE wizard.id = sub.wiz_id AND
spellcast.wizard_id = wizard.id AND
spellcast.type IN ('fire', 'air');
This works but I was wondering whether there was a simpler way to do
it.
All my queries are generated dynamically, and I want to avoid
generating
complex subqueries.
Anyone know of a way to do the above a lot more simply? I can change
the
DB schema if needs be.
Thanks,
Yasir
Based on database theory, your schema is correct (so long as each
spell only consumes up to 1 unit of essence). As you have discovered,
you are trying to take a second-level summary within a single
statement. While the COUNT() aggregate function has a DISTINCT
modifier none of the others do. That is why you posted.
One technique you could try is to create an intermediate pivot table.
That way you can know how much of each essence has been used in each
spell.
CREATE TEMPORARY TABLE tmpSpellSummary (PRIMARY KEY
(wizard_id,spell_id)) SELECT
wizard_id,
spell_id,
SUM(if(type='AIR',1,0)) air,
SUM(if(type='EARTH',1,0)) earth,
SUM(if(type='FIRE',1,0)) fire,
SUM(if(type='WATER',1,0)) water
FROM spellcast
GROUP BY wizard_id, spell_id
Now you can join this pivot table to your other query and you won't
have the duplication. You can also modify this by adding (after the
GROUP BY clause):
HAVING air0 and water0
to pick out just those spell_id's that used both air and water.
MySQL won't let me use HAVING here (because HAVING only works on columns
that are in the SELECT list) so this is what I did:
SELECT wizard.name,
SUM(spell.power)
FROM
~How to load a mysql data file into sybase~
Hello, I have a mysql data bcp file ( using select into outfile ) from a table in mysql database. I have a similar table existing in a sybase database. I need to bcp in the mysql data file into sybase. Can anyone help me on how to go about the same ? Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Thank you MySQL Cluster
Atle Veka wrote: Thank you MySQL Cluster, for ignoring my information request after submitting about a year and a half ago. I was told in email that I was supposed to be contacted, that never happened. Thank you for automatically signing me up for all sorts of MySQL mailinglists for seminars that I do not want to attend. Er i think your email is adressed wrong _ or did they automatically gather emails from the main MySQL mailinglist? Sorry, did you just ask that question and called yourself Unix Administrator ? And, no thanks, I'm no longer interested in MySQL Cluster. :) http://lists.mysql.com/ There you can unsubscribe from any list you don't want to be in anymore. Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very long query for such a simple result
That's not really what I meant. I know what the function SUM() does. But Sum()
takes an expression and '1' doesn't seem like much of an expression to me. So
what is that 1 equates to and where in the MySQL documentation can I find this
explained.
Thanks again.
Peter Brawley [EMAIL PROTECTED] 4/5/06 10:28 AM
Hi Ed,
Count(1) works just as well. Sum(1) just adds 1 for each row so it's logically
equivalent.
PB
-
Ed Reed wrote: WOW!!! THAT WAS AWESOME!!! Thanks a lot Peter. Ok, so what is
SUM(1)? How is it able to do this? And where can I learn more about it? Thanks
again. Peter Brawley [EMAIL PROTECTED] 4/4/06 10:13:00 PM Ed,e:
Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that
I have to make work together and the problemreport table does not reference the
employeeid in the employees table. It was all create about ten years ago and
the data has just always been migrated to the db du jour. I'm currently using
MySQL 4.1x but most importantly I must be able to run the entire query in a
single call. Thanks for the help. OK, that's doable in a subquery, and you can
get the total from SUM(1), so something like ...SELECT IF( SUM(1) = 0,
'', CONCAT( 'You have ', SUM(1), ' Problem
Report', IF(SUM(1) = 1,'','s'), ': Priorities(High=',
SUM(IF(Priority='High',1,0)), ',Med=',
SUM(IF(Priority='Med',1,0)), ',Low=',
SUM(IF(Priority='Low' ,1,0)), ')'))FROM ( SELECT
Priority FROM ProblemReports, Employees WHERE ProblemReports.Status='Open'
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, '
',Employees.LastName) AND Employees.DateTerminated IS NULL AND
Employees.UserName='User1') AS priorities;PB- Peter Brawley [EMAIL
PROTECTED] 4/4/06 2:35:49 PM Ed,The big slowdown in your query is
likely the join on ProblemReports.Responsible = CONCAT(Employees.FirstName, '
',Employees.LastName)) Eek. Does the employees table not have a primary key,
and does the problemreports table not reference that key? If not, I would
think a first priority would be to fix that.Meanwhile, you can simplify your
monster query by writing the user's problem priorities to a temp table, then
doing a simple pivot on priority to generate your sentence. Something like
...DROP TEMPORARY TABLE IF EXISTS ProbSum;CREATE TEMPORARY TABLE ProbSumSELECT
Priority FROM ProblemReports, Employees WHERE ProblemReports.Status='Open' AND
ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName)
AND Employees.DateTerminated IS NULL AND Employees.UserName='User1'SELECT
COUNT(*) INTO @n FROM ProbSum;SELECT IF( @n = 0, '', CONCAT( 'You
have', @n, 'Problem Report',
IF(@n=1,'','s'), ': Priorities(High=',
SUM(IF(Priority='High',1,0)), ',Med=',
SUM(IF(Priority='Med',1,0)), ',Low=',
SUM(IF(Priority='Low' ,1,0)), ')'))FROM
probsum;DROP TEMPORARY TABLE probsum;All this would be easier in a stored
procedure, if you have MySql 5.PB -Ed Reed wrote: Can someone help me
simplify this query please? It's meant to return a single string result that
looks something like this, You have 12 open Problem Reports:
Priorities(High=5, Med=6, Low=1) The relavent columns from the two tables are
Table: ProblemReportsFields: PRNo, Status, Priority, Responsible Table:
EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's the query
Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem
Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem
Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From
ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='High')),', ',(Select
ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE
(ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, '
',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND
(Employees.UserName='User1') And (Priority='Med')),', ',(Select
ConCat('Low=',Count(Priority)) From ProblemReports, Employees WHERE
Many Attributes Required Design Query
Hi All, I have the job of creating a DB for an online clothing company. I have some experience with relational design and sql and php. I'm stumped as they have products that contain numerous attributes, and are wishing to get away, if possible, with entering separate records for each and every product that has different ones. I will list below some example products and their attributes. Shirts: neck body_length shoulder_length chest waist sleeve cuff color Pants: waist under_crotch_length upper_crotch_length upper_crotch_back_length upper_leg_length lower_leg_length Jacket: body_length shoulder_length chest waist sleeve Belts: body_length body_width waist_length buckle_length buckle_length holes sleeve_length below is a link to what they have on Ebay at the moment, http://cgi.ebay.com/DOLCE-GABBANA-PEACE-Jeans-48_W0QQitemZ7757125046QQcategoryZ11483QQtcZphotoQQcmdZViewItem keep in mind, I have only listed attributes above, not the actual table designs. They deal with a lot of variety within brands etc. Anyone got any solutions for this? I haven't started on a basic design yet, but am looking to do that soon. I f i can minimize their record input, that'd be great Cheers. Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Attributes Required Design Query
Mark Sargent wrote: Hi All, I have the job of creating a DB for an online clothing company. I have some experience with relational design and sql and php. I'm stumped as they have products that contain numerous attributes, and are wishing to get away, if possible, with entering separate records for each and every product that has different ones. I will list below some example products and their attributes. Shirts: neck body_length shoulder_length chest waist sleeve cuff color Pants: waist under_crotch_length upper_crotch_length upper_crotch_back_length upper_leg_length lower_leg_length Jacket: body_length shoulder_length chest waist sleeve Belts: body_length body_width waist_length buckle_length buckle_length holes sleeve_length below is a link to what they have on Ebay at the moment, http://cgi.ebay.com/DOLCE-GABBANA-PEACE-Jeans-48_W0QQitemZ7757125046QQcategoryZ11483QQtcZphotoQQcmdZViewItem keep in mind, I have only listed attributes above, not the actual table designs. They deal with a lot of variety within brands etc. Anyone got any solutions for this? I haven't started on a basic design yet, but am looking to do that soon. I f i can minimize their record input, that'd be great Cheers. Mark. Uhm. My solution would be 3 Databases where one has ID,Attrib_object_id, Attrib_name_id, Attrib_value And the other two would be an attrib database and an object databse. Yep, something like that. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Attributes Required Design Query
Hello Mark, What exactly is your question? How to design the tables for the different types of clothing? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Hi All, I have the job of creating a DB for an online clothing company. I have some experience with relational design and sql and php. I'm stumped as they have products that contain numerous attributes, and are wishing to get away, if possible, with entering separate records for each and every product that has different ones. I will list below some example products and their attributes. Shirts: neck body_length shoulder_length chest waist sleeve cuff color Pants: waist under_crotch_length upper_crotch_length upper_crotch_back_length upper_leg_length lower_leg_length Jacket: body_length shoulder_length chest waist sleeve Belts: body_length body_width waist_length buckle_length buckle_length holes sleeve_length below is a link to what they have on Ebay at the moment, http://cgi.ebay.com/DOLCE-GABBANA-PEACE-Jeans-48_W0QQitemZ7757125046QQcategoryZ11483QQtcZphotoQQcmdZViewItem keep in mind, I have only listed attributes above, not the actual table designs. They deal with a lot of variety within brands etc. Anyone got any solutions for this? I haven't started on a basic design yet, but am looking to do that soon. I f i can minimize their record input, that'd be great Cheers. Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very long query for such a simple result
Ed Reed wrote: That's not really what I meant. I know what the function SUM() does. But Sum() takes an expression and '1' doesn't seem like much of an expression to me. So what is that 1 equates to and where in the MySQL documentation can I find this explained. Thanks again. It doesn't care about if the expression is a DB-field with a numeric value or you insert a numeric value yourself. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need to select correct package
Hi Comunity, I have some probleme for selecting correct package of mysql software from download section in the site. I am confused which Linux version i have to use, as there are different packages. please help me.
select all events from (today-N) days
Hi: I have a date/time field (utime) which has unix time in epoch time. I want to select events from yesterday and another statement for all events from previous week. I have tried the functions listed at http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html. But I either get a syntax error or just the wrong info. select * from TABLE where utime endtime and utime starttime how do I get the starttime and endtime for yesterday and also the last week(Sunday to Saturday)? Thanks Ravi __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need to select correct package
Post output of the commands uname -a and /lib/libc.so.6|head -1 What do you plan on doing with MySQL? Clustering or rather normal DB usage? Then we can tell you - or actually you should at least then be able to decide yourself ;) balaraju mandala schrieb: Hi Comunity, I have some probleme for selecting correct package of mysql software from download section in the site. I am confused which Linux version i have to use, as there are different packages. please help me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select all events from (today-N) days
Ok. I found something. But wondering if this is most efficient Events for yesterday: select count(*) from EVENT_DATA where FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() - INTERVAL 1 DAY); Events for last week select count(*) from EVENT_DATA where FROM_UNIXTIME(utime,'%U') = (DATE_FORMAT(CURDATE(),'%v')-1); TIA Ravi --- Ravi Malghan [EMAIL PROTECTED] wrote: Hi: I have a date/time field (utime) which has unix time in epoch time. I want to select events from yesterday and another statement for all events from previous week. I have tried the functions listed at http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html. But I either get a syntax error or just the wrong info. select * from TABLE where utime endtime and utime starttime how do I get the starttime and endtime for yesterday and also the last week(Sunday to Saturday)? Thanks Ravi __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very long query for such a simple result
Ed,
That's not really what I meant. I know what the function SUM() does. But Sum() takes an _expression_ and '1' doesn't seem like much of an _expression_ to me. So what is that 1 equates to and where in the MySQL documentation can I find this explained.
The _expression_ here is '1', which it sums rowwise.
PB
-
Thanks again.
Peter Brawley [EMAIL PROTECTED] 4/5/06 10:28 AM
Hi Ed,
Count(1) works just as well. Sum(1) just adds 1 for each row so it's logically equivalent.
PB
-
Ed Reed wrote: WOW!!! THAT WAS AWESOME!!! Thanks a lot Peter. Ok, so what is SUM(1)? How is it able to do this? And where can I learn more about it? Thanks again. Peter Brawley [EMAIL PROTECTED] 4/4/06 10:13:00 PM Ed,e: Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that I have to make work together and the problemreport table does not reference the employeeid in the employees table. It was all create about ten years ago and the data has just always been migrated to the db du jour. I'm currently using MySQL 4.1x but most importantly I must be able to run the entire query in a single call. Thanks for the help. OK, that's doable in a subquery, and you can get the total from SUM(1), so something like ...SELECT IF( SUM(1) = 0, '', CONCAT( 'You have ', SUM(1), ' Problem Report', IF(SUM(1
) = 1,'','s'), ': Priorities(High=', SUM(IF(Priority='High',1,0)), ',Med=', SUM(IF(Priority='Med',1,0)), ',Low=', SUM(IF(Priority='Low' ,1,0)), ')'))FROM ( SELECT Priority FROM ProblemReports, Employees WHERE ProblemReports.Status='Open' AND ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName) AND Employees.DateTerminated IS NULL AND Employees.UserName='User1') AS priorities;PB- Peter Brawley [EMAIL PROTECTED] 4/4/06 2:35:49 PM Ed,The big slowdown in your query is likely the join on ProblemReports.Responsible = CONCAT(Employees.FirstName, ' ',Employees.LastName)) Eek. Does the employees table not have a primary key, and does the problemreports table not reference that key? If not, I would think a first priority would
be to fix that.Meanwhile, you can simplify your monster query by writing the user's problem priorities to a temp table, then doing a simple pivot on priority to generate your sentence. Something like ...DROP TEMPORARY TABLE IF EXISTS ProbSum;CREATE TEMPORARY TABLE ProbSumSELECT Priority FROM ProblemReports, Employees WHERE ProblemReports.Status='Open' AND ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName) AND Employees.DateTerminated IS NULL AND Employees.UserName='User1'SELECT COUNT(*) INTO @n FROM ProbSum;SELECT IF( @n = 0, '', CONCAT( 'You have', @n, 'Problem Report', IF(@n=1,'','s'), ': Priorities(High=', SUM(IF(Priority='High',1,0)), ',Med=', SUM(IF(Priority='Med',1,0)), ',Low=', SUM(IF(Priority='Low' ,1,0)), ')'))FROM probsum;DROP TEMPORARY TABLE probsum;All this would be easier in a s
tored procedure, if you have MySql 5.PB -Ed Reed wrote: Can someone help me simplify this query please? It's meant to return a single string result that looks something like this, "You have 12 open Problem Reports: Priorities(High=5, Med=6, Low=1)" The relavent columns from the two tables are Table: ProblemReportsFields: PRNo, Status, Priority, Responsible Table: EmployeesFields: Firstname, Lastname, DateTerminated, UserName Here's the query Select IF((@a:=(SELECT Count(PRNo) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1')))0, If (@a=1, ConCat('You have one open Problem Report: Prioritiy = ', (Select Priority From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated I
s Null) AND (Employees.UserName='User1'))), Concat('You have ', @a, ' open Problem Reports: Priorities(', (Select ConCat('High=',Count(Priority)) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='High')),', ',(Select ConCat('Med=',Count(Priority)) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND (ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND (Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And (Priority='Med')),',
Re: need to select correct package
please read what i wrote and do so - I cannot help you without this info as I do not know any Linux Enter prise ver4 please post your replies to the mysql mailing list, not to my email-address balaraju mandala schrieb: Hi DK, I just want use MySql for personal use. But i am confused which Linux package i have to download as there are different packages. I have a system(Pentium4-HT) loaded with Linux Enter prise ver4 or i can load Linux Enter prise ver3. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need to select correct package
Hi DK, The user -a output is : Linux wizon.secureserver.net 2.6.9-11.EL #1 Fri May 20 18:17:57 EDT 2005 i686 i6 I didn't understand what is secound command is. On 4/6/06, Dominik Klein [EMAIL PROTECTED] wrote: please read what i wrote and do so - I cannot help you without this info as I do not know any Linux Enter prise ver4 please post your replies to the mysql mailing list, not to my email-address balaraju mandala schrieb: Hi DK, I just want use MySql for personal use. But i am confused which Linux package i have to download as there are different packages. I have a system(Pentium4-HT) loaded with Linux Enter prise ver4 or i can load Linux Enter prise ver3.
Re: need to select correct package
balaraju mandala wrote: Hi DK, The user -a output is : Linux wizon.secureserver.net 2.6.9-11.EL #1 Fri May 20 18:17:57 EDT 2005 i686 i6 I didn't understand what is secound command is. i686 i6 Thats the needed infortmation. get your package here: Red Hat Enterprise Linux 4 RPM (x86) downloads http://dev.mysql.com/downloads/mysql/5.0.html Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need to select correct package
The user -a output is : uname -a, but anyway ... Linux wizon.secureserver.net 2.6.9-11.EL #1 Fri May 20 18:17:57 EDT 2005 i686 i6 I didn't understand what is secound command is. You should have just pasted it into your command line and paste back the output into your mail. i686 i6 Thats the needed infortmation. Not necessarily. On some systems you can still find glibc V2.2, thats why I added the second command earlier. MySQL which was built with glibc2.3 will not work on such systems. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need to select correct package
Thank You, Thank you very much for your help. Sorry for mailing personally,
Re: Many Attributes Required Design Query
Barry [EMAIL PROTECTED] wrote on 04/06/2006 03:52:53 AM: Mark Sargent wrote: Hi All, I have the job of creating a DB for an online clothing company. I have some experience with relational design and sql and php. I'm stumped as they have products that contain numerous attributes, and are wishing to get away, if possible, with entering separate records for each and every product that has different ones. I will list below some example products and their attributes. Shirts: neck body_length shoulder_length chest waist sleeve cuff color Pants: waist under_crotch_length upper_crotch_length upper_crotch_back_length upper_leg_length lower_leg_length Jacket: body_length shoulder_length chest waist sleeve Belts: body_length body_width waist_length buckle_length buckle_length holes sleeve_length below is a link to what they have on Ebay at the moment, http://cgi.ebay.com/DOLCE-GABBANA-PEACE- Jeans-48_W0QQitemZ7757125046QQcategoryZ11483QQtcZphotoQQcmdZViewItem keep in mind, I have only listed attributes above, not the actual table designs. They deal with a lot of variety within brands etc. Anyone got any solutions for this? I haven't started on a basic design yet, but am looking to do that soon. I f i can minimize their record input, that'd be great Cheers. Mark. Uhm. My solution would be 3 Databases where one has ID,Attrib_object_id, Attrib_name_id, Attrib_value And the other two would be an attrib database and an object databse. Yep, something like that. I think you meant to type tables not databases - :-0 But we knew what you meant... ;-) -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) I agree with the basic design: one table for all of your basic objects (shirts, pants, coats, shoes, etc), one table for all of your attributes (see Barry's response), a sku table equating objects (differentiated by their attributes) and their inventory quantities (on hand, backordered, etc), and one more to relate SKU to all applicable attributes. Each SKU represents one combination of a base object with a particular set of attributes. IT's the SKU number that important for inventory control and that will uniquely identify a size 8 pair of jeans from a size 9 pair or a pair of black size 8s from a pair of red size 8s all in the same style (cut) from the same manufacturer It's a time-tested inventory control model used by all but the smallest of retailers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Key and Primary Key
IF I have a table like this: id int not null, field2 int not null, .., primary key (id), key (field2) ) ENGINE=MyISAM; The primary key is id only or (id, field2)? If this is the case which constraint are aplied on field2? -- Three words describe our society:homo homini lupus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need to select correct package
Dominik Klein wrote: The user -a output is : uname -a, but anyway ... Linux wizon.secureserver.net 2.6.9-11.EL #1 Fri May 20 18:17:57 EDT 2005 i686 i6 I didn't understand what is secound command is. You should have just pasted it into your command line and paste back the output into your mail. i686 i6 Thats the needed infortmation. Not necessarily. On some systems you can still find glibc V2.2, thats why I added the second command earlier. MySQL which was built with glibc2.3 will not work on such systems. Aaah my apologies. That's true :) But i think that package will work probably. Well he sees that it don't works after he installed it and come back telling it don't do ;P barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Key and Primary Key
news [EMAIL PROTECTED] wrote on 06/04/2006 14:39:33: IF I have a table like this: id int not null, field2 int not null, .., primary key (id), key (field2) ) ENGINE=MyISAM; The primary key is id only or (id, field2)? If this is the case which constraint are aplied on field2? You have defined two separate keys, one on ID and one on field2. The id field, being primary, must be without nulls and each entry must be unique. The key on field2, not being a primary key, may contain duplicates and nulls. Are you searching for the synax ... primary key keyname (id, field2) ... ? This creates a single key in which neither of the fields may be null and the combination of the two fields (but not the two fields separately) must be unique. The effects on the two formulations both on constraints and on search performance are different. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Key and Primary Key
news [EMAIL PROTECTED] wrote on 04/06/2006 09:39:33 AM: IF I have a table like this: id int not null, field2 int not null, .., primary key (id), key (field2) ) ENGINE=MyISAM; The primary key is id only or (id, field2)? If this is the case which constraint are aplied on field2? -- Three words describe our society:homo homini lupus You declared that the primary key is only on the field (ID). There are no other fields involved. You also declared that the engine should create and maintain a separate index on the field (field2). There are no contstraints built into a regular index. Does that help? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Return virtual records
Can anyone explain how I might be able to return a numbers of records based on the sum of a quantity in a field in the same table? (After I read that it sounds even confusing to me). Let me explain. I have records like this, Part#Qty 1254 5 1414 2 147581 1254 6 1024 3 1254 1 Now if I did a query like this Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254 I would expect my results to look like this Part# Sum(Qty) 1254 12 But what I really want is this Part# 1254 1254 1254 1254 1254 12541254 1254 12541254 1254 1254 So 12 virtual records for the count of the records returned from the Sum() Can someone help me with this? - Thanks
Re: Many Attributes Required Design Query
[EMAIL PROTECTED] wrote: I agree with the basic design: one table for all of your basic objects (shirts, pants, coats, shoes, etc), one table for all of your attributes (see Barry's response), a sku table equating objects (differentiated by their attributes) and their inventory quantities (on hand, backordered, etc), and one more to relate SKU to all applicable attributes. Hi All, Shawn, what is a SKU? Each SKU represents one combination of a base object with a particular set of attributes. IT's the SKU number that important for inventory control and that will uniquely identify a size 8 pair of jeans from a size 9 pair or a pair of black size 8s from a pair of red size 8s all in the same style (cut) from the same manufacturer Makers: maker_id maker_desc Products: prod_id prod_code maker_id prod_desc attri_id object_id Attributes: attri_id attri_desc Objects: object_id object_desc That is where I got to, as I've never done this kinda design before. Thrown into the deep end, I guess. May I ask for more assistance with this? Where does the quantity go? Any tutorials on this kind of design? Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
VPN Recommendation
Hello, What is the recommended speed for my VPN Server and Client (OpenVPN)? The VPN Server is also the Database Server using MySQL (win32). The VPN Client contains the application (using MyODBC) connecting to the VPN Server and acessing the Server. My speed VPN Server (512Kbps) and Client (256Kbps) is kinda slow particularly the queries even when I used stored procedures and functions. So I would like to know some of your suggestions on my setup. Thanks, Michael Louie Loria LoRz Technology Solutions htttp://www.lorztech.com signature.asc Description: OpenPGP digital signature
MySQL 4.1.11 innodb cache can't be flushed after restart ?
Hi all, I am running MySQL 4.1.11 with an innoDB table holding about 17GB of records. I took a few hundreds of randomly selected records from the table and measured the average access time: 1st test: average access time is 600ms 2nd test: average access time is 30ms 3rd test: average access time is 15ms Stop and restart MySQL 4th test: average access time is 15ms Note that I stopped and restarted mysql between the 3rd and 4th test but the average access time does not change. I also tried another set of random records that are not in the table, the average access time is about 2s for the first test and 115ms for the second test. After stop and restart MySQL, I still got the 115ms access time. Clearly MySQL have both positive and negtive caching. But does anyone know why the cache is not flushed after MySQL restart?? I understand that MySQL has a query_cache, but it is turned off by default and I do NOT have it on. There is also an innodb_buffer_pool_size variable, which in my case is at the default value 1048576 (and can't seem to be set smaller). My data file path in my.cnf file is: # Configure the datafile to be auto expanding innodb_data_file_path = ibdata1:10M:autoextend Thanks a lot! Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Determining if a trigger exists
Hi, Whats the simplest way to determine if a trigger already exists? i.e. For tables you have: CREATE TABLE IF NOT EXISTS ... Is there a way to do CREATE TRIGGER IF NOT EXISTS I'm using MySQL 5.0 btw. Thanks! Regards, Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if a trigger exists
There is a TRIGGERS table in the information_schema Eg. select Trigger_Name from TRIGGERS where trigger_name = 'TI_AGENT' AND trigger_schema = 'PROPLINK' -Original Message- From: Adrian Co [mailto:[EMAIL PROTECTED] Sent: Friday, 7 April 2006 1:54 PM To: mysql@lists.mysql.com Subject: Determining if a trigger exists Hi, Whats the simplest way to determine if a trigger already exists? i.e. For tables you have: CREATE TABLE IF NOT EXISTS ... Is there a way to do CREATE TRIGGER IF NOT EXISTS I'm using MySQL 5.0 btw. Thanks! Regards, Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Attributes Required Design Query
Hi All, what do you all think about using OSCommerce for this? I have no previous experience with it, but have been looking at it the last 2days. This company has been playing with it, but they were concerned with the many attributes and the requirement of inputting so many records as I originally stated and whether OSCommerce could be tweaked to get around that. Whilst not getting away from the initial topic, your thoughts would be greatly appreciated on using OSCommerce. Pros ad Cons. My initial responsibility is to gauge what is their best direction for this. Cheers. Mark Sargent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How could I know which transaction or thread hold the lock
Hello: How could I know which transaction or thread hold the lock? For example: show innodb status\G ---TRANSACTION 0 78076313, ACTIVE 3906 sec, process no 12729, OS thread id 2952076208 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1024 MySQL thread id 268, query id 40997 localhost root Updating UPDATE CORPSMSINFO SET PERMISSIONS='1000' WHERE CUSTOMERID='100010A' --- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 20032 n bits 192 index `PRIMARY` of table `ea191/CORPSMSINFO` trx id 0 78076313 lock_mode X locks rec but not gap waiting Record lock, heap no 122 PHYSICAL RECORD: n_fields 19; compact format; info bits 0 0: len 11; hex 3130303030303030313041; asc 100010A;; 1: len 6; hex 04a755e1; asc U ;; 2: len 7; hex 00562927be; ascV)' ;; 3: len 3; hex 415350; asc ASP;; 4: len 4; hex 4435c9e1; asc D5 ;; 5: len 5; hex 61646d696e; asc admin;; 6: len 4; hex 4434cb9b; asc D4 ;; 7: len 4; hex 4434cb9b; asc D4 ;; 8: len 1; hex 30; asc 0;; 9: SQL NULL; 10: SQL NULL; 11: SQL NULL; 12: len 4; hex 4434cb9b; asc D4 ;; 13: len 4; hex 4434cb9b; asc D4 ;; 14: len 1; hex 30; asc 0;; 15: len 2; hex 3220; asc 2 ;; 16: SQL NULL; 17: len 30; hex 313030303030303030303030303030303030303030303030303030303030; asc 10;...(truncated); 18: len 4; hex 8000; asc ;; -- How could I find which one hold that lock? Regards, gu lei
Replication for historical data
Hi, I have a customer who wants to be able to replicate their live MySQL database to a second server, but not to have any data deleted. i.e., they want to accumulate the data. I don't believe you can do this with replication. Does anyone know a way of doing this? Cheers, Ian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication for historical data
Hi, I have a customer who wants to be able to replicate their live MySQL database to a second server, but not to have any data deleted. i.e., they want to accumulate the data. I don't believe you can do this with replication. Does anyone know a way of doing this? Cheers, Ian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
