Perfomance of MYSQL
Hi, I am Using MySQL for GIS Data.I have to getting response time of each threads for retrival data by a JDBC Program with connection Polling . Here Initail Connection was 15. Maximum Connection was 50. My problem is that on 200 threads there response time is increasing abnormally ?. While 100 threads , 300 threads , 500 threads response time is fine. No. Threads 1 5 50 100 200 300 500 AVG Response time in milliseconds 11.8 1.180.92 4.480.710.814 Regards Rakesh The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to combine MYISAM to MERGE tables ...
- Original Message - From: mos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 18, 2008 6:48 PM Subject: Re: Best way to combine MYISAM to MERGE tables ... At 11:33 AM 2/18/2008, you wrote: Hi All, I am working with MYISAM tables split by year, like: data2003, data2004, data2005, data2006, data2007, data2008, all having the same definitions. To speed up the query process, I also defined MERGE tables, like: CREATE TABLE data20032004 ... ENGINE=MERGE UNION (data2003, data2004); CREATE TABLE data20032005 ... ENGINE=MERGE UNION (data2003, data2004, data2005); CREATE TABLE data20032006 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006); CREATE TABLE data20032007 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007); CREATE TABLE data20032008 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007, data2008); CREATE TABLE data20042005 ... ENGINE=MERGE UNION (data2004, data2005); etc. etc. CREATE TABLE data20072008 ... ENGINE=MERGE UNION (data2007, data2008); For example, if a user query applies to 2003..2006, my app selects the belonging MERGE table data20032006, excluding tables data2007 and data2008 for faster results. Is this assumption correct ? Or are there better ways of doing this ? I'm using: mySQL version 5.0.15-NT TIA, Cor Cor, If the date is indexed, I don't think you're going to have a problem referencing a merge table Data_All based on all the tables. That's what I do with 25 tables (15 million rows) and it is quite fast. It also of course uses the query cache so subsequent queries are instant. Mike Thanks Mike, In my app I can't to use Year as Indexed field, because my app has multi-column keys with (5) higher selectivity fields. Key on Year would be an option, if MySQL search engine could use N separate keys. By the way, I'm using about 120 million rows in 10 tables. I will keep your experience in mind, thanks. Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: AnySQL Maestro released (freeware cross-database tool)
Hi! SQL Maestro Group announces the release of AnySQL Maestro - a freeware, but powerful tool for all the database engines accessible via ODBC driver or OLE DB provider (Access, SQL Server, Firebird, Oracle, MySQL, PostgreSQL, SQLite, etc). http://www.sqlmaestro.com/products/anysql/maestro/ AnySQL Maestro has been successfully tested with the latest MySQL ODBC drivers (both 3.51 and 5.1), and all the latest MySQL server versions. Key features: - database object management - graphical database designer - visual query builder - data export and import - BLOB Viewer/Editor - and a lot of other things. Full press-release: http://www.sqlmaestro.com/news/company/4843/ Background information: SQL Maestro Group is engaged in developing complete database admin and management tools for MySQL, Oracle, MS SQL Server, PostgreSQL, SQLite, Firebird and MaxDB providing the highest performance, scalability and reliability to meet the requirements of today's database applications. Sincerely yours, SQL Maestro Group http://www.sqlmaestro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Call PHP file from MySQL
At 01:55 PM 2/19/2008, Mário Gamito wrote: Hi, Is it possible to call from within MySQL an external PHP script ? I've read MySQL Stored Procedure Programming from O'Reilly but found nothing :( How can I do this ? Any help would be appreciated. Warm Regards, Mário Gamito Mário, That would be the tail wagging the dog. MySQL knows only about MySQL and handles requests from the outside world, but doesn't launch any of its own. You would normally have a program (PHP in your case) making requests to MySQL and getting the results back. You could have PHP make a request to MYSQL and then take an appropriate action if you get a certain result, like executing a PHP routine. Can you explain what you are trying to do? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Limit
Looks like you're missing a comma after comm_id, before the @num := line? andy Santosh Killedar wrote: I am trying the following code on 4.1.2 and getting a syntax error that I could not figure out. It works fine on 5.x. Any suggestion/alternate CREATE TEMPORARY TABLE Temp (Node INT, comm_id INT, INDEX USING BTREE (comm_id)) ENGINE = MyISAM; INSERT INTO Temp SELECT recipient, id FROM `main_guestbook` ; set @Node := '', @num := 1; delete from main_guestbook where id in ( select comm_id from ( select Node, comm_id @num := if(@Node = Node, @num + 1, 1) as row_number, @Node := Node as dummy from Temp order by Node, comm_id desc ) as x where row_number 500 ) MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@num := if( @Node = Node , @num + 1 , 1 ) as row_number , @Node := Node as dumm' at line 1 - I have a MYsql table with following columns Node ID, Comment ID, Text, Date. Coment ID is primary key. For each Node ID there are one or more comment IDs (comments). There is a threshold (max_comments) that a node can have. How can I delete oldest comments associated with those nodes where this threshold is surpassed, such that the number of comments again will be below threshold for the node. The threshold is common for all nodes but the current number of comments is not (since those were added before the threshold policy established). Please note I want to delete older comments first, only for nodes above threshold thanks Santosh Killedar Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [EMAIL PROTECTED] locations
Hi Pierre - You're correct, mysqlhotcopy will no longer work when you switch to InnoDB. One option you could pursue is using mysqldump instead, which will write out full SQL files needed to restore your databases. It will write these to a filesystem. It is generally slower than mysqlhotcopy to take the backup, and slower to restore, but it is still possible to get a consistent backup snapshot this way. I've been using mysqldump for backups for years. See the mysqldump man pages or http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html specifically, the '--lock-all-tables' option will be of interest for a consistent db snapshot HTH. (copying the list to close the loop) Best, Dan On Feb 19, 2008 9:23 PM, P. Evans [EMAIL PROTECTED] wrote: Dan, we have single linux pc's at each location, so replication isnt feasable. The concern is really when we go to innodb, since mysqlhotcopy won't work then if I understand the documentation Pierre *Dan Buettner [EMAIL PROTECTED]* wrote: Are you currently dumping raw SQL? If so, how? One table at a time, or by obtaining a lock on all tables? If you're getting a lock on all tables now, I don't think anything would change if you switched to a transactional engine like InnoDB and did the same thing. The database is frozen for a period of time while the backups happen, which may be very quick if you don't have a lot of data. If you're not getting a lock on all tables now, then it's possible you're not getting a consistent snapshot of your data, and switching to InnoDB or another transactional engine won't fix that. I'd recommend aiming for a consistent backup. You know your operation better than I do, though - if there's truly *never* anything happening at the time you take your backups, then it's no big deal. The best strategy in many people's opinion when you need a consistent snapshot and can't spare the time to have the database frozen, is to set up a replica of your master server, and take your backups from the replica (slave). If you have a large number of servers this may be problematic from a cost/maintenance standpoint. If you can spare the time to have the database frozen, no big deal. -Dan On Fri, Feb 15, 2008 at 4:50 PM, P. Evans [EMAIL PROTECTED] wrote: Greetings, I've got a retail operation with mysql 5.0.22 on linux pc's across the country, and i need some input on setting up a backup strategy, preferrably without purchasing a package. We're currently using MyISAM, with the databases being dumped to a filesystem on a separate drive, in case the main drive goes down. However we will need to implement some kind of transactional engine in the near future, and we'd prefer not to take down the database to take a backup. Any thoughts ? Thanks Pierre - Looking for last minute shopping deals? Find them fast with Yahoo! Search. -- Never miss a thing. Make Yahoo your homepage.http://us.rd.yahoo.com/evt=51438/*http://www.yahoo.com/r/hs
Expanding a field leading to FK violations
I'm trying to change the definition of a field and getting 1025 errors when I try. Here's a simplified reproducible test case, on MySQL 5. CREATE TABLE `Users` ( `UserId` int(11) NOT NULL, PRIMARY KEY (`UserId`) ) ENGINE=InnoDB; CREATE TABLE `Actions` ( `ActionId` int(11) NOT NULL auto_increment, `FromUserId` int(11) NOT NULL, `ToUserId` int(11) NOT NULL, PRIMARY KEY (`ActionId`), KEY `FromUserId` (`FromUserId`), KEY `ToUserId` (`ToUserId`), CONSTRAINT `Actions_ibfk_1` FOREIGN KEY (`FromUserId`) REFERENCES `Users` (`UserId`), CONSTRAINT `Actions_ibfk_2` FOREIGN KEY (`ToUserId`) REFERENCES `Users` (`UserId`) ) ENGINE=InnoDB; mysql ALTER TABLE Users MODIFY UserId BIGINT NOT NULL; ERROR 1025 (HY000): Error on rename of './test/#sql-1d11_9c0fb' to './test/Users' (errno: 150) This is happening regardless of whether I first do SET FOREIGN_KEY_CHECKS=0; or not, and whether I define it as a PK in the ALTER TABLE statement. One way around it would be to drop the FKs first, but if there's a lot of data, I'm assuming that will be prohibitively slow. Especially since I'm just going to put it back up again. Anyone have ideas on how to get the ALTER TABLE statement working? Thanks, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Hi, and thankyou for trying to help me out! I've tried this and it does not work. Here are the problems : 1) If a user has never logged in he doesn't show the user in the list 2) It doesn't count if it is 0 it's not on the liste and if the user has logged in more than once the result is 1 (because of the group by ...). Thankyou Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How bad is adding BLOB column and index at the same time?
I am new to MySQL, and wonder if I have done something terribly stupid. I have an InnoDB table with 27 million rows. Without thinking very much, I issued the following command through the GUI administration tool: ALTER TABLE `wyky`.`externallinks` ADD COLUMN `el_p2` BLOB NOT NULL AFTER `el_index`, ADD INDEX `el_p2`(`el_p2`(150)) Of course, all the values in this column will be the same. The index will, if this ever completes, indicate that one value is associated with all 27E6 rows. My primary question is, will this take O(27E6) time or something worse (e.g., O(27E6 squared) time)? Of course my plan is to eventually put some interesting data in that new column. I do not really need the index until the data is there. This database is not being used on-line, this is just for study, so I do not mind large batch operations. I just don't want to be grossly stupid in my choice of batch operations. My second question is: if I have indeed done something grossly stupid, what is the best (if there is any at all!) way to interrupt it and proceed. I am running MySQL 5.0.51a-community on RHEL 4, storage on one local disk. Thanks! Mike
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Try SELECT a.username, a.first_name, a.last_name, SUM(case when b.username is null then 0 else 1 end) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; Donna Richard [EMAIL PROTECTED] 02/19/2008 05:29 PM To [EMAIL PROTECTED], mysql@lists.mysql.com cc Subject Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ? Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Actually, this works too: SELECT a.username, a.first_name, a.last_name, Count(b.username) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; __ Try SELECT a.username, a.first_name, a.last_name, SUM(case when b.username is null then 0 else 1 end) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; Donna Richard [EMAIL PROTECTED] 02/19/2008 05:29 PM To [EMAIL PROTECTED], mysql@lists.mysql.com cc Subject Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ? Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Try this one: SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 0) AS count FROM user_list a LEFT JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; The LEFT JOIN will ensure you still get a result row even if there are no matching rows in `login_table`. And the COALESCE will give you a value of 0 instead of NULL for the count, in that case. On Feb 19, 2008, at 5:29 PM, Richard wrote: Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Column level replication q?
We need to replicate a table to a third part. The information in the table is pretty much public, with the exception of one column. Is it possible to replicate all of the tables with the exception of one column? What I was thinking was to replication it to a 2nd machine that will limit it to the tables we care about and then expose that slave machine as a secondary master. We are looking to do something like this MasterA - SlaveA/MasterB - SlaveC MasterA tableA (our machine) field1 field2 field3 field4 SlaveA/MasterB (our machine) field1 field2 field4 SlaveC (their machine) field1 field2 field4 We know we can limit the tables which they can use (which is why we have introducted SlaveA/MasterB so they will only be able to pull the tables we make avaiable to them. Our current method is database dumps but this is become impracticle due to size. Replication in testing works for what we want to do, we just have a problem with a single field. Any ideas on how to make this work? Gary Wayne Smith
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Richard, 1) If a user has never logged in he doesn't show the user in the list 2) It doesn't count if it is 0 it's not on the liste and if the user has logged in more than once the result is 1 (because of the group by ...). Do you mean by #1 that you want to list all users whether they have logged in or not? #2 is less clear still; does it mean the query is to show a count of 0 for no logins and 1 for any positive number of logins? If so, try... SELECT a.username, a.first_name, a.last_name,COUNT(DISTINCT b.username) AS count FROM user_list a LEFT JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB Richard wrote: Hi, and thankyou for trying to help me out! I've tried this and it does not work. Here are the problems : 1) If a user has never logged in he doesn't show the user in the list 2) It doesn't count if it is 0 it's not on the liste and if the user has logged in more than once the result is 1 (because of the group by ...). Thankyou Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Call PHP file from MySQL
Hi, On Feb 19, 2008 2:55 PM, Mário Gamito [EMAIL PROTECTED] wrote: Hi, Is it possible to call from within MySQL an external PHP script ? I've read MySQL Stored Procedure Programming from O'Reilly but found nothing :( How can I do this ? You may be interested in this: http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Expanding a field leading to FK violations
Hi Wayne, On Feb 19, 2008 4:52 PM, Waynn Lue [EMAIL PROTECTED] wrote: I'm trying to change the definition of a field and getting 1025 errors when I try. Here's a simplified reproducible test case, on MySQL 5. CREATE TABLE `Users` ( `UserId` int(11) NOT NULL, PRIMARY KEY (`UserId`) ) ENGINE=InnoDB; CREATE TABLE `Actions` ( `ActionId` int(11) NOT NULL auto_increment, `FromUserId` int(11) NOT NULL, `ToUserId` int(11) NOT NULL, PRIMARY KEY (`ActionId`), KEY `FromUserId` (`FromUserId`), KEY `ToUserId` (`ToUserId`), CONSTRAINT `Actions_ibfk_1` FOREIGN KEY (`FromUserId`) REFERENCES `Users` (`UserId`), CONSTRAINT `Actions_ibfk_2` FOREIGN KEY (`ToUserId`) REFERENCES `Users` (`UserId`) ) ENGINE=InnoDB; mysql ALTER TABLE Users MODIFY UserId BIGINT NOT NULL; ERROR 1025 (HY000): Error on rename of './test/#sql-1d11_9c0fb' to './test/Users' (errno: 150) This is happening regardless of whether I first do SET FOREIGN_KEY_CHECKS=0; or not, and whether I define it as a PK in the ALTER TABLE statement. One way around it would be to drop the FKs first, but if there's a lot of data, I'm assuming that will be prohibitively slow. Especially since I'm just going to put it back up again. Anyone have ideas on how to get the ALTER TABLE statement working? Unfortunately you really can't do what you're trying to do without dropping the FK and then putting it back :-( Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How bad is adding BLOB column and index at the same time?
Hi, On Feb 19, 2008 5:20 PM, Mike Spreitzer [EMAIL PROTECTED] wrote: I am new to MySQL, and wonder if I have done something terribly stupid. I have an InnoDB table with 27 million rows. Without thinking very much, I issued the following command through the GUI administration tool: ALTER TABLE `wyky`.`externallinks` ADD COLUMN `el_p2` BLOB NOT NULL AFTER `el_index`, ADD INDEX `el_p2`(`el_p2`(150)) Of course, all the values in this column will be the same. The index will, if this ever completes, indicate that one value is associated with all 27E6 rows. My primary question is, will this take O(27E6) time or something worse (e.g., O(27E6 squared) time)? Of course my plan is to eventually put some interesting data in that new column. I do not really need the index until the data is there. This database is not being used on-line, this is just for study, so I do not mind large batch operations. I just don't want to be grossly stupid in my choice of batch operations. My second question is: if I have indeed done something grossly stupid, what is the best (if there is any at all!) way to interrupt it and proceed. If the table is bigger than memory, building the index will be very slow. If you interrupt it, it's just going to roll back everything it's done so far. So you have the choice of either letting it finish and then dropping the index, or killing it and letting it roll back. (You can't prevent the roll back, even if you restart). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Column level replication q?
Hi, On Feb 19, 2008 7:17 PM, Gary W. Smith [EMAIL PROTECTED] wrote: We need to replicate a table to a third part. The information in the table is pretty much public, with the exception of one column. Is it possible to replicate all of the tables with the exception of one column? What I was thinking was to replication it to a 2nd machine that will limit it to the tables we care about and then expose that slave machine as a secondary master. We are looking to do something like this MasterA - SlaveA/MasterB - SlaveC MasterA tableA (our machine) field1 field2 field3 field4 SlaveA/MasterB (our machine) field1 field2 field4 SlaveC (their machine) field1 field2 field4 We know we can limit the tables which they can use (which is why we have introducted SlaveA/MasterB so they will only be able to pull the tables we make avaiable to them. Our current method is database dumps but this is become impracticle due to size. Replication in testing works for what we want to do, we just have a problem with a single field. Any ideas on how to make this work? This isn't natively supported. You can hack it with replication to a table that has a trigger, which will then insert all but one column into another table, which you can replicate on to the final destination. But I'm scared of such hacks for anything that matters :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How bad is adding BLOB column and index at the same time?
Thanks, Baron. Yes, the table is bigger than memory. It took about 2.5 days to create the table, inserting about 7,000 rows at a time; this column and index addition has been running for about a day now. I notice you did not say it was terribly stupid to create this index before putting the final data in the new column. So I infer there is no big motivation to interrupt the operation I have going. Thanks, Mike Baron Schwartz [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/19/08 08:13 PM To Mike Spreitzer/Watson/[EMAIL PROTECTED] cc mysql@lists.mysql.com Subject Re: How bad is adding BLOB column and index at the same time? Hi, On Feb 19, 2008 5:20 PM, Mike Spreitzer [EMAIL PROTECTED] wrote: I am new to MySQL, and wonder if I have done something terribly stupid. I have an InnoDB table with 27 million rows. Without thinking very much, I issued the following command through the GUI administration tool: ALTER TABLE `wyky`.`externallinks` ADD COLUMN `el_p2` BLOB NOT NULL AFTER `el_index`, ADD INDEX `el_p2`(`el_p2`(150)) Of course, all the values in this column will be the same. The index will, if this ever completes, indicate that one value is associated with all 27E6 rows. My primary question is, will this take O(27E6) time or something worse (e.g., O(27E6 squared) time)? Of course my plan is to eventually put some interesting data in that new column. I do not really need the index until the data is there. This database is not being used on-line, this is just for study, so I do not mind large batch operations. I just don't want to be grossly stupid in my choice of batch operations. My second question is: if I have indeed done something grossly stupid, what is the best (if there is any at all!) way to interrupt it and proceed. If the table is bigger than memory, building the index will be very slow. If you interrupt it, it's just going to roll back everything it's done so far. So you have the choice of either letting it finish and then dropping the index, or killing it and letting it roll back. (You can't prevent the roll back, even if you restart).
RE: Column level replication q?
And that is a hack at best, but it does give me some ideas. I really only need two fields out of that table anyhow so I might actually migrate the columns that I care about into a new table and update the corresponding SQL statements that I use to update them (i.e. split the source data). From: [EMAIL PROTECTED] on behalf of Baron Schwartz Sent: Tue 2/19/2008 5:15 PM To: Gary W. Smith Cc: mysql@lists.mysql.com Subject: Re: Column level replication q? Hi, This isn't natively supported. You can hack it with replication to a table that has a trigger, which will then insert all but one column into another table, which you can replicate on to the final destination. But I'm scared of such hacks for anything that matters :-)
Re: Expanding a field leading to FK violations
Hm, ok. Then one more followup question, if dropping the FKs is necessary, is there any way to figure out how long such an operation will take? SHOW PROCESSLIST usually just has copying to tmp table or some such, does SHOW INNODB STATUS or any other commands give insight into how long it will take? Thanks again, Waynn On Feb 19, 2008 5:11 PM, Baron Schwartz [EMAIL PROTECTED] wrote: Hi Wayne, On Feb 19, 2008 4:52 PM, Waynn Lue [EMAIL PROTECTED] wrote: I'm trying to change the definition of a field and getting 1025 errors when I try. Here's a simplified reproducible test case, on MySQL 5. CREATE TABLE `Users` ( `UserId` int(11) NOT NULL, PRIMARY KEY (`UserId`) ) ENGINE=InnoDB; CREATE TABLE `Actions` ( `ActionId` int(11) NOT NULL auto_increment, `FromUserId` int(11) NOT NULL, `ToUserId` int(11) NOT NULL, PRIMARY KEY (`ActionId`), KEY `FromUserId` (`FromUserId`), KEY `ToUserId` (`ToUserId`), CONSTRAINT `Actions_ibfk_1` FOREIGN KEY (`FromUserId`) REFERENCES `Users` (`UserId`), CONSTRAINT `Actions_ibfk_2` FOREIGN KEY (`ToUserId`) REFERENCES `Users` (`UserId`) ) ENGINE=InnoDB; mysql ALTER TABLE Users MODIFY UserId BIGINT NOT NULL; ERROR 1025 (HY000): Error on rename of './test/#sql-1d11_9c0fb' to './test/Users' (errno: 150) This is happening regardless of whether I first do SET FOREIGN_KEY_CHECKS=0; or not, and whether I define it as a PK in the ALTER TABLE statement. One way around it would be to drop the FKs first, but if there's a lot of data, I'm assuming that will be prohibitively slow. Especially since I'm just going to put it back up again. Anyone have ideas on how to get the ALTER TABLE statement working? Unfortunately you really can't do what you're trying to do without dropping the FK and then putting it back :-( Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
