RE: Changing the timezone
set time_zone='Europe/Kiev';
-Original Message-
From: Andre Polykanine [mailto:an...@oire.org]
Sent: Thursday, February 24, 2011 10:23 AM
To: mysql@lists.mysql.com
Subject: Changing the timezone
Hi everyone,
since I'm using the shared hosting, I can't change the default
timezone for MySql.
Question is: is there any query that I could launch in my connect.php
before other queries to make my timezone change?
For instance, I make a
mysql_query("SET CHARACTER_SET_DATABASE='utf8'") or die ("Unable to change
database charset: ".mysql_error());
and a
mysql_query("SET NAMES 'utf8'") or die ("Unable to set names: ".mysql_error());
Maybe is there a way to change my timezone to Europe/Kiev?
Thank you!
--
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: mysql TIME_WAIT
After a TCP connection has been closed, the state changes to TIME_WAIT. If I recall correctly, this is a way to prevent the same port from being immediately reused, and allow any duplicate packets from the previous connection that might still be floating around the network time to expire. In general, the presence of TIME_WAIT isn't something to be too concerned about. If you see a large number of these, it may be because your application is opening a new database connection for each web server request. You might be able to save yourself a bit of overhead by refactoring your application to use a database connection pool. -Travis -Original Message- From: Madan Thapa [mailto:madan.feedb...@gmail.com] Sent: Wednesday, February 16, 2011 3:35 PM To: mysql@lists.mysql.com Subject: mysql TIME_WAIT Hi, When i do a netstat and grep port 3306 , i can see lots of TIME_WAIT.Can you please advise , what might be the issue for so many TIME_WAIT ? Here are the logs and related files: My.CNF on DB Server ## ## [root@sql mysql]# cat /etc/my.cnf [mysqld] safe-show-database skip-locking skip-bdb port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 16M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 max_connections=5000 long_query_time = 20 log-slow-queries=/var/log/mysqld-slow.log [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [root@sql mysql]# NETSTAT on Web Server # I have changed acutal IP to WEBSERVERIP ( for the webserver ) and DBSERVERIP ( for the db server ip ) [root@web temphotos]# netstat -an | grep DBSERVERIP tcp0 0 WEBSERVERIP:57666 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:54315 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:53293 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53295 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53305 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53304 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53307 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53306 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53309 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53308 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53311 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53310 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53297 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53296 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53299 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53298 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53301 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53300 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53303 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53302 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53257 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53256 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53259 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53258 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53462 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:56250 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:50288 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:52286 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53116 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48573 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48574 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48575 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48568 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48569 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48570 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48571 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48300 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:47460 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:42983 DBSERVERIP:3306 EST
RE: Insert data in one table from Another Problem
Here's one option to "pivot" your results: select record_id ,max(soi) as soi ,max(heading) as heading ,max(description) as description ,max(relloc) as relloc from (select record_id ,if(field_name = 'SOI', field_value, '') as soi ,if(field_name = 'Heading', field_value, '') as heading ,if(field_name = 'Description', field_value, '') as description ,if(field_name = 'RelLoc', field_value, '') as relloc from user_news) s1 group by s1.record_id; -Original Message- From: Adarsh Sharma [mailto:adarsh.sha...@orkash.com] Sent: Wednesday, February 16, 2011 6:33 AM To: mysql@lists.mysql.com Subject: Insert data in one table from Another Problem Dear all, Today I am puzzled around a problem of inserting data into new table in new format. I have a table named *user_news* as : We have four rows with respect to each record_id. fore.g : I have listed main columns as *record_id field_name field_value* 572SOIMedia 572 Heading A senior Police official confirmed the presence of the stone quarry at Jafflong near the India-Bangladesh border 572Description HNLC runs a stone quarry in Jafflong District of Bangladesh. The outfit is also believed to own several betel nut plantations besides running other business in Bangladesh. 572 RelLoc Jafflong 578SOI Media 578 Heading Army Chief General V. K. Singh in Shillong said he was confident that the NDFB would come to the negotiating table if they are "handled properly" 578Description A school teacher was abducted by unidentified militants in Damas of East Garo Hills District. Army Chief General V. K. Singh in Shillong said he was confident . 578 RelLoc Garo Hills Similarly i have 1000 of rows. Now I create a new table as columns as : *record_id SOI heading Description RelLoc * and its values is as : * * 572 MediaA senior Police official confirmed the presence of the stone quarry at Jafflong near the India-Bangladesh border HNLC runs a stone quarry in Jafflong District of Bangladesh. Jafflong The values in *field_name* becomes four columns in the above table . and their values are the values of f*ield_value *column. The problem is that I want this data now in horizontal form and the data of four rows in one row. That is four rows in one table contributes a single row in *other *table. I try with procedures and cursors but fail to achieve the output. Is it possible in Mysql. Please guide me how to achieve this as I am stuck around it. Thanks & Best Regards Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: function to limit value of integer
Maybe some sort of logarithmic expression? select no_of_jobs, 10 * log(10, no_of_jobs) as job_weight from data; Of course, you'd have to tweak your coefficients to match the weighting system you want to use. -Travis -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Thursday, February 10, 2011 3:07 PM To: mysql@lists.mysql.com Subject: function to limit value of integer Is there a function that can limit the value of an integer in a MySQL query? I am trying to write a query that scores someones experience. However, number of jobs can become overweighted in the the query below. If someone has done 10 jobs vs. 1 that's a big difference in experience. But someone who's done 100 vs. someone who's done 50 the difference in experience is not so great as they are both near the top of the learning curve. In essence number of jobs becomes less and less of a contributor as it increases. Is there a way to limit it's value as it increases? SELECT years_srvd + no_of_jobs AS EXPERIENCE Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Table/select problem...
What columns do you have indexed on your event_log table? Can you post the output from SHOW CREATE TABLE? How long does the query run for? -Original Message- From: Andy Wallace [mailto:awall...@ihouseweb.com] Sent: Friday, February 04, 2011 10:29 AM To: mysql list Subject: Table/select problem... Greetings, all... I'm having an issue with a SELECT in our system. We have an event log table, with about 9 million rows in it. Inserts happen with some pretty high frequency, and these selects happen periodically. The event_log table is MyISAM, the rest of the tables are InnoDB. What's happening is that, periodically, when this select gets run, the whole damn thing locks up, and that pretty much shuts us down (since many things insert events into the table, and the table gets locked, so all the inserts hang). The statement and the explain for it are below. the enduser table has about a million rows in it, the event_type table 35 rows. The weird part is that, if I strip down the query to use no joins, the explain wants to return about 17,000 rows, but the query itself does the table locking thing. Should we perhaps change the event log to InnoDB to avoid table locking? Might the table itself be corrupt in some way? Any thoughts? thanks, andy EXPLAIN SELECT EL.event_log_id, EL.event_time, DATE_FORMAT(EL.event_time, '%c-%d-%Y %H:%i:%s') as 'time_formatted', ET.event_type_id, ET.description, EL.csr_name, EL.enduser_acnt, EL.csr_name, EL.referer, EL.mls_id, EL.mls_no, EL.ss_id, EL.details, E.fname, E.lname, E.email, E.phone1 FROM event_log EL JOIN event_type ET ON EL.event_type_id = ET.event_type_id JOIN enduser E ON EL.enduser_acnt = E.enduser_acnt WHERE EL.acnt = 'AR238156' AND EL.enduser_acnt != '' AND EL.event_type_id = 'EndUserLogin' AND event_time BETWEEN DATE_SUB(CURDATE(), INTERVAL '7' DAY) AND NOW() ORDER BY EL.event_time DESC *** 1. row *** id: 1 select_type: SIMPLE table: ET type: const possible_keys: PRIMARY key: PRIMARY key_len: 92 ref: const rows: 1 Extra: Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: EL type: index_merge possible_keys: agent,enduser,event_log_ibfk_1 key: agent,event_log_ibfk_1 key_len: 62,92 ref: NULL rows: 1757 Extra: Using intersect(agent,event_log_ibfk_1); Using where *** 3. row *** id: 1 select_type: SIMPLE table: E type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: idx_acnt.EL.enduser_acnt rows: 1 Extra: Using where 3 rows in set (0.00 sec) -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: SELECT Help
Something like this might help you find all of the times where your user_id switched to a different team_id: select team_id, user_id, min(last_changed) from (select home_team_id as team_id, home_user_id as user_id, last_changed from data union all select away_team_id as team_id, away_user_id as user_id, last_changed from data) s1 where s1.user_id = 3 group by team_id, user_id; -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, February 03, 2011 6:34 AM To: [MySQL] Subject: SELECT Help Hi, I've the following list of sample data, and need a SELECT statement to help me identify the point at which I've highlighted the data : Season, Competition, home_team_id, away_team_id, home_user_id, away_user_id, last_changed 1, 18, 11, 23, 3, 2010-11-14 17:18:17 1, 11, 8, 3, 82, 2010-11-14 18:37:44 1, 20, 11, 69, 3, 2010-11-17 23:07:49 1, 1, 11, 4, 3, 2010-11-18 19:00:26 1, 11, 1, 3, 4, 2010-11-18 19:00:42 1, 12, 11, 5, 3, 2010-11-19 22:49:49 1, 11, 14, 3, 19, 2010-11-23 21:38:19 1, 3, 11, 15, 3, 2010-11-25 22:08:23 1, 7, 11, 66, 3, 2010-11-28 02:38:15 2, 73, 60, 137, 3, 2010-12-08 00:22:30 2, 60, 73, 3, 137, 2010-12-08 00:22:35 2, 60, 37, 3, 112, 2010-12-09 20:05:44 2, 60, 65, 3, 158, 2010-12-12 21:45:14 2, 72, 60, 141, 3, 2010-12-13 15:38:25 2, 60, 68, 3, 87, 2010-12-13 16:08:08 2, 60, 45, 3, 8, 2010-12-13 22:34:40 2, 66, 60, 140, 3, 2010-12-14 22:10:42 2, 60, 71, 3, 142, 2010-12-16 19:48:46 2, 60, 64, 3, 30, 2010-12-19 16:41:21 2, 76, 60, 17, 3, 2010-12-19 19:17:04 2, 60, 76, 3, 17, 2010-12-20 00:40:56 *2, 11, 10, 3, 6, 2010-12-20 22:17:13* 2, 13, 11, 104, 3, 2010-12-21 00:36:37 2, 6, 11, 168, 3, 2010-12-29 20:20:52 2, 11, 18, 3, 97, 2010-12-29 20:41:07 2, 20, 11, 5, 3, 2010-12-30 21:24:58 2, 15, 11, 163, 3, 2010-12-30 21:46:39 2, 13, 11, 12, 3, 2010-12-30 22:33:15 Basically, I need to find the point in which the user for either home_user_id or away_user_id (in this instance 3) changed teams for home_team_id or away_team_id - if you understand what I mean ? Any ideas on how I can achieve this using MySQL ? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: map polygon data for popular us cities
You might check here: http://www.census.gov/geo/www/tiger/ -Travis -Original Message- From: viraj [mailto:kali...@gmail.com] Sent: Wednesday, February 02, 2011 3:31 AM To: mysql@lists.mysql.com Subject: map polygon data for popular us cities dear list, where can i find a list of map polygons for united states cities? any open database? or tool to obtain correct coordinates? i googled but couldn't find anything useful.. may be the terms i use are not the correct keywords :( any help or advice would be really appreciated. ~viraj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: Percent of match in condition
Maybe you could do something like the following with user variables (or prepared statements): set @sex = 1, @country = 120, @education = 0; select if(sex_id = @sex, 1, 0) + if(country_id = @country, 1, 0) + if(education_id = @education, 1, 0) as num_matches, sex_id, country_id, education_id from my_table where sex_id = @sex or country_id = @country or education_id = @education order by num_matches desc; -Travis -Original Message- From: Ali A.F.N [mailto:alio...@yahoo.com] Sent: Thursday, October 21, 2010 7:58 AM To: mysql@lists.mysql.com Subject: Percent of match in condition Hi All, I have a table with different fileds and almost the type of all them are smallint. I want to search on some fields with "OR" condition. I want to know is there possibility to know how many fileds matched exactly? then I can say how many percent match are available. select * from my_table where sex_id = 1 or country_id = 120 or education_id I mean if in my table there are some records with sex_id = 1 or country_id = 120 then I got 2 (2 match) then I can say 66% percent match. Thank you, ali -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: Primary key not unique on InnoDB table
You obviously know best how your application will query your database, and you may have already thought through your indexing strategy. If so, please disregard my comments. In my experience, it is not often you need separate indexes on most or all the columns in a table (excepting very narrow tables, perhaps), so I would think about how you anticipate the database might use each of these indexes. Even though you may have multiple indexes available, most of the time a database query optimizer will only choose one when deciding how to retrieve data for a query. So, if you have a column like first_name that is indexed, your database engine may never use this index unless you have a query like "select * from players_master where first_name = 'xyz'". If a column is part of your select list, but is not used as your WHERE clause expression or as part of a table join, indexing that column may not be a benefit. Running EXPLAIN will tell you whether or not the index you anticipate is actually being used for your query. -Travis From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Friday, October 15, 2010 3:43 AM To: [MySQL]; Travis Ard Subject: Fwd: Primary key not unique on InnoDB table Based on my reply below, do you recommend I continue to have these indexes ? -- Forwarded message -- From: Tompkins Neil Date: Wed, Oct 13, 2010 at 8:22 PM Subject: Re: Primary key not unique on InnoDB table To: Travis Ard Cc: "[MySQL]" Hi Travis, Thanks for your response. The fields which have indexes on, can be used on every other search, which is why I thought about creating them. Would you recommend against this ? Cheers Neil On Wed, Oct 13, 2010 at 6:48 PM, Travis Ard wrote: I couldn't help but notice you have individual indexes on nearly all the fields of your table. If you won't be using these fields exclusively as a join or filter condition in a query, you are unlikely to benefit from the extra indexes and, in fact, they could slow down your inserts and add to your storage requirements. -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, October 13, 2010 8:37 AM To: [MySQL] Subject: Primary key not unique on InnoDB table I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil
RE: Primary key not unique on InnoDB table
I couldn't help but notice you have individual indexes on nearly all the fields of your table. If you won't be using these fields exclusively as a join or filter condition in a query, you are unlikely to benefit from the extra indexes and, in fact, they could slow down your inserts and add to your storage requirements. -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, October 13, 2010 8:37 AM To: [MySQL] Subject: Primary key not unique on InnoDB table I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Can this query be done w/o adding another column?
Sorry, try changing the column mappings.ip to use the table aliases (m.ip and m2.ip). -Travis From: Paul Halliday [mailto:paul.halli...@gmail.com] Sent: Tuesday, October 12, 2010 11:37 AM To: Travis Ard Cc: mysql@lists.mysql.com Subject: Re: Can this query be done w/o adding another column? On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard wrote: You could join your mappings table twice, once on src_ip and again on dst_ip: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), m.cc as src_cc, INET_NTOA(dst_ip), m2.cc as dst_cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings m ON event.src_ip = mappings.ip INNER JOIN mappings m2 ON event.dst_ip = mappings.ip WHERE timestamp BETWEEN "2010-10-12 03:00:00" AND "2010-10-13 03:00:00" GROUP BY INET_NTOA(src_ip), m.cc, INET_NTOA(dst_ip), m2.cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 10; -Travis I get an error: ERROR 1054 (42S22): Unknown column 'mappings.ip' in 'on clause'
RE: Can this query be done w/o adding another column?
You could join your mappings table twice, once on src_ip and again on dst_ip: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), m.cc as src_cc, INET_NTOA(dst_ip), m2.cc as dst_cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings m ON event.src_ip = mappings.ip INNER JOIN mappings m2 ON event.dst_ip = mappings.ip WHERE timestamp BETWEEN "2010-10-12 03:00:00" AND "2010-10-13 03:00:00" GROUP BY INET_NTOA(src_ip), m.cc, INET_NTOA(dst_ip), m2.cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 10; -Travis -Original Message- From: Paul Halliday [mailto:paul.halli...@gmail.com] Sent: Tuesday, October 12, 2010 10:49 AM To: mysql@lists.mysql.com Subject: Can this query be done w/o adding another column? Geez, really taking advantage of the list today :). This one is a little more complicated, well, in my head anyway. Same tables as before, event and mappings. Mappings is just IP to Country info. I want to be able to join both a src and dst but the problem is the mappings table just has one ip column. My initial query looks like this: SELECT COUNT(signature) as count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), INET_NTOA(dst_ip), signature, signature_id, ip_proto FROM event WHERE timestamp BETWEEN "2010-10-12 00:00:00" AND "2010-10-13 00:00:00" GROUP BY src_ip, dst_ip, signature,ip_proto ORDER BY maxTime DESC; Which would return something like this: 2 | 2010-10-12 16:34:17 | 10.1.2.3 | 173.193.202.69 | ET P2P Vuze BT UDP Connection | 2010144 | 17 Now I want to add the country info into the mix. I have made it this far: SELECT COUNT(signature) as count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), mappings.cc, INET_NTOA(dst_ip), mappings.cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings ON event.src_ip = mappings.ip OR event.dst_ip = mappings.ip WHERE timestamp BETWEEN "2010-10-12 03:00:00" AND "2010-10-13 03:00:00" GROUP BY src_ip, dst_ip, signature,ip_proto ORDER BY maxTime DESC LIMIT 10; gives me: 2 | 2010-10-12 16:34:17 | 10.1.2.3 | US | 173.193.202.69 | US | ET P2P Vuze BT UDP Connection | 2010144 | 17 which obviously isn't right ;) but is close. I was just going to change the columns in the mappings table to have src_ip and dst_ip just duplicating the ip column but I have a nagging feeling that that probably isn't necessary. Thanks. -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.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: Can this be done with a single query?
You may get better performance from your query, and be able to make better
use of indexes if you use integer comparisons for your IP address
expressions instead of converting to strings with pattern matching. You
might consider something like the following:
SELECT DISTINCT(e.src_ip)
FROM event e
left outer join mappings m on mappings.src_ip = e.src_ip
WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00'
AND e.src_ip NOT BETWEEN 167772160 AND 184549375
AND e.src_ip NOT BETWEEN 2886729728 AND 2886795263
AND e.src_ip NOT BETWEEN 3232235520 AND 3232301055
AND m.src_ip IS NULL;
-Travis
-Original Message-
From: Paul Halliday [mailto:paul.halli...@gmail.com]
Sent: Tuesday, October 12, 2010 10:08 AM
To: Johnny Withers
Cc: mysql@lists.mysql.com
Subject: Re: Can this be done with a single query?
On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers
wrote:
> I would try:
>
> SELECT DISTINCT(e.src_ip)
> FROM event AS e
> LEFT JOIN mappings AS m ON e.src_ip=m.src_ip
> WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00'
> AND INET_NTOA(e.src_ip) NOT LIKE '10.%.%.%'
> AND INET_NTOA(e.src_ip) NOT LIKE '172.16.%.%'
> AND INET_NTOA(e.src_ip) NOT LIKE '192.168.%.%'
> AND m.src_ip IS NULL
> ;
>
> I would also modify the where clause to use:
>
> AND src_ip NOT BETWEEN INET_ATON('10.0.0.0') AND INET_ATON(10.255.255.255)
> AND src_ip NOT BETWEEN INET_ATON('172.16.0.0') AND
> INET_ATON(172.16.255.255)
> AND src_ip NOT BETWEEN INET_ATON('192.168.0.0') AND
> INET_ATON(192.168.255.255)
>
> instead of
>
> AND INET_NTOA(src_ip) NOT LIKE '10.%.%.%'
> AND INET_NTOA(src_ip) NOT LIKE '172.16.%.%'
> AND INET_NTOA(src_ip) NOT LIKE '192.168.%.%
>
> You should also ensure there is an index on src_ip in events and mappings
> tables.
>
> Using the INET_NTOA() function on the src_ip column will prevent index
> usage during the query.
>
This and the suggestion by Nathan both work.
Thanks for the help!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Constructing query to display item count based on increments of time
Maybe you could use something like the following to truncate your times to 10 minute increments before doing your GROUP BY and COUNT(): select concat(date_format(timestamp_col, '%Y-%m-%d %H:'), truncate(minute(timestamp_col) / 10, 0), '0') from your_table; -Travis -- From: "Pascual Strømsnæs" Sent: Wednesday, October 06, 2010 4:20 AM To: "[MySQL]" Subject: Constructing query to display item count based on increments of time Hi! How would one go about to construct a query that counts items within an increment or span of time, let's say increments of 10 minutes? Imagine a simple table where each row has a timestamp, and the query should return the count of items occurring within the timespan of a defined period. Say, 09:00: 14 09:10: 31 09:20: 25 09:30: 0 09:40: 12 etc. I have been able to get collections of item occurrence based on month and day by using GROUP BY together with a DATE_FORMAT( start_time, "%d %m %Y" ) eg. I can however not seem to be able to find the solution to grouping based on the minute increments in my example above. Any suggestions? -- Kind regards Pascual Strømsnæs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: Add "record number" to timestamped router data to facilitate cross join
I don't think I'd seriously consider the max() option, especially if you're expecting the table to grow large. Using a table to store the next record number (your last_used_record_nums table) is a technique that is fairly commonly used. You can increment and retrieve the value atomically if you use the LAST_INSERT_ID(): insert into last_used_record_nums(routerid, recordnum) values (, last_insert_id(1)) on duplicate key update recordnum = last_insert_id(recordnum + 1); then select last_insert_id(); to get the incremented value. You could probably wrap this in an INSERT trigger on your stats table so your application wouldn't need to worry about it. -Travis -Original Message- From: Jake Peavy [mailto:djstu...@gmail.com] Sent: Tuesday, October 05, 2010 10:34 AM To: MySQL General Mailing List Subject: Re: Add "record number" to timestamped router data to facilitate cross join On Fri, Oct 1, 2010 at 12:24 PM, Jake Peavy wrote: > All, > > I have a number of routers which report in with various stats > periodicially. This period is not a regular interval and can drift based on > other factors. Each router drifts independently. The stats the routers > provide need to be analyzed in terms of deltas between reports (rather than > the absolute number). Therefore I need to perform a cross join to compare > the rows for a given routerID (something like SELECT r1.timestamp, > r2.counter1-r1.counter1 FROM router_data as r1 JOIN router_data as r2 ON > (r2.recordNum = r1.recordNum + 1)) > > Here's an example of some raw data to give you an idea showing 3 records > each from 2 devices: > > +--+--+--+--+--+ > | routerID | timestamp| counter1 | counter2 | counter3 | > +--+--+--+--+--+ > |1 | 24/08/2010 10:36 | 40 | 55 | 70 | > |2 | 24/08/2010 10:51 | 31 | 79 | 29 | > |2 | 24/08/2010 12:19 | 94 | 61 | 64 | > |1 | 24/08/2010 12:41 | 4| 84 | 82 | > |1 | 24/08/2010 14:58 | 26 | 9| 62 | > |2 | 24/08/2010 14:51 | 36 | 75 | 31 | > +--+--+--+--+--+ > > My plan, to facilitate the cross join, was to add a per-device "record > number" like follows: > > > +--+--+---+--+--+--+ > | routerID | timestamp| recordNum | counter1 | counter2 | counter3 > | > > +--+--+---+--+--+--+ > |1 | 24/08/2010 10:36 | 1 | 40 | 55 | 70 > | > |2 | 24/08/2010 10:51 | 1 | 31 | 79 | 29 > | > |2 | 24/08/2010 12:19 | 2 | 94 | 61 | 64 > | > |1 | 24/08/2010 12:41 | 2 | 4| 84 | 82 > | > |1 | 24/08/2010 14:58 | 3 | 26 | 9| 62 > | > |2 | 24/08/2010 14:51 | 3 | 36 | 75 | 31 > | > > +--+--+---+--+--+--+ > > So here's my question, first, of course, is there a better way to perform > the cross join? If not, what's the easiest way to add and increment the > recordNum field? Can I do it directly in SQL somehow? Or do I need to do > it in my parser? If I do it in my parser, it runs periodically (as it > receives the reports) so I think it would need to figure out what record > number it assigned to which device last so it would know where to restart > the numbering. Should I hold that in the parser itself, or a separate table > (SELECT routerID,last_used_record_num FROM last_used_record_nums; then > parse, incrementing record num, then write the last ones back to that table) > or by querying the main table itself (SELECT routerID, MAX(recordNum) FROM > router_data GROUP BY routerID)? My only concern with the last approach is > that router_data is going to get very large and that query may get very > slow. > > TIA for any advice, > Hey all, Anyone have any thoughts/advice on the best way to manage this "record number"? What about a stored procedure? Or use of variables? TIA -- -jp If you're traveling in a time machine, and you're eating corn on the cob, I don't think it's going to affect things one way or the other. But here's the point I'm trying to make: Corn on the cob is good, isn't it? deepthoughtsbyjackhandey.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: multiple aliases
I don't believe it's possible to do what you're suggesting. At least, according to the second example on this page: http://dev.mysql.com/doc/refman/5.1/en/subquery-errors.html. -Travis -Original Message- From: Ramsey, Robert L [mailto:robert-ram...@uiowa.edu] Sent: Monday, September 27, 2010 7:10 AM To: [MySQL] Subject: multiple aliases I have a query with three subselects, all referencing the same table. I'd like to be able to combine them into one with aliases. Here's what I have now: select letter_codename, (select greek from letter_otherlanguages where letter ='A') as greek, (select french from letter_otherlanguages where letter ='A') as french, (select german from letter_otherlanguages where letter ='A') as german from intl_codes where letter='A'; I'd like to replace it with: select letter_codename, (select greek, french, german from letter_otherlanguages where letter ='A') as (greek, french, german) from intl_codes where letter='A'; Don't get hung up on the tables and structures, this is just a simple example. :) I want to use the three subselects because if I use a left join, the processing time goes from .4 to 5 seconds. Is this possible? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Update query problem
Try using the IS NULL operator instead of ! -Travis -Original Message- From: Andy Wallace [mailto:awall...@ihouseweb.com] Sent: Thursday, September 16, 2010 10:47 AM To: mysql@lists.mysql.com Subject: Update query problem So I'm having a problem with an update query. I have three tables: Table: A Columns: acnt, name, company, email, domain Table: AM Columns: acnt, m_id Table: M Columns: m_id, name, company, email, domain and I want to conditionally update the columns in one to values from the other. i.e., I want to put the value of A.name into M.name, but only if M.name is currently NULL, AND A.name has a usable value (not an empty string). This is what I came up with, but it doesn't work - it only replaces the values where the column in M is not null. update A join AM on A.acnt = AM.acnt joinM on AM.m_id = M.m_id SET M.name= IF( (!M.nameAND A.name != ''),A.name,M.name), M.company = IF( (!M.company AND A.company != ''), A.company, M.company), M.email = IF( (!M.email AND A.email != ''), A.email, M.email), M.domain = IF( (!M.domain AND A.domain != ''), A.domain, M.domain) Any thoughts? THanks, andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: extract text from table to file, and recover damage.
You could try SELECT ... INTO OUTFILE ... Also, if you just want to look at the data a page at a time, try setting your pager variable to your favorite pager program. mysql> pager /usr/bin/less and unset it with \n mysql> \n -Travis -- From: "Uwe Brauer" Sent: Wednesday, September 15, 2010 2:00 PM To: Subject: extract text from table to file, and recover damage. Hello I am still fighting with the crashed hard disk and its db. I had a look at the tables mysql -u wikiuser -p maqwiki and then select * from searchindex (This is the only table which seems to have useful information.) However the information is written so fast on the screen that it is impossible to read, I also can scroll since a lot of --- Lines are produced. - how can I write the result of the above command to file? The obvious > does not work. I think I see stuff like anu8c3a1lisis which makes me thing the table is corrupted, of course it could be also the UTF8 representation of the word análisis. Anyhow I run a mysqlcheck -u wikiuser -p maqwiki searchindex And got: maqwiki.searchindexOK Is this proof enough that the table is ok? Uwe Brauer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: 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 Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: "[MySQL]" 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
RE: Conditional join of tow tables
Does this work? select * from t1 join t2 on (t1.datum = t2.sdat or dayname(t1.datum) = t2.tag); -Travis -Original Message- From: mysql [mailto:my...@ayni.com] Sent: Tuesday, September 07, 2010 1:43 AM To: mysql@lists.mysql.com Subject: Conditional join of tow tables Hi listers mysql> show global variables like "version"; +---++ | Variable_name | Value | +---++ | version | 5.1.46 | +---++ 1 row in set (0.02 sec) mysql> Following problem: Two tables which must be joined differently depending on the contents of the second table, the first table esentially contains a date field named datum. the second table is as follows: mysql> describe schulung; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | sdat | date | YES | | NULL|| | tag | text | YES | MUL | NULL|| | szeit| time | YES | | NULL|| | speziell | text | YES | | NULL|| | id | int(10) unsigned | NO | PRI | NULL| auto_increment | +--+--+--+-+-++ 5 rows in set (0.00 sec) mysql> Now, if the second table in the sdat field contains a value which is equivalent to the datum field in the first table (datum = sdat), then this join must be taken and nothing else. Otherwise the more general join via the tag field must be taken (dayname(datum) = tag). I tried to program this using not exists in the on clause of a join inner join schulung on (if not exists (select sdat from schulung where sdat = datum) then (datum = sdat)) else dayname(datum) = tag) but I got an ERROR 1064 near 'not exists (select sdat ' Probably, I have to re-structure the entire statement to an other form using other constructs? Has anyone had similar problems? How did you solve it then? Thank you very much. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: Performance problems on MySQL
Have you considered adding a secondary index on the units column for your
delete queries?
DELETE FROM clientinfo WHERE units='155618918';
-Original Message-
From: Alexandre Vieira [mailto:nul...@gmail.com]
Sent: Thursday, September 02, 2010 8:46 AM
To: John Daisley; joh...@pixelated.net
Cc: mysql@lists.mysql.com
Subject: Performance problems on MySQL
John, Johnny,
Thanks for the prompt answer.
mysql> SHOW CREATE TABLE clientinfo;
++--
+
| Table | Create
Table
|
++--
+
| clientinfo | CREATE TABLE `clientinfo` (
`userid` varchar(21) NOT NULL default '',
`units` float default NULL,
`date_last_query` datetime default NULL,
`last_acc` int(10) unsigned default NULL,
`date_last_units` datetime default NULL,
`notification` int(10) unsigned NOT NULL default '0',
`package` char(1) default NULL,
`user_type` varchar(5) default NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
++--
+
1 row in set (0.00 sec)
mysql> SHOW INDEX FROM clientinfo;
+++--+--+-+-
--+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+-
--+-+--++--++-+
| clientinfo | 0 | PRIMARY |1 | userid |
A | 460056 | NULL | NULL | | BTREE | |
+++--+--+-+-
--+-+--++--++-+
1 row in set (0.00 sec)
SELECT * FROM clientinfo WHERE userid='182106617';
UPDATE clientinfo SET
units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units
=now(),notification=0
WHERE userid='152633876';
INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1,
date_last_units=now(), notification=0, package='D', user_type='PRE';
DELETE FROM clientinfo WHERE units='155618918';
There are no other type of queries.
We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
We also run some other applications in the server, but nothing that consumes
all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
idle CPU time at any time.
TIA
BR
Alex
On Thu, Sep 2, 2010 at 1:52 PM, John Daisley
wrote:
> What is the hardware spec? Anything else running on the box?
>
> Why are you replicating but not making use of the slave?
>
> Can you post the output of SHOW CREATE TABLE?
>
> Regards
> John
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Nested join query?
I think you could do away with your right outer join of table B (which will include all rows from B whether or not they match to C), since you are explicitly filtering for C.State like 'Yes'. The intermediate result doesn't necessarily need to be stored in a temporary table. You can include multiple tables in a single query by specifying each table and the join condition. Also, If you just want a distinct list of values, you could use the "DISTINCT" clause as opposed to grouping. Something like the following query should produce the results you're looking for: select distinct a.val from tablec c inner join tableb b on b.id = c.id inner join tablea a on a.num = b.num where c.state = 'Yes'; -Travis -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: Monday, August 30, 2010 4:39 PM To: MySql Subject: Nested join query? Hello everyone. I'm trying to perform a query that acts on 3 tables at once. I'm thinking I need to produce a set of joins between all three tables to get the results that I want, but am not sure how to go about it with nesting or maybe there's even a better way. I need to check TableC for cases where the State is set to 'Yes' and then find the Num fields in TableB that correspond to the matching IDs between the two. I currently have a query that performs a right join on the two that will give me the results. The problem is that I now have this single column table (TableBC listed below) from the first query that I then need to perform a query on TableA to find the matching records. I'd like to make this into a single query but am not sure the proper way to combine them all or how to perform a join on the values in this temporary table that I'm using. TableA Val Num 1 2 2 3 3 3 4 4 5 4 6 7 7 3 TableB NumID 1 1 2 2 3 1 4 2 5 1 6 1 7 4 8 3 9 5 TableC ID State 1No 2Yes 3No 4Yes 5No Currently to get the single column list from TableB and TableC that I want to use to query TableA, I'm using: SELECT `TableB`.`Num` FROM `TableC` RIGHT JOIN `TableB` ON `TableC`.`ID` = `TableB`.`ID` WHERE (`TableC`.`State` LIKE 'Yes') GROUP BY `TableB`.`Num` ORDER BY `TableB`.`ID` It is possible to have multiple instances of Num in TableB, that is why I'm also doing some groupings. In this simplified example, the result I get from this query is: TableBC Num 2 4 7 And the preferred result once I add in TableA would be: TableABC Val 1 4 5 6 So I believe the problem is now is how to insert or merge this into a query that will look for the results in TableA that I'm really interested in. It looks to be similar to the query I've already performed, but I'm not sure how to perform a join like this since I'm referencing a column that only exists in this temporary table that is being built and I don't want to accidentally reference the fields in TableB or TableC. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: Calculating table standings
I don't think there's anything inherently wrong with the way you've designed your table to store your match data. I don't have experience designing these kinds of applications, so maybe some others might have better advice for you. If you find your reporting is too slow or it is too awkward to query this table twice and union the results, then you might want to add a summary table. -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, August 25, 2010 2:06 PM To: Travis Ard Cc: [MySQL] Subject: Re: Calculating table standings Travis Do you think it would be better if I stored the information in a separate table, rather than using unions etc - to make the searching, counting etc easier ? Or is this method a standard way of dealing with this sort of data. Cheers Neil On Tue, Aug 24, 2010 at 5:43 PM, Travis Ard wrote: > I think your match table has all the information necessary to display the > results you want. Since each record contains data for two teams (home and > away), you'd probably need to select each separately and union the results > together before summarizing. Your query might look something like the > following: > > select >seasons_id >,team_id >,count(*) as games_played >,sum(home) as home_games_played >,sum(away) as away_games_played >,sum(won_home) as won_home >,sum(draw_home) as draw_home >,sum(lost_home) as lost_home >,sum(scored_home) as scored_home >,sum(conceded_home) as conceded_home >,sum(won_away) as won_away >,sum(draw_away) as draw_away >,sum(lost_away) as lost_away >,sum(scored_away) as scored_away >,sum(conceded_away) as conceded_away > from > (select >seasons_id >,home_team_id as team_id >,1 as home >,0 as away >,if(home_goals > away_goals, 1, 0) as won_home >,if(home_goals = away_goals, 1, 0) as draw_home >,if(home_goals < away_goals, 1, 0) as lost_home >,home_goals as scored_home >,away_goals as conceded_home >,0 as won_away >,0 as draw_away >,0 as lost_away >,0 as scored_away >,0 as conceded_away > from matches > union all > select >seasons_id >,away_team_id as team_id >,0 as home >,1 as away >,0 as won_home >,0 as draw_home >,0 as lost_home >,0 as scored_home >,0 as conceded_home >,if(away_goals > home_goals, 1, 0) as won_away >,if(away_goals = home_goals, 1, 0) as draw_away >,if(away_goals < home_goals, 1, 0) as lost_away >,away_goals as scored_away >,home_goals as conceded_away > from matches) s1 > group by seasons_id, team_id; > > -Original Message- > From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > Sent: Tuesday, August 24, 2010 7:45 AM > To: [MySQL] > Subject: Calculating table standings > > Hi, > > I wondered if anyone can offer me some help with regards the following > issue > I'm having. Basically, I've the following table structure containing rows > of results between two football teams. The fields are > > match_id > seasons_id > week_number > home_team_id > away_team_id > home_goals > away_goals > > Based on the above information, I'm wanting to generate a league table > listing showing > > games_played > won_home > draw_home > lost_home > scored_home > conceded_home > won_away > draw_away > lost_away > scored_away > conceded_away > > Finally I also want a tally for the number of points e.g 3 points for win, > 1 > point for a draw. Do you think this is possible with the basic table I > have, or should I consider putting the result data in a leagues table > working out the fields I have listed above, and then just calculating it > and > display it ? > > Thanks for any advice. > > Cheers > Neil > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Calculating table standings
I think your match table has all the information necessary to display the results you want. Since each record contains data for two teams (home and away), you'd probably need to select each separately and union the results together before summarizing. Your query might look something like the following: select seasons_id ,team_id ,count(*) as games_played ,sum(home) as home_games_played ,sum(away) as away_games_played ,sum(won_home) as won_home ,sum(draw_home) as draw_home ,sum(lost_home) as lost_home ,sum(scored_home) as scored_home ,sum(conceded_home) as conceded_home ,sum(won_away) as won_away ,sum(draw_away) as draw_away ,sum(lost_away) as lost_away ,sum(scored_away) as scored_away ,sum(conceded_away) as conceded_away from (select seasons_id ,home_team_id as team_id ,1 as home ,0 as away ,if(home_goals > away_goals, 1, 0) as won_home ,if(home_goals = away_goals, 1, 0) as draw_home ,if(home_goals < away_goals, 1, 0) as lost_home ,home_goals as scored_home ,away_goals as conceded_home ,0 as won_away ,0 as draw_away ,0 as lost_away ,0 as scored_away ,0 as conceded_away from matches union all select seasons_id ,away_team_id as team_id ,0 as home ,1 as away ,0 as won_home ,0 as draw_home ,0 as lost_home ,0 as scored_home ,0 as conceded_home ,if(away_goals > home_goals, 1, 0) as won_away ,if(away_goals = home_goals, 1, 0) as draw_away ,if(away_goals < home_goals, 1, 0) as lost_away ,away_goals as scored_away ,home_goals as conceded_away from matches) s1 group by seasons_id, team_id; -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 24, 2010 7:45 AM To: [MySQL] Subject: Calculating table standings Hi, I wondered if anyone can offer me some help with regards the following issue I'm having. Basically, I've the following table structure containing rows of results between two football teams. The fields are match_id seasons_id week_number home_team_id away_team_id home_goals away_goals Based on the above information, I'm wanting to generate a league table listing showing games_played won_home draw_home lost_home scored_home conceded_home won_away draw_away lost_away scored_away conceded_away Finally I also want a tally for the number of points e.g 3 points for win, 1 point for a draw. Do you think this is possible with the basic table I have, or should I consider putting the result data in a leagues table working out the fields I have listed above, and then just calculating it and display it ? Thanks for any advice. Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Creating a dedicated reporting server for management?
You could try doing a multi-master configuration by setting up mysql proxy to receive changes from 1, 2, and 3 and replicate to 4. -Travis -Original Message- From: Nunzio Daveri [mailto:nunziodav...@yahoo.com] Sent: Monday, August 23, 2010 3:28 PM To: mysql@lists.mysql.com Subject: Creating a dedicated reporting server for management? Hello Gurus, I have a customer who wants to create a reporting server for his management team. He wants to take server 1,2,3 and move the 3 databases from all 3 servers to one server server 4 and then have the management team run all the reports from server 4 since there are tons and tons of joins. How can I accomplish this? I can't do replication since server 4 is going to be a slave AND a slave can only have one master so I am sitting here trying to figure out how to get this done? This is an ongoing transfer of data as the reports have to be in sync with the 3 master servers so replication would be perfect. Any help, docs and directions is most appreciated. Thanks In Advance :-) Nunzio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: query help
Something like this might work: insert into domains select a.accountid, reverse(a.domainid), a.mailname from domains a left outer join domains b on b.domainid = reverse(a.domainid) and b.accountid = a.accountid and b.mailname = a.mailname where b.domainid is null; -Travis -- From: "Steven Buehler" Sent: Friday, August 20, 2010 3:30 PM To: Subject: query help I am hoping that I can do this with one query, I have a table, "Domains" with 3 columns accountID, domainID, mailname what I am trying to do is find all accountID's for "domainID" of 12345 and see if a second row with "domainID" of 54321 exists for that "accountID,mailname". If it doesn't exist, I want it to insert another row with the same accountID and mailname, but with the second (54321) domainid. Any help would be appreciated. Thanks 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: Limit the size of a database. Rotate the log after this size
Well, it wouldn't exactly limit the size of your tables, but you may want to look into creating a partitioned table to store your data. You could define your partition ranges to store a single day's worth of data or whatever granularity works best for you. Then, when you need to remove older data, it will be very easy to simply drop the partition(s) you no longer need. -Travis -Original Message- From: Guillaume Blanc [mailto:guillaume.b.bl...@gmail.com] Sent: Friday, August 20, 2010 8:55 AM To: mysql@lists.mysql.com Subject: Limit the size of a database. Rotate the log after this size Hello everyone, I've actually a database (MySAM) which is growing very quickly (1,3Go/hour). I would like to limit the size of the database but with a log rotation after the size is reached. Do you know a way to do it ? I thought of maybe a script who would delete the oldest entry when it reach a certain size. But i don't know how to write it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Slow ALTER TABLE on 70M row InnoDB table
What are you using as your primary key on this table? Is an auto_increment field or something non-sequential? Do you have your secondary indexes in place while you load the table or are you explicitly disabling them and re-enabling them afterward? -Travis -Original Message- From: Xn Nooby [mailto:xno...@gmail.com] Sent: Wednesday, August 18, 2010 9:34 AM To: mysql@lists.mysql.com Subject: Slow ALTER TABLE on 70M row InnoDB table I have been trying to speed up an ALTER TABLE command that adds a column to a large InnoDB table of about 80M rows. I have found and tried many different methods, but they are all slow.I have tried both optimizing the ALTER TABLE command, and dumping and loading the table (in both SQL and CSV formats). The table size is about 10GB, and the combined index size is about 6GB. I am trying to understand why it is slow. I have read that dumping and loading in the CSV format should be the absolute fastest, and it does only take 20 minutes to dump the 70M rows. However, it takes the LOAD FILE command 13 hours to import the CSV file. My understanding of LOAD FILE was that it was already optimized to load the data, then build the indices afterwords. I don't understand why it takes so long. I have read that breaking a SQL dump in to "chunks" is also supposed to be fast, but later chunks insert more slowly than earlier chunks. This is with keys disabled, and other options disabled. Ideally I could stick with the ALTER TABLE command, and I have played around with a lot of the buffer settings. My understanding is, any enabled key indices need to fit in to RAM, and I have played around with a lot of those settings. Mainly I have increased the buffer size and log buffer size. When importing records, I see the "free buffers" slowly run-out, and the import speed drops off when the buffers are used up. The first few million rows import at up to 30k rows per second, but eventually it slows to a crawl. I have read a lot about this on the mysqlperformance blog. There is a lot of information on the web about this topic, but I am not always sure which parts are for ISAM and which apply to InnoDB. I have not experimented with ISAM, since my tables are InnoDB. This process is slow on a larger box, which belongs to someone else, and on my own desktop PC. Should I stick with trying to make ALTER TABLE work, or should I be trying to get LOAD FILE to work? Any suggestions on adding a column to a large table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: project/extract similar items type, inside a table field as if a field itself
Maybe something like this: select user_id ,max(if(meta_key = 'zip_code', meta_value, null)) as zip_code ,max(if(meta_key = 'first_name', meta_value, null)) as first_name ,max(if(meta_key = 'last_name', meta_value, null)) as last_name from wp_usermeta group by user_id; -Travis -Original Message- From: MadTh [mailto:madan.feedb...@gmail.com] Sent: Thursday, August 12, 2010 4:08 PM To: mysql@lists.mysql.com Subject: project/extract similar items type, inside a table field as if a field itself Hi, There is a mysql table ( wordpress) as following, called wp_usermeta, where field meta_key holds zip_code , first_name, last_name inside it ( should have been separate fields to extract data easily) mysql> desc wp_usermeta; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | umeta_id | bigint(20) unsigned | NO | PRI | NULL| auto_increment | | user_id| bigint(20) unsigned | NO | MUL | 0 || | meta_key | varchar(255)| YES | MUL | NULL|| | meta_value | longtext| YES | | NULL|| ++-+--+-+-++ 4 rows in set (0.00 sec) mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'zip_code' ORDER BY user_id limit 1,3; +--+-+--++ | umeta_id | user_id | meta_key | meta_value | +--+-+--++ | 278 | 15 | zip_code | 32501 | | 297 | 16 | zip_code | 32501 | | 316 | 17 | zip_code | 32504 | +--+-+--++ 3 rows in set (0.00 sec) mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'first_name' ORDER BY user_id limit 1,3; +--+-+++ | umeta_id | user_id | meta_key | meta_value | +--+-+++ | 280 | 16 | first_name | Jesxxdx| | 299 | 17 | first_name | maerer | | 318 | 18 | first_name | Liddd | +--+-+++ 3 rows in set (0.00 sec) mysql> SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE 'last_name' ORDER BY user_id limit 1,3; +--+-+---++ | umeta_id | user_id | meta_key | meta_value | +--+-+---++ | 281 | 16 | last_name | Oweccc | | 300 | 17 | last_name | magf | | 319 | 18 | last_name | Pedfs | +--+-+---++ 3 rows in set (0.01 sec) mysql> Is it possible to exctract each items, zip_code , first_name, last_name inside the field meta_key separately and list them as if each item is a field through a single mysql query. Else, it seems we will have to extract each file and then import that to a new table with a each of the field created inside that table. Result something like: first_name last_namezip_code JesxxdxOweccc 32501 maerer magf 32501 Liddd Pedfs 32504 Thakns -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Slow query using string operator
Can you create a second, indexed column in your feed_new temp table that
includes the title without the year appended? That might allow you to get
by with a single pass through the larger prod table and avoid reading rows
from the feed_new table.
-Travis
-Original Message-
From: Jerry Schwartz [mailto:je...@gii.co.jp]
Sent: Tuesday, August 10, 2010 3:39 PM
To: mysql@lists.mysql.com
Subject: Slow query using string operator
I'm running a set of queries that look like this:
===
SET @PUBID = (SELECT pub_id FROM pub WHERE pub_code = 'DC');
DROP TEMPORARY TABLE IF EXISTS feed_new;
CREATE TEMPORARY TABLE feed_new (
new_title VARCHAR(255), INDEX (new_title)
);
INSERT INTO feed_new
VALUES
('UK Investment Bonds 2010'),
('UK Protection 2010'),
('UK Personal Insurance Distribution 2010'),
('UK Private Medical Insurance 2010'),
...
('UK Private Motor Insurance 2010'),
('Wealth Management for Non-Resident Indians 2010'),
('Middle Eastern Cards Database 2010')
;
SELECT
feed_new.new_title AS `New Title FROM Feed`,
prod.prod_pub_prod_id AS `Lib Code FROM DB`,
prod.prod_title AS `Title FROM DB`,
prod.prod_num AS `Prod Num`,
prod.prod_published AS `Published FROM DB`
FROM feed_new JOIN prod
ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) =
LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5)
WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY feed_new.new_title;
With a relatively small number of rows in `feed_new`, this can take many
seconds. With 163 rows in `feed_new`, compared against 11234 eligible rows
in
prod, it took about 28 seconds. Here's what an EXPLAIN looks like:
*** 1. row ***
id: 1
select_type: SIMPLE
table: feed_new
type: index
possible_keys: NULL
key: PRIMARY
key_len: 767
ref: NULL
rows: 1
Extra: Using index
*** 2. row ***
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id,pub_id_2
key: pub_id
key_len: 48
ref: const
rows: 11040
Extra: Using where
=
prod.pub_id is an indexed VARCHAR(15).
If I remove the string functions, I don't get what I want -- but the
remaining
query runs in .05 seconds. Here's an EXPLAIN of that one:
===
us-gii >EXPLAIN
-> SELECT
-> feed_new.new_title AS `New Title FROM Feed`,
-> prod.prod_pub_prod_id AS `Lib Code FROM DB`,
-> prod.prod_title AS `Title FROM DB`,
-> prod.prod_num AS `Prod Num`,
-> prod.prod_published AS `Published FROM DB`
-> FROM feed_new JOIN prod
-> ON feed_new.new_title = prod.prod_title
-> WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0
-> ORDER BY feed_new.new_title\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: feed_new
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 767
ref: NULL
rows: 163
Extra: Using index
*** 2. row ***
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id,prod_title,pub_id_2,prod_title_fulltext
key: prod_title
key_len: 768
ref: giiexpr_db.feed_new.new_title
rows: 1
Extra: Using where
Obviously the string manipulation is keeping MySQL from using `prod_title`
as
a key, but I wouldn't have thought that using `pub_id` instead would be that
horrific.
Does anyone have any suggestions as to how to speed this business up? I
can't
get away without some string manipulation, because I'm looking for "near
matches" by ignoring the year at the end of the title.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: script of mysql
Sorry, I am not aware of a windows-specific version. You might check with the script author, or you may be able to get this to work on windows by installing cygwin and using a bash shell. -Travis > Date: Mon, 2 Aug 2010 12:20:57 +0100 > Subject: Re: script of mysql > From: pratikshadjayswa...@gmail.com > To: travis_...@hotmail.com > CC: mysql@lists.mysql.com > > Hi Travis, > > Thanks a lot for your help, > > Can i have a same scrip for windows OS. > > > On Sat, Jul 31, 2010 at 3:29 AM, Travis Ard wrote: > > > > > Something like this mysql-summary script might be useful: > > http://code.google.com/p/aspersa/wiki/mysql_summary > > > > -Travis > > > > > > > Date: Fri, 30 Jul 2010 12:19:07 +0100 > > > Subject: script of mysql > > > From: pratikshadjayswa...@gmail.com > > > To: mysql@lists.mysql.com > > > > > > Hi List, > > > > > > Can somebody please help me if they have a script using which we can get > > an > > > idea for installed mysql server, backup, created databases, indexes, > > tables, > > > engines, replication etc... > > > > > > I will appreciate your help in advance > > > > > > > > > Thanks > > > Pratiksha > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?unsub=pratikshadjayswa...@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: query results group/summed by interval
You could also pre-define your intervals in a subquery using UNION and join that to your original table like so: select ifnull(sum(calls), 0) as calls, n as queue_seconds from (select 0 as n union select 5 union select 10 union select 15) as step left join calls on calls.queue_seconds > (step.n - 5) and calls.queue_seconds <= step.n group by n; +---+---+ | calls | queue_seconds | +---+---+ | 250 | 0 | | 168 | 5 | | 268 | 10 | | 0 | 15 | +---+---+ 4 rows in set (0.00 sec) -Travis > Date: Sun, 1 Aug 2010 13:16:36 +0100 > From: nuno.tava...@dri.pt > To: mgai...@hotmail.com > CC: cuong.m...@vienthongso.com; ave...@yahoo-inc.com; mustafa...@gmail.com; > mysql@lists.mysql.com > Subject: RE: query results group/summed by interval > > Hi all, > > Aveeks solution should work if you have at least one call for each intervall. > It's the classical GROUP BY solution that only works on the available dataset. > Although it should work pretty well in the cited scenario, you will miss > intervals (from a "all intervals report" point of view) if indeed there are > intervals (of more than 5 minutes, in this example) when there were no calls > at > all. > > I had a somewhat similar problem (running the second scenario, though) and > this > is the solution I setup (this was a Data Warehouse and that's why you'll read > about partition pruning, dataset was dozens of Gigs): > > http://gpshumano.blogs.dri.pt/2009/09/28/finding-for-each-time-interval-how-many-records-are-ocurring-during-that-interval/ > > This might become handy if Ghulam understands the differences between my > scenario and his. > > Hope that helps, > -NT > > > > > Quoting Martin Gainty : > > > > > no that would give you the count for each second interval instead of using > > the interval variable 5 > > > > Aveeks floor: > > FLOOR(X) Returns the largest integer value not greater than X. > > > > 1st (seconds/5) interval example > > 5/5=1 > > floor(5/5) = 1 > > supplied value would truncate and give you the int not greater than X > > then multiply by 5 > > 1*5=5 > > is correct > > > > Aveeks sum function: > > SUM([DISTINCT] expr) > > Returns the sum of expr. If the return set has no rows, SUM() returns NULL. > > The DISTINCT keyword can be used in MySQL 5.0 to sum only the distinct > > values > > of expr. > > SUM() returns NULL if there were no matching rows. > > sum(calls) from calls group by 5 * floor(seconds/5) > > sum(calls) from calls group by 5 * floor(5/5) > > sum(calls) from class group by 5 * 1 > > sum(calls) from class group by 5 > > is correct > > > > 2nd(seconds/5) interval example > > 10/5=2 > > floor(10/5)=2 > > supplied value would truncate and give you the int not greater than X > > then multiply by 5 > > 2*5=10 > > is correct > > > > Aveeks sum function > > sum(calls) from calls group by 5 * floor(seconds/5) > > sum(calls) from calls group by 5 * floor(10/5) > > sum(calls) from class group by 5 * 2 > > sum(calls) from class group by 10 > > would be applicable only if the interval was 10 > > > > Aveek if your interval is 5 change: > > sum(calls) from calls group by 5 * floor(seconds/5) > > to > > sum(calls) from calls group by floor(seconds/5) > > > > Martin Gainty > > __ > > Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité > > > > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene > > Empfaenger > > sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte > > Weiterleitung > > oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich > > dem Austausch von Informationen und entfaltet keine rechtliche > > Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen > > wir keine Haftung fuer den Inhalt uebernehmen. > > Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le > > destinataire prévu, nous te demandons avec bonté que pour satisfaire > > informez > > l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci > > est interdite. Ce message sert à l'information seulement et n'aura pas > > n'importe quel effet légalement obligatoire. Étant donné que les email > > peuvent facilement être sujets à la manipulation, nous ne pouvons accepter > > aucune responsabilité pour le contenu fourni. > > > > > > > > > > > > > Date: Sat, 31 Jul 2010 10:31:43 +0700 > > > From: cuong.m...@vienthongso.com > > > To: ave...@yahoo-inc.com > > > CC: mustafa...@gmail.com; mysql@lists.mysql.com > > > Subject: Re: query results group/summed by interval > > > > > > Hi Aveek, > > > > > > I think Ghulam just want to count calls for each intervals > > > so the query should looks like this: > > > > > > select count(*) as total_calls, queue_seconds > > > from calls group by queue_seconds order by total_calls; > > > > > > > > > - Original Message - >
RE: script of mysql
Something like this mysql-summary script might be useful: http://code.google.com/p/aspersa/wiki/mysql_summary -Travis > Date: Fri, 30 Jul 2010 12:19:07 +0100 > Subject: script of mysql > From: pratikshadjayswa...@gmail.com > To: mysql@lists.mysql.com > > Hi List, > > Can somebody please help me if they have a script using which we can get an > idea for installed mysql server, backup, created databases, indexes, tables, > engines, replication etc... > > I will appreciate your help in advance > > > Thanks > Pratiksha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Variable Expansion Using MySQL Client and .sql Files
You could try it inside a "here" document:
mysql < Date: Thu, 29 Jul 2010 14:29:55 -0500
> From: todd_...@ssiresults.com
> To: mysql@lists.mysql.com
> Subject: Variable Expansion Using MySQL Client and .sql Files
>
> Hey all,
>
> I've found many packages that sit on top of MySQL for various clients.
> For the purposes of consistency I'd like to automate these installs.
> I've been directed towards using .sql files and they work great.
>
> The trouble I'm having now is that I would like to secure the
> installation but variable expansion isn't clicking for me.
>
> My setup is fairly straight-forward:
>
> I have a single installer script that calls all other scripts. This is
> how it works:
> 1) Source in all global environment variables from a working file:
> 1_GLOBAL_ENV.sh
> 2) execute script to create mysql db
> 3) secure mysql
>
> . /root/payload/1_GLOBAL_ENV.sh
> ...
> ###---
> ### Configure MySQL
> ###---
> set -x
> mysql -v < ${INST_SCRIPTS}/mysqld/secure_mysqld.sql
> ...
> mysql -v < ${INST_SCRIPTS}/mysqld/create_db.sql
> ...
> ---
>
> The create_db.sql should be similar to this:
> Concrete5, for example needs:
> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON
> concrete5.db TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT';
> ---
>
> The secure_mysqld.sql script is fairly simple as well:
>
> # Display the current user:
> select user();
>
> # Display all default accounts:
> SELECT User,Host,password FROM mysql.user;
>
> # Remove anonymous accounts:
> DELETE FROM mysql.user WHERE user = '';
>
> # Display all remaining accounts:
> SELECT User,Host,password FROM mysql.user;
>
> # Sync root passowrds:
> UPDATE mysql.user SET Password = PASSWORD('$PASSWD_PRIV_ROOT') WHERE
> User = 'root';
>
> exit
> ---
>
> It all works pretty well. Variable expansion is the problem. For now all
> of my other scripts substitute $PASSWD_PRIV_ROOT (from my
> 1_GLOBAL_ENV.sh) for the actual password. The *.sql scripts do not.
>
> If anyone can shed some light on this I would appreciate the help.
>
> --
> Thanks for the assist,
>
> Todd E Thomas
> C: 515.778.6913
> "It's a frail music knits the world together."
> -Robert Dana
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_...@hotmail.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: concatenate sql query with group by and having
With some databases such as MySQL, subqueries have to be explicitly named. For example select * from (select * from (select * from table) sub1) sub2; If not, you will see an error like: "ERROR 1248 (42000): Every derived table must have its own alias" If I understand your problem correctly, you are looking to limit your result set to only those records that have symbols with a single unique combination of chrom, and strand. If that's correct, something like the query below might work: select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat where geneName in -- returns all geneNames (symbols) with one unique combination of chrom and strand (select geneName from -- returns all unique combinations of symbol, chrom, and strand (select distinct geneName, chrom, strand from refFlat) sub1 group by geneName having count(*) = 1) group by refSeq having count(*) = 1; > Date: Wed, 28 Jul 2010 11:10:32 -0500 > Subject: concatenate sql query with group by and having > From: pengyu...@gmail.com > To: mysql@lists.mysql.com > > mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A > > I start mysql with the above command. Then I want to select the rows > from the result of the following query, provided that for any rows > that have the same symbol, chrom and strand should be the same > (basically, discard the rows that have the same symbols but different > chrom and strand). Could anybody show me how to do it? > > select geneName as symbol, name as refSeq, chrom, strand, txStart from > refFlat group by refSeq having count(*)=1; > > > I think that something like > > SELECT name FROM (SELECT name, type_id FROM (SELECT * FROM foods)); > > works for sqlite3 (in terms of syntax). But the following do not work > for mysql. Is this a difference between mysql and sqlite3? (I'm always > confused by the difference between different variants of SQL) > > select * from (select geneName as symbol, name as refSeq, chrom, > strand, txStart from refFlat group by refSeq having count(*)=1); > > -- > Regards, > Peng > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
