Re: Database Replication Fallover
I forgot to mention that I am running Linux. If anybody has some idea of software which can do this, I'd be very interested. Regards, Ben Ben Clewett wrote: Dear MySql, I'm looking into availability and wonder if any member might be able to help me. I have two databases, one Primary and one full Replication. Normally the primary is used for data input, reports are drawn from the replication. If I loose the Primary, do any members have any software they can recommend which: - Stops the replication daemon. - Sets the replication server to Read/Write. - Shuts down the primary. - Routes traffic to the replication. Any advise or ideas would be very useful... Regards, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The time of last updated field
I have to two different fields Last_Updated and Story. I want whenever Story Field got updated (modified) the time and date of the modification get recorded (insert) in Last_Updated Field - No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started.
Re: The time of last updated field
On Monday 02 April 2007 09:11:18 sam rumaizan wrote: I have to two different fields Last_Updated and Story. I want whenever Story Field got updated (modified) the time and date of the modification get recorded (insert) in Last_Updated Field The answer you seek lies within the manual. http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improving performance of server
sure, i am inserting the data with: LOAD DATA LOCAL INFILE 'lightning-data.txt' INTO TABLE strikes; does that clear things up? i assume that the data is first inserted into the table and then the index is created afterwards. following the creation of the database files i see that first one is filled up, which then remains static while the other one (the index, i assume) is populated. it is the second step that takes such a long time (although the cpu is doing very little and ram is barely occupied, it spends all its time swapping). so, my question really is how to tell the server to use ram instead of swap? i am using the default storage engine. Could you post the actual code you are using for the INSERT? Also, what storage engine are you using? Jay andrew collier wrote: hello, i am having some trouble getting mysql to perform decently on my machine. it is a 2 GHz dual core AMD 64 machine (although i am presently running a 32 bit linux system) with 1 Gb RAM and 1 Gb swap partition. the data i am using is loaded into a table described by: CREATE TABLE IF NOT EXISTS strikes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, epoch DATETIME, usecMEDIUMINT UNSIGNED, fdate DOUBLE, lat FLOAT(6,4), lon FLOAT(7,4), error TINYINT UNSIGNED, nstat TINYINT UNSIGNED ); the data itself is pretty big: 70082053 records. during the loading process mysqlq pretty much hogs the CPU but uses only around 5% of the RAM. it takes 13m50s to load the data. there is a lot of disk activity, but this is just reading the data and there is virtually no swap space in use. adding the following index: INDEX coords (lat,lon) takes a really long time. once again the hard disk is working hard, but there is no swapping, so obviously this is just due to database reads. CPU usage is about the same. in the end after 60 hours i gave up: had to reboot to windows to do some other stuff. but it was just taking unreasonably long anyway. i am pretty sure that a lot more of this processing could be done without that much disk activity and i am guessing that is what is slowing the whole process down. the configuration i have in my.cnf is: [client] port= 3306 socket = /var/run/mysql/mysql.sock [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 32M max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 128K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout please could someone give me an idea of how i might go about making this whole thing a little more efficient? thanks! best regards, andrew collier. -- Click to consolidate debt and lower month expenses http://tags.bluebottle.com/fc/CAaCMPJklgxQ4NVfi4KJjZZEBd8Cw1Pv/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a Week ( date ) query
The first part of the WHERE clause gives you the week number of a record's date, and compare it with today's week number. Note that WEEK(2008-01-01,7) will return 53, indicating that because 2008-01-01 is a Tuesday it is part of the last week of 2007. That, I think, is what you want. The second clause is supposed to make sure that we aren't finding records that are in the same week but in previous years. In other words, if we are in week 23 we don't want to find records that are from week 23 ten years ago. I think I got the arguments to DATEDIFF backwards, though. The query should read SELECT * FROM specials WHERE WEEK(NOW(), 7) = WEEK(specials.start_date, 7) AND DATEDIFF(NOW(),specials.start_date) 7; Let me know if that works. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Joey [mailto:[EMAIL PROTECTED] Sent: Saturday, March 31, 2007 4:18 AM To: 'Jerry Schwartz' Subject: RE: Help With a Week ( date ) query Hi Jerry, This part of the query is working, but something in the AND part isn't and is failing. SELECT * FROM special WHERE WEEK(NOW(), 7) = WEEK(special.date, 7) AND DATEDIFF(specials. date,DATE(NOW()) 7; Thanks for your help! -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 10:29 AM To: 'Joey'; 'MySQL DB' Subject: RE: Help With a Week ( date ) query I'm not sure if this gets you all the way, or not. There is a WEEK() function that converts a date into its week of the year. There isn't any obvious way to turn it back into a date, but it doesn't sound like you need it for your particular application. SELECT * FROM specials WHERE WEEK(NOW(), 7) = WEEK(specials.start_date, 7) AND DATEDIFF(specials.start_date,DATE(NOW)) 7; Since you have the start date for your special, then you will be getting it back from your query anyways so you don't need to convert back. You can make your queries more efficient if you store the week the special starts as well as its date, saving one function call in your query and allowing you to index on that week field. The second part of the WHERE clause should keep you within the right year, if I did it correctly. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Joey [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 9:46 AM To: MySQL DB Subject: Help With a Week ( date ) query Hi Guys, I'm kind of at a standstill in coming up with how to get a query I need to write. I'm trying to find a record which matches the week we are in. Example today is Thursday the 29th, it is within the week which has the 26th through the 1st, and if the day is within this week display the record that has the date 3/26/2007. Basically we are returning a special which is dated each Monday, any day within that week should show the Monday value. I appreciate your help! Joey -- 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: improving performance of server
Hi Andrew, Since the default storage engine is myisam, increase the below parameter and then try your index operation. myisam_sort_buffer_size. This parameter is used for index creation in myisam. Also Set key_buffer to 5 - 50% of your RAM depending on how much you use MyISAM tables, but keep key_buffer_size + InnoDB buffer pool size 80% of your RAM. Please let us know if this helps your activity. regards anandkl On 4/2/07, andrew collier [EMAIL PROTECTED] wrote: sure, i am inserting the data with: LOAD DATA LOCAL INFILE 'lightning-data.txt' INTO TABLE strikes; does that clear things up? i assume that the data is first inserted into the table and then the index is created afterwards. following the creation of the database files i see that first one is filled up, which then remains static while the other one (the index, i assume) is populated. it is the second step that takes such a long time (although the cpu is doing very little and ram is barely occupied, it spends all its time swapping). so, my question really is how to tell the server to use ram instead of swap? i am using the default storage engine. Could you post the actual code you are using for the INSERT? Also, what storage engine are you using? Jay andrew collier wrote: hello, i am having some trouble getting mysql to perform decently on my machine. it is a 2 GHz dual core AMD 64 machine (although i am presently running a 32 bit linux system) with 1 Gb RAM and 1 Gb swap partition. the data i am using is loaded into a table described by: CREATE TABLE IF NOT EXISTS strikes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, epoch DATETIME, usecMEDIUMINT UNSIGNED, fdate DOUBLE, lat FLOAT(6,4), lon FLOAT(7,4), error TINYINT UNSIGNED, nstat TINYINT UNSIGNED ); the data itself is pretty big: 70082053 records. during the loading process mysqlq pretty much hogs the CPU but uses only around 5% of the RAM. it takes 13m50s to load the data. there is a lot of disk activity, but this is just reading the data and there is virtually no swap space in use. adding the following index: INDEX coords (lat,lon) takes a really long time. once again the hard disk is working hard, but there is no swapping, so obviously this is just due to database reads. CPU usage is about the same. in the end after 60 hours i gave up: had to reboot to windows to do some other stuff. but it was just taking unreasonably long anyway. i am pretty sure that a lot more of this processing could be done without that much disk activity and i am guessing that is what is slowing the whole process down. the configuration i have in my.cnf is: [client] port= 3306 socket = /var/run/mysql/mysql.sock [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 32M max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 128K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout please could someone give me an idea of how i might go about making this whole thing a little more efficient? thanks! best regards, andrew collier. -- Click to consolidate debt and lower month expenses http://tags.bluebottle.com/fc/CAaCMPJklgxQ4NVfi4KJjZZEBd8Cw1Pv/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improving performance of server
At 03:23 AM 4/2/2007, you wrote: sure, i am inserting the data with: LOAD DATA LOCAL INFILE 'lightning-data.txt' INTO TABLE strikes; does that clear things up? i assume that the data is first inserted into the table and then the index is created afterwards. Only if the table is empty when you first start the Load Data command. If you have data in the table then the index is maintained as the rows are loaded (much slower). If this is the case then you may want to disable the indexes prior to Load Data and then re-enable the indexes later will rebuild them. following the creation of the database files i see that first one is filled up, which then remains static while the other one (the index, i assume) is populated. it is the second step that takes such a long time (although the cpu is doing very little and ram is barely occupied, it spends all its time swapping). so, my question really is how to tell the server to use ram instead of swap? For MyISAM tables, you need to increase the Key_Buffer_Size to up to 30% of available memory. This is where the keys are built. Increasing this value (get more RAM if you need to), will dramatically speed up the performance of building the index, as much as 100x faster. So if you don't have enough RAM, beg borrow or steal some for a few days and bump up Key_Buffer_Size and you should notice a big difference. i am using the default storage engine. I assume then it is MyISAM. You can of course override it and make InnoDb the default in which case the Key_Buffer_Size won't have any effect. Mike Could you post the actual code you are using for the INSERT? Also, what storage engine are you using? Jay andrew collier wrote: hello, i am having some trouble getting mysql to perform decently on my machine. it is a 2 GHz dual core AMD 64 machine (although i am presently running a 32 bit linux system) with 1 Gb RAM and 1 Gb swap partition. the data i am using is loaded into a table described by: CREATE TABLE IF NOT EXISTS strikes ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, epoch DATETIME, usecMEDIUMINT UNSIGNED, fdate DOUBLE, lat FLOAT(6,4), lon FLOAT(7,4), error TINYINT UNSIGNED, nstat TINYINT UNSIGNED ); the data itself is pretty big: 70082053 records. during the loading process mysqlq pretty much hogs the CPU but uses only around 5% of the RAM. it takes 13m50s to load the data. there is a lot of disk activity, but this is just reading the data and there is virtually no swap space in use. adding the following index: INDEX coords (lat,lon) takes a really long time. once again the hard disk is working hard, but there is no swapping, so obviously this is just due to database reads. CPU usage is about the same. in the end after 60 hours i gave up: had to reboot to windows to do some other stuff. but it was just taking unreasonably long anyway. i am pretty sure that a lot more of this processing could be done without that much disk activity and i am guessing that is what is slowing the whole process down. the configuration i have in my.cnf is: [client] port= 3306 socket = /var/run/mysql/mysql.sock [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 32M max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 128K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout please could someone give me an idea of how i might go about making this whole thing a little more efficient? thanks! best regards, andrew collier. -- Click to consolidate debt and lower month expenses http://tags.bluebottle.com/fc/CAaCMPJklgxQ4NVfi4KJjZZEBd8Cw1Pv/ -- 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: Tricky Sorting
That should be fairly easy. What you are looking to do is have your sort field conditional. The ORDER BY doesn't have to specify a database field, it can be any field in your query. So you could do something like this: SELECT IF( issuemonth IS NULL, issuenum, issuedate) AS sortfield, issuedate, issuenum,... FROM tablename ORDER BY sortfield. I don't know your field names and table structure, so you'll need to change the names. - Original Message - From: Shannon Appelcline [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, March 31, 2007 1:32 AM Subject: Tricky Sorting I'm trying to figure out the best way to do a tricky bit of sorting. I'm pretty sure it's entirely possible with an IFNULL or something, but I always feel like I hit a barrier when I get to a certain level of complexity in my MYSQL. In any case, I have some magazines, each of which has a DATE, a VOLUME, and an ISSUE. The sorting is usually simple, in that the date includes a year and a month and you can sort by that. However, sometimes magazines get delayed and they start putting only a year on their issues, omitting the months. Worse, they sometimes randomly change numbers (or names) Ideally, I'd like things to sort by the date, unless there's not a month, in which case it falls back to the issue number. So for example, this would be a correct sort: 1996-01-01 Original Mag V1 #1 1996-02-01 Original Mag V1 #2 1996-00-00 Original Mag V1 #3 1996-00-00 Original Mag V1 #4 1996-05-01 Original Mag V1 #5 1996-06-01 Replacement Mag V1 #1 ORDER BY date, volume, issue does this: 1996-00-00 Original Mag V1 #3 1996-00-00 Original Mag V1 #4 1996-01-01 Original Mag V1 #1 1996-02-01 Original Mag V1 #2 1996-05-01 Original Mag V1 #5 1996-06-01 Replacement Mag V1 #1 ORDER BY volume, issue does this: 1996-01-01 Original Mag V1 #1 1996-06-01 Replacement Mag V1 #1 1996-02-01 Original Mag V1 #2 1996-00-00 Original Mag V1 #3 1996-00-00 Original Mag V1 #4 1996-05-01 Original Mag V1 #5 None of it's quite ideal. ORDER BY YEAR(date), volume, issue is what I'm using right now because it puts things in the right ballpark. Shannon -- 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]
Thank you for the free ride
As probably you saw in the announcement (http://jcole.us/blog/archives/2007/03/31/mysql-conference-expo-free-ride-winners/), im one of the winners of the free ride to the Mysql Conference. So, i just want to say thanks to Jeremy Cole, Proven Scaling and the whole Mysql team for the opportunity to go and share experience with other people involved on the databases world. I really hope to meet some great guys from Mysql as Mark Matthews, Brian Aker, Jim Starkey, Heikki Tuuri, Stewart Smith and non mysql people like John Newton from Alfresco and the Pentaho's group and Zmanda crew (just to mention a few). See ya at the conference :) Carlos Proal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex sql help
I have a need to output a recordset that shows the record with the higest value for severity within a date range. That is easy enough, but, in the same query, I need to show that data 3 times in the same query. Once where event_avail = 1, then again where event_perf = 1 and finally where even_sec = 1. It can be across any hostid as long as it is in the groupid list as seen below. I am including an example of what looks like it works for ONE of the sets of data (see e.event_avail = 1) but I need to include a severity, hostid, color, severitydesc, fontcolor, eventid and description for the most servere event in that group where event_perf = 1 and again for wehre event_sec = 1... This is grouped by Groupid so I can show the most severe event that has happened for availability, performance and security within that group (across any host in the group). Here is a sample that shows me avail data correctly (I think) select g.name AS name, g.groupid AS groupid, e.severity AS apoint, e.hostid AS ahostid, fs.color AS apointcolor, fs.severitydesc AS apointdesc, fs.fontcolor AS apointfont, e.eventid, e.description FROM groups g, fs_events e, fs_severity fs, hosts_groups hg WHERE e.hostid = hg.hostid and g.groupid = hg.groupid and fs.severityid = e.severity and e.event_avail = 1 and e.time_stamp = date_sub(now(), interval 30 DAY) and e.acknowledged in (0,1) and g.groupid in (2,3,4,5,6) group by groupid order by name,apoint desc Here are the tables involved: CREATE TABLE `groups` ( `groupid` bigint(20) unsigned NOT NULL default '0', `name` varchar(64) NOT NULL default '', PRIMARY KEY (`groupid`), KEY `groups_1` (`name`), KEY `groupid` (`groupid`,`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `hosts_groups` ( `hostgroupid` bigint(20) unsigned NOT NULL default '0', `hostid` bigint(20) unsigned NOT NULL default '0', `groupid` bigint(20) unsigned NOT NULL default '0', PRIMARY KEY (`hostgroupid`), KEY `hosts_groups_groups_1` (`hostid`,`groupid`), KEY `hostid` (`hostid`,`groupid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `fs_severity` ( `severityid` int(4) NOT NULL, `severitydesc` varchar(64) NOT NULL, `color` varchar(64) NOT NULL, `fontcolor` varchar(64) NOT NULL, `severityabbrev` varchar(64) default NULL, `severityclass` varchar(64) default NULL, PRIMARY KEY (`severityid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `fs_events` ( `eventid` int(4) NOT NULL auto_increment, `hostid` int(4) NOT NULL, `expression` varchar(255) default NULL, `description` varchar(255) default NULL, `remediation` varchar(255) default NULL, `status` int(4) NOT NULL, `value` int(4) default NULL, `severity` int(4) NOT NULL, `time_stamp` datetime default NULL, `event_type` int(4) NOT NULL, `src_addr` varchar(64) NOT NULL default '', `dest_addr` varchar(64) default NULL, `service_type` varchar(64) default NULL, `event_avail` int(11) NOT NULL, `event_perf` int(11) NOT NULL, `event_sec` int(11) NOT NULL, `itemid` int(4) NOT NULL, `triggerid` int(4) NOT NULL, `devicetype` int(11) default NULL, `acknowledged` int(11) NOT NULL, `comment` varchar(4000) default NULL, `last_changed` datetime default NULL, `username` varchar(255) default NULL, `url` varchar(255) default NULL, PRIMARY KEY (`eventid`,`src_addr`), KEY `hostid` (`hostid`), KEY `severity` (`severity`), KEY `time_stamp` (`time_stamp`), KEY `triggerid` (`triggerid`), KEY `hostid_2` (`hostid`,`severity`,`time_stamp`,`triggerid`), KEY `hostid_3` (`hostid`,`status`,`severity`) ) ENGINE=MyISAM AUTO_INCREMENT=20967 DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Replication Fallover
Ben, Check out Linux-ha ( http://linux-ha.org). We are using version 1 at my company, which is fairly easy to set up and there are a lot of good articles on it. With this you can create a virtual address to fail-over between systems, run commands / scripts during a fail over, and even kill the other node (stonith) if you have supporting hardware. For our setup, we have the client apps accessing the db through the virtual IP address. We have 2 mysql config files, one for the master and one for the slave (actually we have one m4'd config file, but you get the idea). During failover, we restart mysql on the slave using the master config file and transfer the virtual IP over. Here are a couple of key points for setting this up: 1. Slave must be running with the 'log-slave-updates' option. 2. After you run a back issue a reset master on the slave server. Hope this helps, Scott Tanner On Mon, Apr 02, 2007 at 08:43:35AM +0100, Ben Clewett wrote: I forgot to mention that I am running Linux. If anybody has some idea of software which can do this, I'd be very interested. Regards, Ben Ben Clewett wrote: Dear MySql, I'm looking into availability and wonder if any member might be able to help me. I have two databases, one Primary and one full Replication. Normally the primary is used for data input, reports are drawn from the replication. If I loose the Primary, do any members have any software they can recommend which: - Stops the replication daemon. - Sets the replication server to Read/Write. - Shuts down the primary. - Routes traffic to the replication. Any advise or ideas would be very useful... Regards, Ben Clewett. -- 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]
UPDATE / not UPDATE??
When I run the query: UPDATE InvHead I JOIN (SELECT InvNo,Sum(Rate + CASE WHEN AttendingAcademy=1 THEN 30 ELSE 0 END) AS InvTot FROM Participants P GROUP BY InvNo) AS PartSum ON PartSum.InvNo=I.InvNo SET I.Total=PartSum.InvTot WHERE (I.InvoiceType='CL' OR I.InvoiceType='CSS') AND I.RegFinishedDate IS NOT NULL It initially updates several rows. However, when I run it again, it updates no rows at all. If I then go in and manually change the Total to an incorrect value, then run it again, it updates that one row. Does MySQL check a value before updating it, and if it is the same as the value that it's updating it with, it doesn't bother updating it again? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky Sorting
I think you're right and using an IF is ultimately the right answer. However just substituting issuenum for issuedate doesn't do the right thing. Looking at my sorting a bit more analytically I think that what I need to do is, WHEN there isn't a month number, THEN substitute the existing month number from the previous issue of the same magazine with the highest month number. So this is more or less the SQL I'm considering: SELECT IF(MONTH(editions.pubdate) 0, editions.pubdate,???) AS datesort,coreinfo.title,coreinfo.volume,coreinfo.issue FROM coreinfo,editions WHERE coreinfo.category='Magazine' AND coreinfo.title LIKE 'Shadis%' AND coreinfo.mainid=editions.mainid SORT BY datesort,coreinfo.volume,coreinfo.issue The ??? needs to be something like MAX(editions.pubdate WHERE coreinfo.title IS THE SAME and coreinfo.issue IS LESS THAN THE CURRENT ISSUE) but I can't quite suss out how to do that particular lookup. Thoughts? Thanks, Shannon -- If I had that On 4/2/07, Brent Baisley [EMAIL PROTECTED] wrote: That should be fairly easy. What you are looking to do is have your sort field conditional. The ORDER BY doesn't have to specify a database field, it can be any field in your query. So you could do something like this: SELECT IF( issuemonth IS NULL, issuenum, issuedate) AS sortfield, issuedate, issuenum,... FROM tablename ORDER BY sortfield. I don't know your field names and table structure, so you'll need to change the names. - Original Message - From: Shannon Appelcline [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, March 31, 2007 1:32 AM Subject: Tricky Sorting I'm trying to figure out the best way to do a tricky bit of sorting. I'm pretty sure it's entirely possible with an IFNULL or something, but I always feel like I hit a barrier when I get to a certain level of complexity in my MYSQL. In any case, I have some magazines, each of which has a DATE, a VOLUME, and an ISSUE. The sorting is usually simple, in that the date includes a year and a month and you can sort by that. However, sometimes magazines get delayed and they start putting only a year on their issues, omitting the months. Worse, they sometimes randomly change numbers (or names) Ideally, I'd like things to sort by the date, unless there's not a month, in which case it falls back to the issue number. So for example, this would be a correct sort: 1996-01-01 Original Mag V1 #1 1996-02-01 Original Mag V1 #2 1996-00-00 Original Mag V1 #3 1996-00-00 Original Mag V1 #4 1996-05-01 Original Mag V1 #5 1996-06-01 Replacement Mag V1 #1 ORDER BY date, volume, issue does this: 1996-00-00 Original Mag V1 #3 1996-00-00 Original Mag V1 #4 1996-01-01 Original Mag V1 #1 1996-02-01 Original Mag V1 #2 1996-05-01 Original Mag V1 #5 1996-06-01 Replacement Mag V1 #1 ORDER BY volume, issue does this: 1996-01-01 Original Mag V1 #1 1996-06-01 Replacement Mag V1 #1 1996-02-01 Original Mag V1 #2 1996-00-00 Original Mag V1 #3 1996-00-00 Original Mag V1 #4 1996-05-01 Original Mag V1 #5 None of it's quite ideal. ORDER BY YEAR(date), volume, issue is what I'm using right now because it puts things in the right ballpark. Shannon -- 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: UPDATE / not UPDATE??
Don't know? 5.0.22-community-nt-log Win XP Pro InnoDB Jesse - Original Message - From: sol beach To: Jesse Sent: Monday, April 02, 2007 4:22 PM Subject: Re: UPDATE / not UPDATE?? Might this behavior be version dependent; which you neglected to provide. Same for OS name version; plus underlying storage engine type? On 4/2/07, Jesse [EMAIL PROTECTED] wrote: When I run the query: UPDATE InvHead I JOIN (SELECT InvNo,Sum(Rate + CASE WHEN AttendingAcademy=1 THEN 30 ELSE 0 END) AS InvTot FROM Participants P GROUP BY InvNo) AS PartSum ON PartSum.InvNo=I.InvNo SET I.Total=PartSum.InvTot WHERE (I.InvoiceType='CL' OR I.InvoiceType='CSS') AND I.RegFinishedDate IS NOT NULL It initially updates several rows. However, when I run it again, it updates no rows at all. If I then go in and manually change the Total to an incorrect value, then run it again, it updates that one row. Does MySQL check a value before updating it, and if it is the same as the value that it's updating it with, it doesn't bother updating it again? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE / not UPDATE??
Yes. It only reports the changed rows. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Monday, April 02, 2007 4:16 PM To: MySQL List Subject: UPDATE / not UPDATE?? When I run the query: UPDATE InvHead I JOIN (SELECT InvNo,Sum(Rate + CASE WHEN AttendingAcademy=1 THEN 30 ELSE 0 END) AS InvTot FROM Participants P GROUP BY InvNo) AS PartSum ON PartSum.InvNo=I.InvNo SET I.Total=PartSum.InvTot WHERE (I.InvoiceType='CL' OR I.InvoiceType='CSS') AND I.RegFinishedDate IS NOT NULL It initially updates several rows. However, when I run it again, it updates no rows at all. If I then go in and manually change the Total to an incorrect value, then run it again, it updates that one row. Does MySQL check a value before updating it, and if it is the same as the value that it's updating it with, it doesn't bother updating it again? Thanks, Jesse -- 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]
Joins versus Grouping/Indexing: Normalization Excessive?
So I'm currently designing a database for a web site and intra net for my campuses student radio. Since I'm not getting paid for this and I'm doing this in my free time I kinda want to take my time and have the system setup as perfectly as any one college student can. I'm currently debating on how I want to store all the songs in the system. Namely I'm not quite sure which way will eek the most performance out of MySQL. My plan so far is quite normalized, a songs table with 1:1 relationships with an Albums, Artists, and Genres table. The big benefits I see from this is when I'm building the intra net application I'll want to pull just all of the artists or all of the albums, etc. However I feel like I'm encountering issues with where to store the year field of an mp3 (do I want it on the album, song, or both) along with issues like printing everything out at once. The only other way I can think of thats relatively efficient is to have the singular songs table and have indexes on albums, artists, and genres. My question, more out of curiosity than necessity, is which of these would be more efficient (given that I'll be using the InnoDB storage engine)? Other relevant facts include it'll be using the latest, stable release of MySQL 5 and I'll be developing in PHP5 (through CakePHP's database abstraction layer). -- Thanks and Gig 'Em! Daniel Cousineau http://www.terminalfuture.com/ http://www.linkedin.com/in/dcousineau [EMAIL PROTECTED]
Re: Joins versus Grouping/Indexing: Normalization Excessive?
I think you're approaching this from the wrong angle. You'll want to put the data at the highest level at which it changes. i.e. If every song on an album is always the same year, put it at the album level, however, if it changes from song to song on a particular album, then you want it at the song level. Year wouldn't ever apply to artist I don't think, unless they're truly a one hit wonder. :) -Micah On 04/02/2007 09:14 PM, Daniel Cousineau wrote: So I'm currently designing a database for a web site and intra net for my campuses student radio. Since I'm not getting paid for this and I'm doing this in my free time I kinda want to take my time and have the system setup as perfectly as any one college student can. I'm currently debating on how I want to store all the songs in the system. Namely I'm not quite sure which way will eek the most performance out of MySQL. My plan so far is quite normalized, a songs table with 1:1 relationships with an Albums, Artists, and Genres table. The big benefits I see from this is when I'm building the intra net application I'll want to pull just all of the artists or all of the albums, etc. However I feel like I'm encountering issues with where to store the year field of an mp3 (do I want it on the album, song, or both) along with issues like printing everything out at once. The only other way I can think of thats relatively efficient is to have the singular songs table and have indexes on albums, artists, and genres. My question, more out of curiosity than necessity, is which of these would be more efficient (given that I'll be using the InnoDB storage engine)? Other relevant facts include it'll be using the latest, stable release of MySQL 5 and I'll be developing in PHP5 (through CakePHP's database abstraction layer). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
