Re: Does putting a LIMIT on a DELETE clause make any difference?
Vincent, Since the column is indexed, it would use the index during the delete. regards anandkl On Thu, Sep 9, 2010 at 5:47 AM, Daevid Vincent dae...@daevid.com wrote: I am curious about something. I have a glue or hanging table like so: CREATE TABLE `fault_impact_has_fault_system_impact` ( `id_fault_impact` int(10) unsigned NOT NULL, `id_fault_system_impact` smallint(5) unsigned NOT NULL, KEY `fault_impact_key` (`id_fault_impact`,`id_fault_system_impact`), KEY `id_fault_system_impact` (`id_fault_system_impact`), CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_1` FOREIGN KEY (`id_fault_impact`) REFERENCES `fault_impact` (`id_fault_impact`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_2` FOREIGN KEY (`id_fault_system_impact`) REFERENCES `fault_system_impact` (`id_fault_system_impact`) ON DELETE CASCADE ON UPDATE CASCADE ) And a lookup table like this: CREATE TABLE `fault_system_impact` ( `id_fault_system_impact` smallint(5) unsigned NOT NULL auto_increment, `fault_sytem_impact_name` varchar(50) NOT NULL, PRIMARY KEY (`id_fault_system_impact`) ) I have a bunch of checkboxes in a FORM and so in order to update properly, I wipe out all the PK IDs and then start inserting. It looks like this: UPDATE `fault_impact` SET `bite_code` = '12', `bite_code_type` = '32', `bite_subcode` = '21', `description_text` = 'Some random fault description here.', `fault_id` = '11-1', `fault_impact_other_explain` = '', `id_fault_area_impact` = '3', `symptom_lru_id` = '232', `symptom_lru_subid` = '34', `sys_perf_affected` = '', `update_date` = '2010-09-09 00:04:29' WHERE id_fault_impact = '2495' LIMIT 1; DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = 2495; INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 1); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 3); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 2); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 7); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 10); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 14); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 9); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 4); Given that I know there can only be a maximum of id_fault_system_impact IDs -- currently there are 17 rows in the fault_system_impact table -- and they're unique to any given id_fault_impact, would it benefit me to change my DELETE statement to something like this: DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = 2495 LIMIT 17; Since the fault_impact_has_fault_system_impact table could have thousands of rows and it seems that mySQL would do a table scan? Unfortunately, you can't EXPLAIN on a DELETE to see what it might be doing. :( OR is mySQL smart enough to know that the id_fault_impact is an index and therefore it will just be right quick and stop after deleting those 8 rows above? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
statistics statement in general log
Hi guys, anyone knows what's the meaning of statistics in general log ? thanks. ... 100908 9:40:11 21513 Statistics 100908 9:40:14 21518 Statistics 100908 9:40:19 21518 Statistics 100908 9:40:21 21513 Statistics 100908 9:40:24 21518 Statistics 100908 9:40:29 21518 Statistics 100908 9:40:31 21513 Statistics 100908 9:40:34 21518 Statistics 100908 9:40:39 21518 Statistics 100908 9:40:41 21513 Statistics 100908 9:40:44 21518 Statistics 100908 9:40:49 21518 Statistics 100908 9:40:51 21513 Statistics ... this general log is on my slave server. those Statistics blocked slave SQL thread, results in some transactions Lock wait timeout exceeded; try restarting transaction errors. Actually, these transactions run fast on my master. *^_^*
Fwd: Query SUM help
Any help would be really appreciated ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: [MySQL] mysql@lists.mysql.com Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 GROUP BY players.teams_id) s1 ORDER BY s1.total_team_rating DESC This gives me the total of players_master.rating for each players.teams_id. However, I'm wanting to only base the players_master.rating on the top 11 records in the players table for each team. How can I modify my query to achieve this ? Thanks Neil
Re: Query SUM help
did u try to use LIMIT after ORDER BY On Thu, Sep 9, 2010 at 1:27 PM, Tompkins Neil neil.tompk...@googlemail.comwrote: Any help would be really appreciated ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: [MySQL] mysql@lists.mysql.com Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 GROUP BY players.teams_id) s1 ORDER BY s1.total_team_rating DESC This gives me the total of players_master.rating for each players.teams_id. However, I'm wanting to only base the players_master.rating on the top 11 records in the players table for each team. How can I modify my query to achieve this ? Thanks Neil
Re: Query SUM help
Yes, but it doesn't work. Basically I want the SUM(players_master.rating) only to SUM the top 11 players from each team. Any suggestions ? Cheers Neil On Thu, Sep 9, 2010 at 9:17 AM, Ananda Kumar anan...@gmail.com wrote: did u try to use LIMIT after ORDER BY On Thu, Sep 9, 2010 at 1:27 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Any help would be really appreciated ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: [MySQL] mysql@lists.mysql.com Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 GROUP BY players.teams_id) s1 ORDER BY s1.total_team_rating DESC This gives me the total of players_master.rating for each players.teams_id. However, I'm wanting to only base the players_master.rating on the top 11 records in the players table for each team. How can I modify my query to achieve this ? Thanks Neil
Re: Query SUM help
try using the RANK function... something like select * from table order by RANK desc limit 11.this will get u the top 11 rows. regards anandkl On Thu, Sep 9, 2010 at 1:55 PM, Tompkins Neil neil.tompk...@googlemail.comwrote: Yes, but it doesn't work. Basically I want the SUM(players_master.rating) only to SUM the top 11 players from each team. Any suggestions ? Cheers Neil On Thu, Sep 9, 2010 at 9:17 AM, Ananda Kumar anan...@gmail.com wrote: did u try to use LIMIT after ORDER BY On Thu, Sep 9, 2010 at 1:27 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Any help would be really appreciated ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: [MySQL] mysql@lists.mysql.com Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 GROUP BY players.teams_id) s1 ORDER BY s1.total_team_rating DESC This gives me the total of players_master.rating for each players.teams_id. However, I'm wanting to only base the players_master.rating on the top 11 records in the players table for each team. How can I modify my query to achieve this ? Thanks Neil
Re: Fwd: Query SUM help
On 9/9/2010 3:57 AM, Tompkins Neil wrote: Any help would be really appreciated ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: [MySQL] mysql@lists.mysql.com Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 GROUP BY players.teams_id) s1 ORDER BY s1.total_team_rating DESC This gives me the total of players_master.rating for each players.teams_id. However, I'm wanting to only base the players_master.rating on the top 11 records in the players table for each team. How can I modify my query to achieve this ? Thanks Neil The meat of your problem is the top 11 players part. The SQL language operates on sets, not sequences. In order to find the top 11 you need to somehow identify them explicitly so that you can process them as a set of values. Do you have a column on your `players` table that sequences (from 1..n) the players in the order you want them ranked? If not, you will need to add that data to your `players` table (or build a temporary table with that information in it), then pick the top 11, then work on their SUM()-s. Can you not just filter out the top 11 in your client code from the query that includes all players totals? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replaying the mysqld.log file from production onto QA???
So.. I am trying to mimic replaying production like queries so joins, temp tables etc... are stuff I am trying to test as well. Just doing a dump and import is no more than export and importing, I also want to test selects, updates :-) Thanks for replying :-) Nunzio From: andrew.2.mo...@nokia.com andrew.2.mo...@nokia.com To: nunziodav...@yahoo.com; mysql@lists.mysql.com Sent: Wed, September 8, 2010 4:19:19 PM Subject: Re: Replaying the mysqld.log file from production onto QA??? Try using a mysqldump. - Reply message - From: ext Nunzio Daveri nunziodav...@yahoo.com Date: Wed, Sep 8, 2010 19:25 Subject: Replaying the mysqld.log file from production onto QA??? To: mysql@lists.mysql.com mysql@lists.mysql.com Hello all, I am trying to REPLAY the mysqld.log file from a production 5.1.4 server onto a QA server with 5.5 running and was wondering if there is an easy way of replaying the mysqld.log file? Just want to make sure all of the kinds of inserts, updates, selects and deletes work just as well on the 5.5 box, esp since we are turning on replication and copying tables from another server onto this server. Any ideas please? TIA... Nunzio
Re: Does putting a LIMIT on a DELETE clause make any difference?
Correct. To verify this, simply create a select with the same structure as your delete - the execution plan will be similar. I do not believe limit will help you, however, as it is only applied after execution, when the full dataset is known. On Thu, Sep 9, 2010 at 8:06 AM, Ananda Kumar anan...@gmail.com wrote: Vincent, Since the column is indexed, it would use the index during the delete. regards anandkl On Thu, Sep 9, 2010 at 5:47 AM, Daevid Vincent dae...@daevid.com wrote: I am curious about something. I have a glue or hanging table like so: CREATE TABLE `fault_impact_has_fault_system_impact` ( `id_fault_impact` int(10) unsigned NOT NULL, `id_fault_system_impact` smallint(5) unsigned NOT NULL, KEY `fault_impact_key` (`id_fault_impact`,`id_fault_system_impact`), KEY `id_fault_system_impact` (`id_fault_system_impact`), CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_1` FOREIGN KEY (`id_fault_impact`) REFERENCES `fault_impact` (`id_fault_impact`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_2` FOREIGN KEY (`id_fault_system_impact`) REFERENCES `fault_system_impact` (`id_fault_system_impact`) ON DELETE CASCADE ON UPDATE CASCADE ) And a lookup table like this: CREATE TABLE `fault_system_impact` ( `id_fault_system_impact` smallint(5) unsigned NOT NULL auto_increment, `fault_sytem_impact_name` varchar(50) NOT NULL, PRIMARY KEY (`id_fault_system_impact`) ) I have a bunch of checkboxes in a FORM and so in order to update properly, I wipe out all the PK IDs and then start inserting. It looks like this: UPDATE `fault_impact` SET `bite_code` = '12', `bite_code_type` = '32', `bite_subcode` = '21', `description_text` = 'Some random fault description here.', `fault_id` = '11-1', `fault_impact_other_explain` = '', `id_fault_area_impact` = '3', `symptom_lru_id` = '232', `symptom_lru_subid` = '34', `sys_perf_affected` = '', `update_date` = '2010-09-09 00:04:29' WHERE id_fault_impact = '2495' LIMIT 1; DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = 2495; INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 1); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 3); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 2); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 7); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 10); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 14); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 9); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 4); Given that I know there can only be a maximum of id_fault_system_impact IDs -- currently there are 17 rows in the fault_system_impact table -- and they're unique to any given id_fault_impact, would it benefit me to change my DELETE statement to something like this: DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = 2495 LIMIT 17; Since the fault_impact_has_fault_system_impact table could have thousands of rows and it seems that mySQL would do a table scan? Unfortunately, you can't EXPLAIN on a DELETE to see what it might be doing. :( OR is mySQL smart enough to know that the id_fault_impact is an index and therefore it will just be right quick and stop after deleting those 8 rows above? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com -- 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: Query SUM help
MySQL doesn't have the windowing functions that some other databases provide, but you can probably achieve the same effect with a couple user-defined variables: select teams_id as my_teams_id ,sum(rating) as total_team_rating from (select players.teams_id ,players.players_id ,players_master.rating ,if(@team players.teams_id, @row := 1, @row := @row + 1) as rank, @team := players.team_id from players join players_master on players.players_id = players_master.players_id where players.worlds_id = 1 and players.red_cards = 0 and players.injury_duration_remaining = 0 order by players.teams_id, players_master.rating desc) s1 where rank = 11 group by teams_id; -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, September 09, 2010 1:58 AM To: [MySQL] Subject: Fwd: Query SUM help Any help would be really appreciated ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: [MySQL] mysql@lists.mysql.com Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 GROUP BY players.teams_id) s1 ORDER BY s1.total_team_rating DESC This gives me the total of players_master.rating for each players.teams_id. However, I'm wanting to only base the players_master.rating on the top 11 records in the players table for each team. How can I modify my query to achieve this ? Thanks Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Trying to remove a filesort.
I wonder if anyone could help with a query which I've been unable to prevent from using a filesort. Might be something obvious I'm overlooking! I have a table which tracks milestones in distributed computing projects Create Table: CREATE TABLE `boinc_milestone` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL, `stat_date` date NOT NULL DEFAULT '-00-00', `milestone_type` char(1) NOT NULL DEFAULT '0', `milestone` double NOT NULL DEFAULT '0', `cpid` varchar(32) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), KEY `two` (`proj`,`stat_date`,`id`,`milestone`), KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 These are added to on a daily basis as users pass the various credit milestones so for instance you can end up with rows for 1000,5000,1,5,100 etc on different dates as time goes on. Now on one page for display I want to show the latest milestone for each project for a particular cpid. The query I use is as follows: select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = '$cpid' group by proj order by stat_date desc The order by causes the filesort and I can't find an easy way around it. mysql explain select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G *** 1. row *** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,two,cpid,team key: cpid key_len: 35 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: stats.a.proj rows: 1 Extra: Using where 2 rows in set (0.00 sec) I could just remove the order by altogether and perform the sort in php afterwards I guess but any other ideas? Thanks Phil -- Distributed Computing stats http://stats.free-dc.org
Re: Trying to remove a filesort.
The filesort is probably necessary because of the number of rows in the result set to be ordered. How many rows do you get out of this query? - michael dykman On Thu, Sep 9, 2010 at 1:53 PM, Phil freedc@gmail.com wrote: I wonder if anyone could help with a query which I've been unable to prevent from using a filesort. Might be something obvious I'm overlooking! I have a table which tracks milestones in distributed computing projects Create Table: CREATE TABLE `boinc_milestone` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL, `stat_date` date NOT NULL DEFAULT '-00-00', `milestone_type` char(1) NOT NULL DEFAULT '0', `milestone` double NOT NULL DEFAULT '0', `cpid` varchar(32) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), KEY `two` (`proj`,`stat_date`,`id`,`milestone`), KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 These are added to on a daily basis as users pass the various credit milestones so for instance you can end up with rows for 1000,5000,1,5,100 etc on different dates as time goes on. Now on one page for display I want to show the latest milestone for each project for a particular cpid. The query I use is as follows: select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = '$cpid' group by proj order by stat_date desc The order by causes the filesort and I can't find an easy way around it. mysql explain select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G *** 1. row *** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,two,cpid,team key: cpid key_len: 35 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: stats.a.proj rows: 1 Extra: Using where 2 rows in set (0.00 sec) I could just remove the order by altogether and perform the sort in php afterwards I guess but any other ideas? Thanks Phil -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to remove a filesort.
On average it would be between 10 and 40, certainly no more than 100. On Thu, Sep 9, 2010 at 2:57 PM, Michael Dykman mdyk...@gmail.com wrote: The filesort is probably necessary because of the number of rows in the result set to be ordered. How many rows do you get out of this query? - michael dykman On Thu, Sep 9, 2010 at 1:53 PM, Phil freedc@gmail.com wrote: I wonder if anyone could help with a query which I've been unable to prevent from using a filesort. Might be something obvious I'm overlooking! I have a table which tracks milestones in distributed computing projects Create Table: CREATE TABLE `boinc_milestone` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL, `stat_date` date NOT NULL DEFAULT '-00-00', `milestone_type` char(1) NOT NULL DEFAULT '0', `milestone` double NOT NULL DEFAULT '0', `cpid` varchar(32) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), KEY `two` (`proj`,`stat_date`,`id`,`milestone`), KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 These are added to on a daily basis as users pass the various credit milestones so for instance you can end up with rows for 1000,5000,1,5,100 etc on different dates as time goes on. Now on one page for display I want to show the latest milestone for each project for a particular cpid. The query I use is as follows: select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = '$cpid' group by proj order by stat_date desc The order by causes the filesort and I can't find an easy way around it. mysql explain select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G *** 1. row *** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,two,cpid,team key: cpid key_len: 35 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: stats.a.proj rows: 1 Extra: Using where 2 rows in set (0.00 sec) I could just remove the order by altogether and perform the sort in php afterwards I guess but any other ideas? Thanks Phil -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Distributed Computing stats http://stats.free-dc.org
Re: Trying to remove a filesort.
How many rows before the GROUP BY? Group by is, in effect a sorting process.. perhaps that contains enough data to justify going to disk. What is the value of the variable sort_buffer_size? show variables like '%sort%'; - md On Thu, Sep 9, 2010 at 3:04 PM, Phil freedc@gmail.com wrote: On average it would be between 10 and 40, certainly no more than 100. On Thu, Sep 9, 2010 at 2:57 PM, Michael Dykman mdyk...@gmail.com wrote: The filesort is probably necessary because of the number of rows in the result set to be ordered. How many rows do you get out of this query? - michael dykman On Thu, Sep 9, 2010 at 1:53 PM, Phil freedc@gmail.com wrote: I wonder if anyone could help with a query which I've been unable to prevent from using a filesort. Might be something obvious I'm overlooking! I have a table which tracks milestones in distributed computing projects Create Table: CREATE TABLE `boinc_milestone` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL, `stat_date` date NOT NULL DEFAULT '-00-00', `milestone_type` char(1) NOT NULL DEFAULT '0', `milestone` double NOT NULL DEFAULT '0', `cpid` varchar(32) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), KEY `two` (`proj`,`stat_date`,`id`,`milestone`), KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 These are added to on a daily basis as users pass the various credit milestones so for instance you can end up with rows for 1000,5000,1,5,100 etc on different dates as time goes on. Now on one page for display I want to show the latest milestone for each project for a particular cpid. The query I use is as follows: select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = '$cpid' group by proj order by stat_date desc The order by causes the filesort and I can't find an easy way around it. mysql explain select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G *** 1. row *** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,two,cpid,team key: cpid key_len: 35 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: stats.a.proj rows: 1 Extra: Using where 2 rows in set (0.00 sec) I could just remove the order by altogether and perform the sort in php afterwards I guess but any other ideas? Thanks Phil -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to remove a filesort.
Even prior to the group by it's still not likely to ever be more than 200 or so maximum. I have the sort_buffer_size at 256Mb so I don't believe it's that either :( On Thu, Sep 9, 2010 at 3:16 PM, Michael Dykman mdyk...@gmail.com wrote: How many rows before the GROUP BY? Group by is, in effect a sorting process.. perhaps that contains enough data to justify going to disk. What is the value of the variable sort_buffer_size? show variables like '%sort%'; - md On Thu, Sep 9, 2010 at 3:04 PM, Phil freedc@gmail.com wrote: On average it would be between 10 and 40, certainly no more than 100. On Thu, Sep 9, 2010 at 2:57 PM, Michael Dykman mdyk...@gmail.com wrote: The filesort is probably necessary because of the number of rows in the result set to be ordered. How many rows do you get out of this query? - michael dykman On Thu, Sep 9, 2010 at 1:53 PM, Phil freedc@gmail.com wrote: I wonder if anyone could help with a query which I've been unable to prevent from using a filesort. Might be something obvious I'm overlooking! I have a table which tracks milestones in distributed computing projects Create Table: CREATE TABLE `boinc_milestone` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL, `stat_date` date NOT NULL DEFAULT '-00-00', `milestone_type` char(1) NOT NULL DEFAULT '0', `milestone` double NOT NULL DEFAULT '0', `cpid` varchar(32) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), KEY `two` (`proj`,`stat_date`,`id`,`milestone`), KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 These are added to on a daily basis as users pass the various credit milestones so for instance you can end up with rows for 1000,5000,1,5,100 etc on different dates as time goes on. Now on one page for display I want to show the latest milestone for each project for a particular cpid. The query I use is as follows: select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = '$cpid' group by proj order by stat_date desc The order by causes the filesort and I can't find an easy way around it. mysql explain select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G *** 1. row *** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,two,cpid,team key: cpid key_len: 35 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: stats.a.proj rows: 1 Extra: Using where 2 rows in set (0.00 sec) I could just remove the order by altogether and perform the sort in php afterwards I guess but any other ideas? Thanks Phil -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Distributed Computing stats http://stats.free-dc.org
Re: Trying to remove a filesort.
have u set sort_buffer_size at session level or in my.cnf. Setting high value in my.cnf, will cause mysql to run out off MEMORY and paging will happen regards anandkl On Fri, Sep 10, 2010 at 1:10 AM, Phil freedc@gmail.com wrote: Even prior to the group by it's still not likely to ever be more than 200 or so maximum. I have the sort_buffer_size at 256Mb so I don't believe it's that either :( On Thu, Sep 9, 2010 at 3:16 PM, Michael Dykman mdyk...@gmail.com wrote: How many rows before the GROUP BY? Group by is, in effect a sorting process.. perhaps that contains enough data to justify going to disk. What is the value of the variable sort_buffer_size? show variables like '%sort%'; - md On Thu, Sep 9, 2010 at 3:04 PM, Phil freedc@gmail.com wrote: On average it would be between 10 and 40, certainly no more than 100. On Thu, Sep 9, 2010 at 2:57 PM, Michael Dykman mdyk...@gmail.com wrote: The filesort is probably necessary because of the number of rows in the result set to be ordered. How many rows do you get out of this query? - michael dykman On Thu, Sep 9, 2010 at 1:53 PM, Phil freedc@gmail.com wrote: I wonder if anyone could help with a query which I've been unable to prevent from using a filesort. Might be something obvious I'm overlooking! I have a table which tracks milestones in distributed computing projects Create Table: CREATE TABLE `boinc_milestone` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL, `stat_date` date NOT NULL DEFAULT '-00-00', `milestone_type` char(1) NOT NULL DEFAULT '0', `milestone` double NOT NULL DEFAULT '0', `cpid` varchar(32) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), KEY `two` (`proj`,`stat_date`,`id`,`milestone`), KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 These are added to on a daily basis as users pass the various credit milestones so for instance you can end up with rows for 1000,5000,1,5,100 etc on different dates as time goes on. Now on one page for display I want to show the latest milestone for each project for a particular cpid. The query I use is as follows: select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = '$cpid' group by proj order by stat_date desc The order by causes the filesort and I can't find an easy way around it. mysql explain select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G *** 1. row *** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,two,cpid,team key: cpid key_len: 35 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: stats.a.proj rows: 1 Extra: Using where 2 rows in set (0.00 sec) I could just remove the order by altogether and perform the sort in php afterwards I guess but any other ideas? Thanks Phil -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Distributed Computing stats http://stats.free-dc.org
Re: Trying to remove a filesort.
It's in my.cnf. There is 12Gb in the database server and I watch it fairly carefully and have not gone into swap yet in the past few years. On Thu, Sep 9, 2010 at 3:43 PM, Ananda Kumar anan...@gmail.com wrote: have u set sort_buffer_size at session level or in my.cnf. Setting high value in my.cnf, will cause mysql to run out off MEMORY and paging will happen regards anandkl On Fri, Sep 10, 2010 at 1:10 AM, Phil freedc@gmail.com wrote: Even prior to the group by it's still not likely to ever be more than 200 or so maximum. I have the sort_buffer_size at 256Mb so I don't believe it's that either :( On Thu, Sep 9, 2010 at 3:16 PM, Michael Dykman mdyk...@gmail.com wrote: How many rows before the GROUP BY? Group by is, in effect a sorting process.. perhaps that contains enough data to justify going to disk. What is the value of the variable sort_buffer_size? show variables like '%sort%'; - md On Thu, Sep 9, 2010 at 3:04 PM, Phil freedc@gmail.com wrote: On average it would be between 10 and 40, certainly no more than 100. On Thu, Sep 9, 2010 at 2:57 PM, Michael Dykman mdyk...@gmail.com wrote: The filesort is probably necessary because of the number of rows in the result set to be ordered. How many rows do you get out of this query? - michael dykman On Thu, Sep 9, 2010 at 1:53 PM, Phil freedc@gmail.com wrote: I wonder if anyone could help with a query which I've been unable to prevent from using a filesort. Might be something obvious I'm overlooking! I have a table which tracks milestones in distributed computing projects Create Table: CREATE TABLE `boinc_milestone` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL, `stat_date` date NOT NULL DEFAULT '-00-00', `milestone_type` char(1) NOT NULL DEFAULT '0', `milestone` double NOT NULL DEFAULT '0', `cpid` varchar(32) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), KEY `two` (`proj`,`stat_date`,`id`,`milestone`), KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 These are added to on a daily basis as users pass the various credit milestones so for instance you can end up with rows for 1000,5000,1,5,100 etc on different dates as time goes on. Now on one page for display I want to show the latest milestone for each project for a particular cpid. The query I use is as follows: select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = '$cpid' group by proj order by stat_date desc The order by causes the filesort and I can't find an easy way around it. mysql explain select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G *** 1. row *** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,two,cpid,team key: cpid key_len: 35 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: stats.a.proj rows: 1 Extra: Using where 2 rows in set (0.00 sec) I could just remove the order by altogether and perform the sort in php afterwards I guess but any other ideas? Thanks Phil -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Distributed Computing stats http://stats.free-dc.org -- Distributed Computing stats http://stats.free-dc.org
Re: Trying to remove a filesort.
Its not advisiable...as this size will be allocated to all the session and cause system running out of memory. It should be set at session and in my.cnf it should be around 2 MB. Please correct if i am wrong. regards anandkl On Fri, Sep 10, 2010 at 1:26 AM, Phil freedc@gmail.com wrote: It's in my.cnf. There is 12Gb in the database server and I watch it fairly carefully and have not gone into swap yet in the past few years. On Thu, Sep 9, 2010 at 3:43 PM, Ananda Kumar anan...@gmail.com wrote: have u set sort_buffer_size at session level or in my.cnf. Setting high value in my.cnf, will cause mysql to run out off MEMORY and paging will happen regards anandkl On Fri, Sep 10, 2010 at 1:10 AM, Phil freedc@gmail.com wrote: Even prior to the group by it's still not likely to ever be more than 200 or so maximum. I have the sort_buffer_size at 256Mb so I don't believe it's that either :( On Thu, Sep 9, 2010 at 3:16 PM, Michael Dykman mdyk...@gmail.com wrote: How many rows before the GROUP BY? Group by is, in effect a sorting process.. perhaps that contains enough data to justify going to disk. What is the value of the variable sort_buffer_size? show variables like '%sort%'; - md On Thu, Sep 9, 2010 at 3:04 PM, Phil freedc@gmail.com wrote: On average it would be between 10 and 40, certainly no more than 100. On Thu, Sep 9, 2010 at 2:57 PM, Michael Dykman mdyk...@gmail.com wrote: The filesort is probably necessary because of the number of rows in the result set to be ordered. How many rows do you get out of this query? - michael dykman On Thu, Sep 9, 2010 at 1:53 PM, Phil freedc@gmail.com wrote: I wonder if anyone could help with a query which I've been unable to prevent from using a filesort. Might be something obvious I'm overlooking! I have a table which tracks milestones in distributed computing projects Create Table: CREATE TABLE `boinc_milestone` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL, `stat_date` date NOT NULL DEFAULT '-00-00', `milestone_type` char(1) NOT NULL DEFAULT '0', `milestone` double NOT NULL DEFAULT '0', `cpid` varchar(32) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), KEY `two` (`proj`,`stat_date`,`id`,`milestone`), KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 These are added to on a daily basis as users pass the various credit milestones so for instance you can end up with rows for 1000,5000,1,5,100 etc on different dates as time goes on. Now on one page for display I want to show the latest milestone for each project for a particular cpid. The query I use is as follows: select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = '$cpid' group by proj order by stat_date desc The order by causes the filesort and I can't find an easy way around it. mysql explain select a.proj,a.id ,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G *** 1. row *** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,two,cpid,team key: cpid key_len: 35 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: stats.a.proj rows: 1 Extra: Using where 2 rows in set (0.00 sec) I could just remove the order by altogether and perform the sort in php afterwards I guess but any other ideas? Thanks Phil -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Distributed Computing stats http://stats.free-dc.org -- Distributed Computing stats
Re: Trying to remove a filesort.
You make an excellent point. If there are a lot of connections to that server, many sort buffers may be in use and can squeeze ram out of the rest of the system. 2M is a pretty good choice. - md On Thu, Sep 9, 2010 at 4:08 PM, Ananda Kumar anan...@gmail.com wrote: Its not advisiable...as this size will be allocated to all the session and cause system running out of memory. It should be set at session and in my.cnf it should be around 2 MB. Please correct if i am wrong. regards anandkl On Fri, Sep 10, 2010 at 1:26 AM, Phil freedc@gmail.com wrote: It's in my.cnf. There is 12Gb in the database server and I watch it fairly carefully and have not gone into swap yet in the past few years. On Thu, Sep 9, 2010 at 3:43 PM, Ananda Kumar anan...@gmail.com wrote: have u set sort_buffer_size at session level or in my.cnf. Setting high value in my.cnf, will cause mysql to run out off MEMORY and paging will happen regards anandkl On Fri, Sep 10, 2010 at 1:10 AM, Phil freedc@gmail.com wrote: Even prior to the group by it's still not likely to ever be more than 200 or so maximum. I have the sort_buffer_size at 256Mb so I don't believe it's that either :( On Thu, Sep 9, 2010 at 3:16 PM, Michael Dykman mdyk...@gmail.com wrote: How many rows before the GROUP BY? Group by is, in effect a sorting process.. perhaps that contains enough data to justify going to disk. What is the value of the variable sort_buffer_size? show variables like '%sort%'; - md On Thu, Sep 9, 2010 at 3:04 PM, Phil freedc@gmail.com wrote: On average it would be between 10 and 40, certainly no more than 100. On Thu, Sep 9, 2010 at 2:57 PM, Michael Dykman mdyk...@gmail.com wrote: The filesort is probably necessary because of the number of rows in the result set to be ordered. How many rows do you get out of this query? - michael dykman On Thu, Sep 9, 2010 at 1:53 PM, Phil freedc@gmail.com wrote: I wonder if anyone could help with a query which I've been unable to prevent from using a filesort. Might be something obvious I'm overlooking! I have a table which tracks milestones in distributed computing projects Create Table: CREATE TABLE `boinc_milestone` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL, `stat_date` date NOT NULL DEFAULT '-00-00', `milestone_type` char(1) NOT NULL DEFAULT '0', `milestone` double NOT NULL DEFAULT '0', `cpid` varchar(32) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), KEY `two` (`proj`,`stat_date`,`id`,`milestone`), KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 These are added to on a daily basis as users pass the various credit milestones so for instance you can end up with rows for 1000,5000,1,5,100 etc on different dates as time goes on. Now on one page for display I want to show the latest milestone for each project for a particular cpid. The query I use is as follows: select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = '$cpid' group by proj order by stat_date desc The order by causes the filesort and I can't find an easy way around it. mysql explain select a.proj,a.id ,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G *** 1. row *** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,two,cpid,team key: cpid key_len: 35 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: stats.a.proj rows: 1 Extra: Using where 2 rows in set (0.00 sec) I could just remove the order by altogether and perform the sort in php afterwards I guess but any other ideas? Thanks Phil -- Distributed Computing stats http://stats.free-dc.org -- - michael dykman - mdyk...@gmail.com May the Source be with you. --
hard disk crash: how to discover the db?
Hello This is a real nightmare. A Mac crashed, the hard disk could be saved. Right now it can't be booted. But I don't know precisely the version of the OS (most likely Mac X 10.4), nor the version of mysql (most likely 5.3.x) and to make things worse I did not generate a dump. So is there any change to recover the db? Could I just try to copy the relevant directory which contains the db say to a linux machine and hope for the best? Any advice is strongly appreciated since a wiki is in that db. regards Uwe Brauer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: hard disk crash: how to discover the db?
Try using the failed hdd as a slave in a Linux machine. You might find that the hdd won't boot to OS but may have enough in it to access the file system. - Reply message - From: ext Uwe Brauer o...@mat.ucm.es Date: Thu, Sep 9, 2010 21:31 Subject: hard disk crash: how to discover the db? To: mysql@lists.mysql.com mysql@lists.mysql.com Hello This is a real nightmare. A Mac crashed, the hard disk could be saved. Right now it can't be booted. But I don't know precisely the version of the OS (most likely Mac X 10.4), nor the version of mysql (most likely 5.3.x) and to make things worse I did not generate a dump. So is there any change to recover the db? Could I just try to copy the relevant directory which contains the db say to a linux machine and hope for the best? Any advice is strongly appreciated since a wiki is in that db. regards Uwe Brauer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com
Re: hard disk crash: how to discover the db?
andrew.2.mo...@nokia.com wrote: Try using the failed hdd as a slave in a Linux machine. You might find that the hdd won't boot to OS but may have enough in it to access the file system. I have done that already and I have access. But I don't know how to extract the db (via dump) since the corresponding mysql server software is not running. how can i tell linux to use the mysql db of the Mac? Uwe Brauer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table design help
Hi all, Needing some advice on my tables design. Basically I am designing a soccer application, and have a table which contains player_bids (the values of which a player costs to be transferred between clubs). Can someone please offer some input on the best way in which I should design the financial table for each team to hold the teams current balance,historic financial information in terms of player wages and how best I should link it to the players_bids table, if I should at all ? Other information storage in the financial table would be things like prize money, gate receipts etc Thanks for any help. Cheers Neil
Re: hard disk crash: how to discover the db?
We do nightly backups at work just by taring the mysql directory. In my environment, that is /var/lib/mysql. Like this: service mysql stop cd /var/lib/mysql rm -rf * tar zxvf file.tar rm -rf ib_logfile* chown -R mysql.mysql service mysql start Something similar might work for you. Somebody with more MySQL expertise than me can probably help you customize the process to your environment. Good luck! G On 9 September 2010 17:08, Uwe Brauer o...@mat.ucm.es wrote: andrew.2.mo...@nokia.com wrote: Try using the failed hdd as a slave in a Linux machine. You might find that the hdd won't boot to OS but may have enough in it to access the file system. I have done that already and I have access. But I don't know how to extract the db (via dump) since the corresponding mysql server software is not running. how can i tell linux to use the mysql db of the Mac? Uwe Brauer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=george.g.lar...@gmail.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: Trying to remove a filesort.
When the explain output says Using filesort, it doesn't necessarily mean it is sorting on disk. It could still be sorting in memory and, thus, be reasonably fast. You might check the value of Created_tmp_disk_tables before and after your query to see for sure. -Travis -Original Message- From: Phil [mailto:freedc@gmail.com] Sent: Thursday, September 09, 2010 11:54 AM To: mysql Subject: Trying to remove a filesort. I wonder if anyone could help with a query which I've been unable to prevent from using a filesort. Might be something obvious I'm overlooking! I have a table which tracks milestones in distributed computing projects Create Table: CREATE TABLE `boinc_milestone` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL, `stat_date` date NOT NULL DEFAULT '-00-00', `milestone_type` char(1) NOT NULL DEFAULT '0', `milestone` double NOT NULL DEFAULT '0', `cpid` varchar(32) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), KEY `two` (`proj`,`stat_date`,`id`,`milestone`), KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 These are added to on a daily basis as users pass the various credit milestones so for instance you can end up with rows for 1000,5000,1,5,100 etc on different dates as time goes on. Now on one page for display I want to show the latest milestone for each project for a particular cpid. The query I use is as follows: select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = '$cpid' group by proj order by stat_date desc The order by causes the filesort and I can't find an easy way around it. mysql explain select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G *** 1. row *** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,two,cpid,team key: cpid key_len: 35 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: stats.a.proj rows: 1 Extra: Using where 2 rows in set (0.00 sec) I could just remove the order by altogether and perform the sort in php afterwards I guess but any other ideas? Thanks Phil -- Distributed Computing stats http://stats.free-dc.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to remove a filesort.
Thanks! I did not know that. Just tried it and indeed the Created_tmp_disk_tables did not increase, just the Created_tmp_tables increased by +1. Still not perfect, but it's better than I thought and at least in memory. And for the previous mails, I'm not sure why I ever had the sort_buffer_size that high, have reduced it now. On Thu, Sep 9, 2010 at 6:30 PM, Travis Ard travis_...@hotmail.com wrote: When the explain output says Using filesort, it doesn't necessarily mean it is sorting on disk. It could still be sorting in memory and, thus, be reasonably fast. You might check the value of Created_tmp_disk_tables before and after your query to see for sure. -Travis -Original Message- From: Phil [mailto:freedc@gmail.com] Sent: Thursday, September 09, 2010 11:54 AM To: mysql Subject: Trying to remove a filesort. I wonder if anyone could help with a query which I've been unable to prevent from using a filesort. Might be something obvious I'm overlooking! I have a table which tracks milestones in distributed computing projects Create Table: CREATE TABLE `boinc_milestone` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL, `stat_date` date NOT NULL DEFAULT '-00-00', `milestone_type` char(1) NOT NULL DEFAULT '0', `milestone` double NOT NULL DEFAULT '0', `cpid` varchar(32) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`proj`,`id`,`stat_date`,`milestone`), KEY `two` (`proj`,`stat_date`,`id`,`milestone`), KEY `cpid` (`cpid`,`proj`,`id`,`stat_date`,`milestone`), KEY `team` (`proj`,`team`,`id`,`stat_date`,`milestone`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 These are added to on a daily basis as users pass the various credit milestones so for instance you can end up with rows for 1000,5000,1,5,100 etc on different dates as time goes on. Now on one page for display I want to show the latest milestone for each project for a particular cpid. The query I use is as follows: select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = '$cpid' group by proj order by stat_date desc The order by causes the filesort and I can't find an easy way around it. mysql explain select a.proj,a.id,max(stat_date),max(a.milestone) as milestone,b.description from boinc_milestone a join boinc_projects b on a.proj = b.proj where cpid = 'XXX' group by proj order by stat_date\G *** 1. row *** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,two,cpid,team key: cpid key_len: 35 ref: const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: stats.a.proj rows: 1 Extra: Using where 2 rows in set (0.00 sec) I could just remove the order by altogether and perform the sort in php afterwards I guess but any other ideas? Thanks Phil -- Distributed Computing stats http://stats.free-dc.org -- Distributed Computing stats http://stats.free-dc.org
background IO threads for MyISAM?
Hello, Does anyone know if MyISAM supports background IO threads, or it is available only for InnoDB? Thanks, Jacek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
