Re: ENGINE=ARCHIVE doesn't support INDEX!!??
Correct. I assume the thinking behind it, is that you use that kind of table for huge amounts of inactive data, so it doesn't matter if your selects are a bit slower. Also, keep in mind that because it is a compressed file format, you will be scanning much more data per physical read than with a regular table, and so the table scan will be quite a bit faster, too. If there are specific values you tend to use in where clauses all the time, though, you can always consider partitioning the archived table. On Thu, Sep 30, 2010 at 1:22 AM, Daevid Vincent wrote: > I gotta ask... > > http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html#c11511 > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Is conversion required?
On Sep 29, 2010, at 5:15 PM, Paul Halliday wrote: > I just converted (reinstalled) a FreeBSD system from i386 to amd64. Of > course; I missed the memo. I have been struggling to get everything back > online. I just finished exporting a few Gigs of RRD's to XML so that I could > use them :| > > My question: I was s/rushing/stupid so I just moved /var/mysql to a > partition (i386) and reinstalled. Can I just copy this back or does some > magic need to happen first? If you're talking about the data, I wouldn't expect this change to cause issues, unless perhaps you're also updating to a different version of MySQL. That could be a problem, depending on how different the old and new versions are. Consult the "upgrading" section of the manual to see. http://dev.mysql.com/doc/refman/5.1/en/upgrading.html -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Kill All Proccesses
you can do it by a simple shell script by doing a grep of id's and passing it to mysql. On Wed, Sep 29, 2010 at 8:31 PM, Willy Mularto wrote: > AFAIK mysqladmin just kill a proccess and can not do kill all instances. > > > > sangprabv > sangpr...@gmail.com > http://www.petitiononline.com/froyo/ > > > On Sep 29, 2010, at 9:09 PM, Евгений Килимчук wrote: > > > mysqladmin kill id,id,... > > > > 2010/9/29 Willy Mularto > > Hi, > > I see so many locked tables and can not be unlocked. Is there any single > command or tools to kill all processes? > > > > > > > > > > sangprabv > > sangpr...@gmail.com > > http://www.petitiononline.com/froyo/ > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/mysql?unsub=ekilimc...@gmail.com > > > > > > > > > > -- > > Best regards, > > > > Eugene Kilimchuk > > -- Thanks Suresh Kuna MySQL DBA
Re: Kill All Proccesses
AFAIK mysqladmin just kill a proccess and can not do kill all instances. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ On Sep 29, 2010, at 9:09 PM, Евгений Килимчук wrote: > mysqladmin kill id,id,... > > 2010/9/29 Willy Mularto > Hi, > I see so many locked tables and can not be unlocked. Is there any single > command or tools to kill all processes? > > > > > sangprabv > sangpr...@gmail.com > http://www.petitiononline.com/froyo/ > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=ekilimc...@gmail.com > > > > > -- > Best regards, > > Eugene Kilimchuk
RE: ORDER BY with field alias issue
Easy. SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time_Format` FROM `reservation` ORDER BY `Time` > -Original Message- > From: BMBasal [mailto:bmb37...@gmail.com] > Sent: Wednesday, September 29, 2010 3:50 PM > To: 'Chris W'; 'MYSQL General List' > Subject: RE: ORDER BY with field alias issue > > It is inherent in your naming. > As long as your alias "time" is the same as the column name > "time", MySQL > will have no way to distinguish which one you refers to > exactly in your > order-by clause, and chooses the alias in the select-clause > as the one you > intended. You confused MySQL. > > First, why you have to hang on "time" as alias. > Second, if you don't mind adding another column in your > select-clause as a > throw-away, say, > "select DATE_FORMAT(`Time`, '%h:%i%p') as `Time`, `time` as > `timex`" > Then, you could use `timex` in your order clause. This works, > but with extra > output, not elegant. > > -Original Message- > From: Chris W [mailto:4rfv...@cox.net] > Sent: Tuesday, September 28, 2010 8:10 PM > To: MYSQL General List > Subject: ORDER BY with field alias issue > > I have the following query that is giving me problems. > > SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time` > FROM `reservation` > ORDER BY `Time` > > Problem is it sorts wrong because of the date format function output > with am and pm. I guess I should have named things differently but I > would rather not do that. Is there a standard way to get around this > and have it sort by the non-formatted time value? > > > Chris W > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=li...@cs.albany.edu > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=dae...@daevid.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ENGINE=ARCHIVE doesn't support INDEX!!??
I gotta ask... http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html#c11511 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: ORDER BY with field alias issue
It is inherent in your naming. As long as your alias "time" is the same as the column name "time", MySQL will have no way to distinguish which one you refers to exactly in your order-by clause, and chooses the alias in the select-clause as the one you intended. You confused MySQL. First, why you have to hang on "time" as alias. Second, if you don't mind adding another column in your select-clause as a throw-away, say, "select DATE_FORMAT(`Time`, '%h:%i%p') as `Time`, `time` as `timex`" Then, you could use `timex` in your order clause. This works, but with extra output, not elegant. -Original Message- From: Chris W [mailto:4rfv...@cox.net] Sent: Tuesday, September 28, 2010 8:10 PM To: MYSQL General List Subject: ORDER BY with field alias issue I have the following query that is giving me problems. SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time` FROM `reservation` ORDER BY `Time` Problem is it sorts wrong because of the date format function output with am and pm. I guess I should have named things differently but I would rather not do that. Is there a standard way to get around this and have it sort by the non-formatted time value? Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@cs.albany.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Getting a Value and an Average Value of Previous 20 Records in One Query
I have been struggling with this issue most of the day. I can get the result
I need by using 2 queries, but that takes way too long. I'm trying to see if
there is a way to get the same result within a single query.
Here's the table
CREATE TABLE `log` (
`id` int(14) NOT NULL auto_increment,
`VarName` varchar(255) NOT NULL,
`TimeString` varchar(255) NOT NULL,
`VarValue` decimal(25,6) NOT NULL
)
The log table has 1 row added each minute of the day. For each VarValue I
also need the average value of the 20 previous rows.
My 2 step solution looks like this:
$phs = $db->get_results("SELECT VarValue, TimeString FROM log WHERE VarName
= 'xyz' AND SUBSTR(TimeString,1,10) = CURDATE() ORDER BY TimeString ASC");
foreach($phs as $ph) {
$myvalue = $db->get_var("SELECT AVG(VarValue) FROM log WHERE VarName = 'xyz'
AND TimeString <= '".$ph->TimeString."' ORDER BY TimeString DESC LIMIT 20");
}
I have tried to figure a way using join as well as subselects, but haven't
hit on the right solution yet.
I appreciate some direction.
Thanks.
Al
Is conversion required?
I just converted (reinstalled) a FreeBSD system from i386 to amd64. Of course; I missed the memo. I have been struggling to get everything back online. I just finished exporting a few Gigs of RRD's to XML so that I could use them :| My question: I was s/rushing/stupid so I just moved /var/mysql to a partition (i386) and reinstalled. Can I just copy this back or does some magic need to happen first? Thanks! -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org
RE: How to get hanging 1:M table rows as single column in main query?
BRILLIANT SELECT `id_fmr`, `fmr_number`, `fmr_system`, `fmr_station`, `created_ts`, GROUP_CONCAT(`seat`) FROM `fmr` JOIN `fmr_has_seat` USING (id_fmr) JOIN `dim_seat` USING (id_dim_seat) WHERE id_fmr = 3 GROUP BY id_fmr; id_fmr fmr_number fmr_system fmr_station created_ts group_concat(`seat`) -- -- -- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C,35D,35E > -Original Message- > From: Johnny Withers [mailto:joh...@pixelated.net] > Sent: Wednesday, September 29, 2010 1:35 PM > To: Daevid Vincent > Cc: MySQL > Subject: Re: How to get hanging 1:M table rows as single > column in main query? > > GROUP_CONCAT() ? > > And group by id_fmr ? > > JW > > > On Wed, Sep 29, 2010 at 2:38 PM, Daevid Vincent > wrote: > > > Given three basic tables. An "fmr" table which has Field Maintenance > > Reports, a Seat table and a "hanging" or "glue" table to > map Seats to FMRs. > > [See below] > > > > How do I get all the Seats to be in a single "row" with the > FMR data? > > > > If I make this kind of query, they come in as separate rows: > > > > SELECT > >`id_fmr`, > >`fmr_number`, > >`fmr_system`, > >`fmr_station`, > >`created_ts`, > > `seat` > > FROM `fmr` > > JOIN `fmr_has_seat` USING (id_fmr) > > JOIN `dim_seat` USING (id_dim_seat) > > WHERE id_fmr = 3; > > > > id_fmr fmr_number fmr_system fmr_station > created_ts seat > > -- -- -- --- > --- > > 3 320237274 2333 JFK 2010-09-24 04:35:31 35C > > 3 320237274 2333 JFK 2010-09-24 04:35:31 35D > > 3 320237274 2333 JFK 2010-09-24 04:35:31 35E > > > > I want something more like: > > > > id_fmr fmr_number fmr_system fmr_station > created_ts seat > > -- -- -- --- --- > > --- > > 3 320237274 2333 JFK 2010-09-24 04:35:31 > > 35C,35D,35E > > > > > > Now, I'm going to be showing a few thousand FMR rows (and > ideally their > > seats). > > > > What I do now is use PHP to pull the FMR records that match > a certain > > criteria/filter. > > Then I pull in the entire dim_seats as an array and store > it in a session > > since it's not going to change ever. Then I loop over all > the id_fmr that I > > have pulled and look up in the fmr_has_seat table by id_fmr > and implode() > > the seats from the session array. It saves me a few joins > and gets the job > > done, but I keep feeling like there's a better way to do it. > > > > I'm thinking there's some magic with a subselect and concat > or something in > > SQL, but then I wonder if that's any more efficient as > mySQL still has to > > do two SELECTs per FMR row. This feels to me like a common > problem and > > there must be an optimal mySQL way of doing it. Hanging > tables of 1:M > > relationships are used everywhere. > > > > > == > = > > == > > > > CREATE TABLE `fmr` ( > > `id_fmr` int(11) NOT NULL auto_increment, > > `fmr_number` varchar(32) NOT NULL default '', > > `fmr_system` smallint(6) default NULL, > > `fmr_station` varchar(4) NOT NULL default '', > > `created_ts` timestamp NOT NULL default CURRENT_TIMESTAMP, > > PRIMARY KEY (`id_fmr`) > > ) ENGINE=InnoDB > > > > id_fmr fmr_number fmr_system fmr_station created_ts > > -- -- -- --- --- > > 1 319235F2A 2333 JFK 2010-09-24 04:35:31 > > 2 319235F29 2333 JFK 2010-09-24 04:35:31 > > 3 320237274 2333 JFK 2010-09-24 > 04:35:31 <--- > > 4 32023726D 2333 JFK 2010-09-24 04:35:31 > > 5 32023725A 2333 JFK 2010-09-24 04:35:31 > > 6 32023724F 2333 JFK 2010-09-24 04:35:31 > > 7 320237241 2333 LAX 2010-09-24 04:35:31 > > 8 32023723A 2333 LAX 2010-09-24 04:35:31 > > 9 320237232 2333 JFK 2010-09-24 04:35:31 > >10 320237230 2333 JFK 2010-09-24 04:35:31 > > .... .. .. .. > > > > CREATE TABLE `fmr_has_seat` ( > > `id_fmr` int(11) NOT NULL auto_increment, > > `id_dim_seat` int(10) unsigned NOT NULL, > > PRIMARY KEY (`id_fmr`,`id_dim_seat`), > > KEY `id_dim_seat` (`id_dim_seat`), > > CONSTRAINT `fmr_has_seat_ibfk_1` FOREIGN KEY (`id_fmr`) > REFERENCES `fmr` > > (`id_fmr`) ON DELETE CASCADE ON UPDATE CASCADE, > > CONSTRAINT `fmr_has_seat_ibfk_2` FOREIGN KEY > (`id_dim_seat`) REFERENCES > > `dim_seat` (`id_dim_seat`) ON DELETE CASCADE ON UPDATE CASCADE > > )
RE: INSERT DELAYED and created_on timestamps
> -Original Message-
> From: Dan Nelson [mailto:dnel...@allantgroup.com]
> Sent: Wednesday, September 29, 2010 2:26 PM
> To: Daevid Vincent
> Cc: 'MySQL'
> Subject: Re: INSERT DELAYED and created_on timestamps
>
> In the last episode (Sep 29), Daevid Vincent said:
> > I'm doing some reading on INSERT DELAYED
> > http://dev.mysql.com/doc/refman/5.0/en/insert.html
> >
> > I have a user_log table:
> >
> > CREATE TABLE `user_log` (
> > `id_user_log` bigint(20) unsigned NOT NULL auto_increment,
> > `id_user` int(10) unsigned default '0',
> > `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP
> on update CURRENT_TIMESTAMP,
> > `type`
> enum('View','Action','Admin','Search','Login','Logout','Access
> ','General',' API') NULL,
> > `source` enum('web','mobile') character set latin1
> collate latin1_general_ci default 'web',
> > `body` text character set latin1 collate latin1_general_ci,
> > ) ENGINE=InnoDB
> >
> > We are noticing a lot of these in the logs however:
> >
> > Sep 29 03:05:02 pse05 mysqld[14640]: TRANSACTION 0
> 62715480, ACTIVE 0 sec, process no 14639, OS thread id
> 2904791952 inserting
> > Sep 29 03:05:02 pse05 mysqld[14640]: mysql tables in use 1, locked 1
> > Sep 29 03:05:02 pse05 mysqld[14640]: LOCK WAIT 4 lock
> struct(s), heap size 320, undo log entries 1
> > Sep 29 03:05:02 pse05 mysqld[14640]: MySQL thread id 8330,
> query id 799424 10.10.10.46 OMT_Master update
> > Sep 29 03:05:02 pse05 mysqld[14640]: INSERT INTO user_log
> (`id_user`, `type`, `source`, `body`) VALUES ...)
> > Sep 29 03:05:02 pse05 mysqld[14640]: *** (1) WAITING FOR
> THIS LOCK TO BE GRANTED:
> >
> > So I'm thinking we could use the DELAYED or LOW_PRIORITY.
>
> INSERT DELAYED only works on MyISAM, MEMORY, and ARCHIVE
> tables. You'll get
> a 1616 error if you try it on InnoDB. MySQL 5.5 is supposed
> to have a lot
> of concurrency improvements in; can you test your application
> on that and
> see if it's any faster than 5.0?
Yeah, I just discovered that. However LOW_PRIORITY works on InnoDB tables
it seems (at least, no error). But my original question still applies (even
if for curiosity sake). Does mySQL account for the "DELAY" or
"LOW_PRIORITY" time it took to write to the DB and adjust the timestamp
accordingly or does it do the timestamp at the time of actual write vs. the
time it was originally called?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: INSERT DELAYED and created_on timestamps
In the last episode (Sep 29), Daevid Vincent said:
> I'm doing some reading on INSERT DELAYED
> http://dev.mysql.com/doc/refman/5.0/en/insert.html
>
> I have a user_log table:
>
> CREATE TABLE `user_log` (
> `id_user_log` bigint(20) unsigned NOT NULL auto_increment,
> `id_user` int(10) unsigned default '0',
> `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
> `type`
> enum('View','Action','Admin','Search','Login','Logout','Access','General','
> API') NULL,
> `source` enum('web','mobile') character set latin1 collate
> latin1_general_ci default 'web',
> `body` text character set latin1 collate latin1_general_ci,
> ) ENGINE=InnoDB
>
> We are noticing a lot of these in the logs however:
>
> Sep 29 03:05:02 pse05 mysqld[14640]: TRANSACTION 0 62715480, ACTIVE 0 sec,
> process no 14639, OS thread id 2904791952 inserting
> Sep 29 03:05:02 pse05 mysqld[14640]: mysql tables in use 1, locked 1
> Sep 29 03:05:02 pse05 mysqld[14640]: LOCK WAIT 4 lock struct(s), heap size
> 320, undo log entries 1
> Sep 29 03:05:02 pse05 mysqld[14640]: MySQL thread id 8330, query id 799424
> 10.10.10.46 OMT_Master update
> Sep 29 03:05:02 pse05 mysqld[14640]: INSERT INTO user_log (`id_user`, `type`,
> `source`, `body`) VALUES ...)
> Sep 29 03:05:02 pse05 mysqld[14640]: *** (1) WAITING FOR THIS LOCK TO BE
> GRANTED:
>
> So I'm thinking we could use the DELAYED or LOW_PRIORITY.
INSERT DELAYED only works on MyISAM, MEMORY, and ARCHIVE tables. You'll get
a 1616 error if you try it on InnoDB. MySQL 5.5 is supposed to have a lot
of concurrency improvements in; can you test your application on that and
see if it's any faster than 5.0?
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
INSERT DELAYED and created_on timestamps
I'm doing some reading on INSERT DELAYED
http://dev.mysql.com/doc/refman/5.0/en/insert.html
I have a user_log table:
CREATE TABLE `user_log` (
`id_user_log` bigint(20) unsigned NOT NULL auto_increment,
`id_user` int(10) unsigned default '0',
`created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`type`
enum('View','Action','Admin','Search','Login','Logout','Access','General','
API') NULL,
`source` enum('web','mobile') character set latin1 collate
latin1_general_ci default 'web',
`body` text character set latin1 collate latin1_general_ci,
) ENGINE=InnoDB
We are noticing a lot of these in the logs however:
Sep 29 03:05:02 pse05 mysqld[14640]: TRANSACTION 0 62715480, ACTIVE 0 sec,
process no 14639, OS thread id 2904791952 inserting
Sep 29 03:05:02 pse05 mysqld[14640]: mysql tables in use 1, locked 1
Sep 29 03:05:02 pse05 mysqld[14640]: LOCK WAIT 4 lock struct(s), heap size
320, undo log entries 1
Sep 29 03:05:02 pse05 mysqld[14640]: MySQL thread id 8330, query id 799424
10.10.10.46 OMT_Master update
Sep 29 03:05:02 pse05 mysqld[14640]: INSERT INTO user_log (`id_user`,
`type`, `source`, `body`) VALUES ...)
Sep 29 03:05:02 pse05 mysqld[14640]: *** (1) WAITING FOR THIS LOCK TO BE
GRANTED:
So I'm thinking we could use the DELAYED or LOW_PRIORITY.
My concern is the created_on time.
Is there any difference in the actual timestamp recorded in the database if
I use:
INSERT INTO user_log (id_user) VALUES (3);
INSERT DELAYED INTO user_log (id_user) VALUES (3);
INSERT LOW_PRIORITY INTO user_log (id_user) VALUES (3);
INSERT INTO user_log (id_user, created_on) VALUES (3, NOW());
INSERT DELAYED INTO user_log (id_user, created_on) VALUES (3, NOW());
(or set the date via PHP):
'INSERT INTO user_log (id_user, created_on) VALUES (3, '.gmdate().')';
'INSERT DELAYED INTO user_log (id_user, created_on) VALUES (3,
'.gmdate().')';
My point is, is mySQL smart enough to know what the time WAS when the
INSERT was supposed to be written by default, or if I DELAY it will it
process the NOW() at INSERT time or DELAYED time or what time is NOW() and
lastly if I set it with gmdate() in PHP, then that seems like it's the
exact server time at the right moment??
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Migrating my mindset from MyISAM to InnoDB
> 2. Don't stare at the screen. Start it, script the process & have it email > your phone when it's done. Do something else in the mean time. I don't literally stare at the screen -- of course I script it and do other things.. but when I have a resource limited environment, it sure would be nice to have *some idea* of the progress of the rebuild. By staring at the blank screen, I really meant to say that there is absolutely no feedback at all during the process, to get even any idea of how far it has completed and how far it has to go. >From my initial tests at rebuilding a 5.6 million record table (4.75 hours), trying to rebuild a 200 million record table would take more than 7 days. And I have two of those tables to rebuild. I can accomplish the same myISAM rebuild in two hours. >Unfortunately, no. MySQL threads should really make periodic updates to >their status so you can see the progress of long-running queries in the >"show processlist" output. http://bugs.mysql.com/bug.php?id=26182 included >a patch that adds progress updates to select statements, so it should be >possible to do the same for ALTER TABLEs as well. Wow, that sure would be nice... even with some extended information like myisamchk output. That would be an awesome feature to add to 5.5. >Expect to see anywhere from a 1.5x to a 3x increase in size when converting >from myisam to innodb, depending on your field types and indexes. It's the >penalty you pay for supporting transactions and concurrent read/write >access, and for switching to an index-organized table. Now that you put it that way, I'm thinking of just sticking with myisam. I can't spend two weeks upgrading the two 200 million row tables. Thanks for all your comments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to get hanging 1:M table rows as single column in main query?
GROUP_CONCAT() ? And group by id_fmr ? JW On Wed, Sep 29, 2010 at 2:38 PM, Daevid Vincent wrote: > Given three basic tables. An "fmr" table which has Field Maintenance > Reports, a Seat table and a "hanging" or "glue" table to map Seats to FMRs. > [See below] > > How do I get all the Seats to be in a single "row" with the FMR data? > > If I make this kind of query, they come in as separate rows: > > SELECT >`id_fmr`, >`fmr_number`, >`fmr_system`, >`fmr_station`, >`created_ts`, > `seat` > FROM `fmr` > JOIN `fmr_has_seat` USING (id_fmr) > JOIN `dim_seat` USING (id_dim_seat) > WHERE id_fmr = 3; > > id_fmr fmr_number fmr_system fmr_station created_ts seat > -- -- -- --- --- > 3 320237274 2333 JFK 2010-09-24 04:35:31 35C > 3 320237274 2333 JFK 2010-09-24 04:35:31 35D > 3 320237274 2333 JFK 2010-09-24 04:35:31 35E > > I want something more like: > > id_fmr fmr_number fmr_system fmr_station created_ts seat > -- -- -- --- --- > --- > 3 320237274 2333 JFK 2010-09-24 04:35:31 > 35C,35D,35E > > > Now, I'm going to be showing a few thousand FMR rows (and ideally their > seats). > > What I do now is use PHP to pull the FMR records that match a certain > criteria/filter. > Then I pull in the entire dim_seats as an array and store it in a session > since it's not going to change ever. Then I loop over all the id_fmr that I > have pulled and look up in the fmr_has_seat table by id_fmr and implode() > the seats from the session array. It saves me a few joins and gets the job > done, but I keep feeling like there's a better way to do it. > > I'm thinking there's some magic with a subselect and concat or something in > SQL, but then I wonder if that's any more efficient as mySQL still has to > do two SELECTs per FMR row. This feels to me like a common problem and > there must be an optimal mySQL way of doing it. Hanging tables of 1:M > relationships are used everywhere. > > === > == > > CREATE TABLE `fmr` ( > `id_fmr` int(11) NOT NULL auto_increment, > `fmr_number` varchar(32) NOT NULL default '', > `fmr_system` smallint(6) default NULL, > `fmr_station` varchar(4) NOT NULL default '', > `created_ts` timestamp NOT NULL default CURRENT_TIMESTAMP, > PRIMARY KEY (`id_fmr`) > ) ENGINE=InnoDB > > id_fmr fmr_number fmr_system fmr_station created_ts > -- -- -- --- --- > 1 319235F2A 2333 JFK 2010-09-24 04:35:31 > 2 319235F29 2333 JFK 2010-09-24 04:35:31 > 3 320237274 2333 JFK 2010-09-24 04:35:31 <--- > 4 32023726D 2333 JFK 2010-09-24 04:35:31 > 5 32023725A 2333 JFK 2010-09-24 04:35:31 > 6 32023724F 2333 JFK 2010-09-24 04:35:31 > 7 320237241 2333 LAX 2010-09-24 04:35:31 > 8 32023723A 2333 LAX 2010-09-24 04:35:31 > 9 320237232 2333 JFK 2010-09-24 04:35:31 >10 320237230 2333 JFK 2010-09-24 04:35:31 > .... .. .. .. > > CREATE TABLE `fmr_has_seat` ( > `id_fmr` int(11) NOT NULL auto_increment, > `id_dim_seat` int(10) unsigned NOT NULL, > PRIMARY KEY (`id_fmr`,`id_dim_seat`), > KEY `id_dim_seat` (`id_dim_seat`), > CONSTRAINT `fmr_has_seat_ibfk_1` FOREIGN KEY (`id_fmr`) REFERENCES `fmr` > (`id_fmr`) ON DELETE CASCADE ON UPDATE CASCADE, > CONSTRAINT `fmr_has_seat_ibfk_2` FOREIGN KEY (`id_dim_seat`) REFERENCES > `dim_seat` (`id_dim_seat`) ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB > > id_fmr id_dim_seat > -- --- > 3 888 <--- > 3 889 <--- > 3 890 <--- > 4 422 > 4 423 > 4 551 > 4 552 > 4 553 > 5 420 > 5 550 > 5 628 > 5 629 > 5 706 > 5 707 > 5 811 > ... ... > > CREATE TABLE `dim_seat` ( > `id_dim_seat` int(10) unsigned NOT NULL auto_increment, > `seat` varchar(4) default NULL, > PRIMARY KEY (`id_dim_seat`), > KEY `seat` (`seat`) > ) ENGINE=InnoDB > > id_dim_seat seat > --- -- > ... ... >888 35C <--- >889 35D <--- >890 35E <--- >891 35F >892 35G >... ... > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net > > -- - Johnny Withers 601.209.4985 joh...@pixelated.net
How to get hanging 1:M table rows as single column in main query?
Given three basic tables. An "fmr" table which has Field Maintenance Reports, a Seat table and a "hanging" or "glue" table to map Seats to FMRs. [See below] How do I get all the Seats to be in a single "row" with the FMR data? If I make this kind of query, they come in as separate rows: SELECT `id_fmr`, `fmr_number`, `fmr_system`, `fmr_station`, `created_ts`, `seat` FROM `fmr` JOIN `fmr_has_seat` USING (id_fmr) JOIN `dim_seat` USING (id_dim_seat) WHERE id_fmr = 3; id_fmr fmr_number fmr_system fmr_station created_ts seat -- -- -- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C 3 320237274 2333 JFK 2010-09-24 04:35:31 35D 3 320237274 2333 JFK 2010-09-24 04:35:31 35E I want something more like: id_fmr fmr_number fmr_system fmr_station created_ts seat -- -- -- --- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C,35D,35E Now, I'm going to be showing a few thousand FMR rows (and ideally their seats). What I do now is use PHP to pull the FMR records that match a certain criteria/filter. Then I pull in the entire dim_seats as an array and store it in a session since it's not going to change ever. Then I loop over all the id_fmr that I have pulled and look up in the fmr_has_seat table by id_fmr and implode() the seats from the session array. It saves me a few joins and gets the job done, but I keep feeling like there's a better way to do it. I'm thinking there's some magic with a subselect and concat or something in SQL, but then I wonder if that's any more efficient as mySQL still has to do two SELECTs per FMR row. This feels to me like a common problem and there must be an optimal mySQL way of doing it. Hanging tables of 1:M relationships are used everywhere. === == CREATE TABLE `fmr` ( `id_fmr` int(11) NOT NULL auto_increment, `fmr_number` varchar(32) NOT NULL default '', `fmr_system` smallint(6) default NULL, `fmr_station` varchar(4) NOT NULL default '', `created_ts` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id_fmr`) ) ENGINE=InnoDB id_fmr fmr_number fmr_system fmr_station created_ts -- -- -- --- --- 1 319235F2A 2333 JFK 2010-09-24 04:35:31 2 319235F29 2333 JFK 2010-09-24 04:35:31 3 320237274 2333 JFK 2010-09-24 04:35:31 <--- 4 32023726D 2333 JFK 2010-09-24 04:35:31 5 32023725A 2333 JFK 2010-09-24 04:35:31 6 32023724F 2333 JFK 2010-09-24 04:35:31 7 320237241 2333 LAX 2010-09-24 04:35:31 8 32023723A 2333 LAX 2010-09-24 04:35:31 9 320237232 2333 JFK 2010-09-24 04:35:31 10 320237230 2333 JFK 2010-09-24 04:35:31 .... .. .. .. CREATE TABLE `fmr_has_seat` ( `id_fmr` int(11) NOT NULL auto_increment, `id_dim_seat` int(10) unsigned NOT NULL, PRIMARY KEY (`id_fmr`,`id_dim_seat`), KEY `id_dim_seat` (`id_dim_seat`), CONSTRAINT `fmr_has_seat_ibfk_1` FOREIGN KEY (`id_fmr`) REFERENCES `fmr` (`id_fmr`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fmr_has_seat_ibfk_2` FOREIGN KEY (`id_dim_seat`) REFERENCES `dim_seat` (`id_dim_seat`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB id_fmr id_dim_seat -- --- 3 888 <--- 3 889 <--- 3 890 <--- 4 422 4 423 4 551 4 552 4 553 5 420 5 550 5 628 5 629 5 706 5 707 5 811 ... ... CREATE TABLE `dim_seat` ( `id_dim_seat` int(10) unsigned NOT NULL auto_increment, `seat` varchar(4) default NULL, PRIMARY KEY (`id_dim_seat`), KEY `seat` (`seat`) ) ENGINE=InnoDB id_dim_seat seat --- -- ... ... 888 35C <--- 889 35D <--- 890 35E <--- 891 35F 892 35G ... ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Not to show until a certain date
On Wed, Sep 29, 2010 at 8:33 AM, Patrice Olivier-Wilson wrote: > On 9/28/10 8:33 PM, Chris W wrote: > >> >> SELECT * >> FROM announcements >> WHERE announcements_expiredate > CURDATE() >> AND announcements_postdate <= CURDATE() >> ORDER BY announcements_expiredate ASC Or how about something like this: SELECT * FROM announcements WHERE CURDATE() between announcements_postdate and announcements_expiredate ORDER BY announcements_expiredate ASC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select NICE
At 10:49 AM 9/29/2010, Steve Staples wrote: Google has not been kind to me on this one, so I figured I would ask here... how can I select with NICE options, so that it doesn't KILL my server, or any other queries... Do you understand what I am asking? Steve Steve, You might look at http://www.databasedesign-resource.com/mysql-tuning.html. It mentions "nice" option near the bottom of the document. I've never used it myself. http://www.google.ca/#hl=en&biw=1440&bih=684&q=mysql+renice+priority&aq=f&aqi=&aql=&oq=&gs_rfai=&fp=1558102cc0a7bff1 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select NICE
Google has not been kind to me on this one, so I figured I would ask here... how can I select with NICE options, so that it doesn't KILL my server, or any other queries... Do you understand what I am asking? Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Migrating my mindset from MyISAM to InnoDB
In the last episode (Sep 28), Gavin Towey said: > Also note, 5.5 isn't production ready. 5.1 is the current GA release. 5.5 is really really close, though (5.5.6 is marked as Release Candidate), Better to switch now while you're already doing a migration, and then install 5.5.x updates as they happen. > From: Hank [mailto:hes...@gmail.com] >> Primarily due to many positive posts I've seen about MySQL 5.5 and >> advances in InnoDB, I'm seriously considering converting all my MyISAM >> databases to InnoDB. I don't need many of the InnoDB features, but if >> I'm going to upgrade from 4.1.14 to 5.5, I might as well bit the bullet >> since that seems to be the direction of MySQL/Oracle. >> >> I very much like how verbose myisamchk is in detailing which index it >> is currently rebuilding, and the progress in terms of records >> re-indexed. >> >> SO, my questions are this: >> >> 1. With InnoDB, do the indexes ever need to be rebuilt to reduce index >> size and improve performance like I get with MyISAM? All databases can benefit from occasional index rebuilds/optimizations to recover slack space. Once a system gets big enough, though, the downtime required for the OPTIMIZE TABLE may outweigh the benefits. Just add more disk and RAM :) >> 2. If so, are there any tools like myisamchk to monitor the InnoDB index >> rebuild process, other than issuing a "repair table..." and staring >> indefinitely at a blank screen until it finishes hours later? Unfortunately, no. MySQL threads should really make periodic updates to their status so you can see the progress of long-running queries in the "show processlist" output. http://bugs.mysql.com/bug.php?id=26182 included a patch that adds progress updates to select statements, so it should be possible to do the same for ALTER TABLEs as well. >> 3. I've been testing the rebuild process during upgrading using "alter >> table engine=innodb" to convert my tables from 4.1.14 to >> 5.5.6, and I'm seeing a 130% increase (more than double) in the raw disk >> space required for the new InnoDB tables compared to their old MyISAM >> counterparts. (I am using single-file-per-table). Is this normal? If >> not, how can I adjust the space requirements for these tables so they >> don't take up so much additional space? Expect to see anywhere from a 1.5x to a 3x increase in size when converting from myisam to innodb, depending on your field types and indexes. It's the penalty you pay for supporting transactions and concurrent read/write access, and for switching to an index-organized table. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Kill All Proccesses
mysqladmin kill id,id,... 2010/9/29 Willy Mularto > Hi, > I see so many locked tables and can not be unlocked. Is there any single > command or tools to kill all processes? > > > > > sangprabv > sangpr...@gmail.com > http://www.petitiononline.com/froyo/ > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=ekilimc...@gmail.com > > -- Best regards, Eugene Kilimchuk
Query locked at "Writing to net" state
Hello, I have a Windows Delphi application (Zeos component) which makes a request on a remote MySQL database. The server is in France. On a Windows workstation that is located in France (500 kilometers), the application works well despite the 200,000 records to retrieve. The same application is not working on workstations on various ADSL connections in Israel. In the MySQL process list, the application remains in state "writing to net" and never goes out "sending data". While in France, "writing to net" is fast and followed by "sending data". The same query in MySQL Workbench operates seamlessly in Israel and in France. Do you have an idea or a line of inquiry? Server config: Core2Duo, 4GB, 100Mbps, CentOS5.2. Mysql5 Thank you for your help, Regards, Denis -- Denis Lefebvre
Re: Not to show until a certain date
On 9/28/10 8:33 PM, Chris W wrote: SELECT * FROM announcements WHERE announcements_expiredate > CURDATE() AND announcements_postdate <= CURDATE() ORDER BY announcements_expiredate ASC Thank you! -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Kill All Proccesses
Hi, I see so many locked tables and can not be unlocked. Is there any single command or tools to kill all processes? sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
