Re: Migration from 32-bit to 64-bit MySQL
learnt On Wed, Apr 30, 2008 at 5:18 AM, Mihail Manolov [EMAIL PROTECTED] wrote: Hi all, I feel obliged to report on my success with migration from 32bit to 64bit platform. Last Sunday I braced myself and migrated 3 servers (one master and two slaves) with approximately 100GB data each by simply rsyncing the data files. It took about 1 hour total downtime. Everything looks great so far. I ran lots of tests, especially on currency columns and all tests were successfull. It is fair to note that I don't have any FLOAT columns in my databases. I have mixed table environment (MyISAM and InnoDB tables). Running MySQL 4.1.24, Linux binaries. Let me know if you have any questions. Mihail On Apr 25, 2008, at 12:48 PM, Mihail Manolov wrote: I am in process of planning 32 to 64 migration as well. I googled the following, but it could be only relevant to a specific application: It should be noted that, when switching between 32bit and 64bit server using the same data-files, all the current major storage engines (with one exception) are architecture neutral, both in endian-ness and bit size. You should be able to copy a 64-bit or 32-bit DB either way, and even between platforms without problems for MyISAM, InnoDB and NDB. For other engines it doesn't matter (CSV, MEMORY, MERGE, BLACKHOLE and FEDERATED) either the engine doesn't have a disk storage format or the format they use is text based (CSV) or based on MyISAM (MERGE; and therefore not an issue). The only exception is Falcon, which is only available in MySQL 6.0. It is generally recommended from MySQL that a dump and reload of data for absolute compatibility for any engine and major migration. The googled link: http://wikis.sun.com/display/WebStack/MySQL64bitARC Any comments on this? Mihail On Apr 25, 2008, at 12:03 PM, Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: A question about index
On May 21, 2008, at 8:09 AM, Moon's Father wrote: Now I want to know which way you use to create index of a table. 1、ix_u (item_id,item_count) ix_item_count (item_count) This makes sense if you have queries which search item_id AND item_count, and queries which just search the column 'item_count'. 2、ix_u (item_id,item_count) ix_item_id (item_id) This is duplication of the indexing of the column 'item_id'. The server is able to use the index ix_u (item_id,item_count) to search on the column 'item_id'. Any reply is welcome. Thanks. -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about index
On May 21, 2008, at 8:46 AM, Paul McCullagh wrote: On May 21, 2008, at 8:09 AM, Moon's Father wrote: Now I want to know which way you use to create index of a table. 1、ix_u (item_id,item_count) ix_item_count (item_count) This makes sense if you have queries which search item_id AND item_count, and queries which just search the column 'item_count'. On second thoughts, this answer is not complete. If this is case then the index ix_u (item_count, item_id) would be the best choice. These indices: ix_u (item_id,item_count) ix_item_count (item_count) are best when you have the following types of queries: WHERE item_id AND item_count the server uses ix_u (item_id,item_count) WHERE item_id the server uses ix_u (item_id,item_count) WHERE item_count the server uses x_item_count (item_count) 2、ix_u (item_id,item_count) ix_item_id (item_id) This is duplication of the indexing of the column 'item_id'. The server is able to use the index ix_u (item_id,item_count) to search on the column 'item_id'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about index
Thanks for your reply very much. What I always use is the first way. But I also want to know if the following way is proper when I search item_id AND item_count and the column 'item_count'.? ix_item_id (item_id) ix_item_count (item_count) 2008/5/21 Paul McCullagh [EMAIL PROTECTED]: On May 21, 2008, at 8:09 AM, Moon's Father wrote: Now I want to know which way you use to create index of a table. 1、ix_u (item_id,item_count) ix_item_count (item_count) This makes sense if you have queries which search item_id AND item_count, and queries which just search the column 'item_count'. 2、ix_u (item_id,item_count) ix_item_id (item_id) This is duplication of the indexing of the column 'item_id'. The server is able to use the index ix_u (item_id,item_count) to search on the column 'item_id'. Any reply is welcome. Thanks. -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: A question about index
Thanks very much. 2008/5/21 Paul McCullagh [EMAIL PROTECTED]: On May 21, 2008, at 8:46 AM, Paul McCullagh wrote: On May 21, 2008, at 8:09 AM, Moon's Father wrote: Now I want to know which way you use to create index of a table. 1、ix_u (item_id,item_count) ix_item_count (item_count) This makes sense if you have queries which search item_id AND item_count, and queries which just search the column 'item_count'. On second thoughts, this answer is not complete. If this is case then the index ix_u (item_count, item_id) would be the best choice. These indices: ix_u (item_id,item_count) ix_item_count (item_count) are best when you have the following types of queries: WHERE item_id AND item_count the server uses ix_u (item_id,item_count) WHERE item_id the server uses ix_u (item_id,item_count) WHERE item_count the server uses x_item_count (item_count) 2、ix_u (item_id,item_count) ix_item_id (item_id) This is duplication of the indexing of the column 'item_id'. The server is able to use the index ix_u (item_id,item_count) to search on the column 'item_id'. -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: very simple but slow query
Thanks very much to all who suggest me to use a JOIN instead of the IN clause, which performances are very poor vs join ones, as I read in http://www.artfulsoftware.com/infotree/queries.php#568: Both the |IN()| and |EXISTS()| queries have to execute a table scan for each row in the table. Performance degrades as the square of the number of rows. The |JOIN| version builds its derived table on one table scan, and quickly picks off its resultset from that. My query now runs in less than 8 secs (the previous about 10minutes!!!) I never see I forum where so much people offer their help. Thank again very much to everyone! Carlo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.x Performance on FreeBSD AMD64 7-STABBLE Box?
Hi I am just wondering if anyone is running MySQL Database on a fairly busy Website running on FreeBSD AMD64 7-stabble with PHP+Apache? I am going to build a server for couple of websites having traffic 5 million per month... Any comments? -- Thanks! BR / vj
DESC index column
Hi all - Is there a good workaround for mysql's lack of 'DESC' functionality when creating an index? I'm looking at migrating an Oracle RAC database to mysql (InnoDB or Cluster - testing both at the moment), and the Oracle database uses a lot of multi-column indexes with some colums indexed in descending order. If I can't emulate the descending index colums somehow it's likely to seriously impact performance and possibly derail the prospect of migration - help! cheers Iain -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB tables but no FK constraints
Hi, When I add a reference to a non-existing row in the referenced table, I have no error: My table member_orders_items references members_orders, member_orders_item has a FK to a non existing PK in member_orders (since this one is empty), no error is generated. I can see in MySQL Administrator that both tables are InnoDB. Here is my table structure: DROP TABLE IF EXISTS `members_orders`; CREATE TABLE `members_orders` ( `id_order` int(10) unsigned NOT NULL auto_increment, `paid_date` datetime default NULL, `record_date` datetime NOT NULL, `total` decimal(7,2) unsigned NOT NULL, `total_partner` decimal(7,2) unsigned NOT NULL, `member_id` int(10) unsigned NOT NULL, `total_no_discount` decimal(7,2) unsigned default NULL, PRIMARY KEY (`id_order`), KEY `FK_MEMBER_ID_MEMBERS_ORDERS` (`member_id`), CONSTRAINT `FK_MEMBER_ID_MEMBERS_ORDERS` FOREIGN KEY (`member_id`) REFERENCES `members` (`id_member`) ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8; LOCK TABLES `members_orders` WRITE; UNLOCK TABLES; DROP TABLE IF EXISTS `members_orders_items`; CREATE TABLE `members_orders_items` ( `id_order_item` int(10) unsigned NOT NULL auto_increment, `qty` int(10) unsigned NOT NULL, `total` decimal(7,2) unsigned NOT NULL, `order_id` int(10) unsigned NOT NULL, `item_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id_order_item`), KEY `FK_ORDER_ID_MEMBERS_ORDERS_ITEMS` (`order_id`), KEY `FK_ITEM_ID_MEMBERS_ORDERS_ITEMS` (`item_id`), CONSTRAINT `FK_ITEM_ID_MEMBERS_ORDERS_ITEMS` FOREIGN KEY (`item_id`) REFERENCES `services_items` (`id_item`), CONSTRAINT `FK_ORDER_ID_MEMBERS_ORDERS_ITEMS` FOREIGN KEY (`order_id`) REFERENCES `members_orders` (`id_order`) ) ENGINE=InnoDB AUTO_INCREMENT=137 DEFAULT CHARSET=utf8; LOCK TABLES `members_orders_items` WRITE; INSERT INTO `members_orders_items` VALUES (137,750,'54.00',25,45); //-- Here should be an error ? UNLOCK TABLES; Thank you for any kind help !! Matt. -- View this message in context: http://www.nabble.com/InnoDB-tables-but-no-FK-constraints-tp17364156p17364156.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DESC index column
What I've done in the past is to create extra columns which contain the reverse of a number/date used previously in an index. So, for instance if it's a simple INT column (A) and you know the max would be 999 for example, create an extra column and populate that with (1000 - A) and use it as an ASC index. Same can be done with dates. Not always applicable, but it works and is fairly easy to implement. Phil On Tue, May 20, 2008 at 2:20 PM, Bof [EMAIL PROTECTED] wrote: Hi all - Is there a good workaround for mysql's lack of 'DESC' functionality when creating an index? I'm looking at migrating an Oracle RAC database to mysql (InnoDB or Cluster - testing both at the moment), and the Oracle database uses a lot of multi-column indexes with some colums indexed in descending order. If I can't emulate the descending index colums somehow it's likely to seriously impact performance and possibly derail the prospect of migration - help! cheers Iain -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Help build our city at http://free-dc.myminicity.com !
NOT [solved] Re: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5)
Hello mysql list I posted this problem to the list earlier this month: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5) I was then told to upgrade to the newest version, which I did and which seemed to solve the problem. Today, I got this in my log: 080521 14:18:22 [ERROR] Error reading packet from server: Out of memory (Needed 2848 bytes) ( server_errno=5) 080521 14:18:22 [ERROR] Stopping slave I/O thread due to out-of-memory error from master Now, not only does it report an error, it also stops the slave process instead of auto re-starting it as it did in version 5.0.45 (which I used before). So all slave machines do not replicate until I manually start the slave again. What can I do about this problem? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DESC index column
Hi Phil - Thanks for the suggestion. Might that involve possibly changing queries in the web application hitting the database so that it uses the new column, or would the indexing on the new column take care of speeding up the existing queries? cheers Iain -- --- Phil [EMAIL PROTECTED] wrote: What I've done in the past is to create extra columns which contain the reverse of a number/date used previously in an index. So, for instance if it's a simple INT column (A) and you know the max would be 999 for example, create an extra column and populate that with (1000 - A) and use it as an ASC index. Same can be done with dates. Not always applicable, but it works and is fairly easy to implement. Phil On Tue, May 20, 2008 at 2:20 PM, Bof [EMAIL PROTECTED] wrote: Hi all - Is there a good workaround for mysql's lack of 'DESC' functionality when creating an index? I'm looking at migrating an Oracle RAC database to mysql (InnoDB or Cluster - testing both at the moment), and the Oracle database uses a lot of multi-column indexes with some colums indexed in descending order. If I can't emulate the descending index colums somehow it's likely to seriously impact performance and possibly derail the prospect of migration - help! cheers Iain -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Help build our city at http://free-dc.myminicity.com ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DESC index column
yes, you'd have to alter the queries to use the new index. As I say it's very application dependent and does not always apply, but you can normally shoehorn any application to use it. Phil On Wed, May 21, 2008 at 9:22 AM, Bof [EMAIL PROTECTED] wrote: Hi Phil - Thanks for the suggestion. Might that involve possibly changing queries in the web application hitting the database so that it uses the new column, or would the indexing on the new column take care of speeding up the existing queries? cheers Iain -- --- Phil [EMAIL PROTECTED] wrote: What I've done in the past is to create extra columns which contain the reverse of a number/date used previously in an index. So, for instance if it's a simple INT column (A) and you know the max would be 999 for example, create an extra column and populate that with (1000 - A) and use it as an ASC index. Same can be done with dates. Not always applicable, but it works and is fairly easy to implement. Phil On Tue, May 20, 2008 at 2:20 PM, Bof [EMAIL PROTECTED] wrote: Hi all - Is there a good workaround for mysql's lack of 'DESC' functionality when creating an index? I'm looking at migrating an Oracle RAC database to mysql (InnoDB or Cluster - testing both at the moment), and the Oracle database uses a lot of multi-column indexes with some colums indexed in descending order. If I can't emulate the descending index colums somehow it's likely to seriously impact performance and possibly derail the prospect of migration - help! cheers Iain -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Help build our city at http://free-dc.myminicity.com ! -- Help build our city at http://free-dc.myminicity.com !
ANN: Database Workbench Pro v3.1.1 released
Dear reader, Upscene Productions releases Database Workbench Pro v3.1.1 which fixes an issue with the trial version and Windows Vista as well as two other bugs. For more info, see: http://www.upscene.com/news/20080521.htm Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance on LVM2
http://tldp.org/HOWTO/LVM-HOWTO/whatislvm.html On Tue, May 20, 2008 at 10:13 PM, Moon's Father [EMAIL PROTECTED] wrote: What is LVM? 2008/5/12 MarisRuskulis [EMAIL PROTECTED]: Hello! I'm wondering about MySQL LVM2 preformance, but cant found any comparisions. I know that there is some speed decrease with LVM, something about 30%. But how this decrease impacts overal MySQL performance? Now we are backuping replication slave server with mysqldump w full table locks, this takes some time. I think better solution is to use LVM snapshots, but this performance decrease really scares me. Has anyone some advices on this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- obed.org.mx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
Price, Randall wrote:
Could you use something like this (untried):
SELECT
CONCAT(COALESCE(r.first_name, ''), ' ',
COALESCE(r.last_name,''), '\n',
COALESCE(r.organization, ''), '\n',
COALESCE(r.title,''), '\n',
COALESCE(a.address1, ''), '\n',
COALESCE(a.city, ''), ', ',
COALESCE(a.state,''), ' ',
COALESCE(a.zip, ''), '\n',
COALESCE(r.email,''))
FROM
registrants r,
addresses a
WHERE
r.reg_id=121
this is good. though, if r.title is NULL I'll get an extra empty row on
screen:
john doe
doe, inc.
-- no title, empty row
123 main st.
testtown, TE 12345
also, I would like to hear opinion about the following query:
SELECT o.col1, o.col2, o.col3,
(
SELECT CONCAT_WS('', r.first_name, ' ', r.last_name, '\n',
r.organization, '', r.title, '\n', a.address1, '\n', a.city, ', ',
a.state, ' ', a.zip, '\n', r.email, '\nHome: ', left(r.phone_home, 3),
'-', mid(r.phone_home, 3, 3), '-', right(r.phone_home, 4), '\nWork: ',
left(r.phone_work, 3), '-', mid(r.phone_work, 3, 3), '-',
right(r.phone_work, 4))
FROM registrants r, addresses a
WHERE r.reg_id=o.registered_id
and a.reg_id=r.reg_id
and a.address_type='Business'
LIMIT 1
) as REGISTERED_BY,
pm.payment_method as payment_method_name, f.form_name FORM_NAME
FROM orders o, payment_methods pm, forms f
WHERE o.order_id=.$order_id.
AND pm.pm_id=o.payment_method
AND f.form_id=.$form_id.
is it smart/good way to have subquery this way or solution below is
better one:
SELECT o.col1, o.col2, o.col3, r.first_name, r.last_name,
r.organization, r.title, a.address1, a.city, a.state, a.zip, r.email,
r.phone_home, r.phone_work, pm.payment_method as payment_method_name,
f.form_name FORM_NAME
FROM orders o, payment_methods pm, forms f
WHERE o.order_id=.$order_id.
AND pm.pm_id=o.payment_method
AND f.form_id=.$form_id.
AND r.reg_id=o.registered_id
AND a.reg_id=r.reg_id
AND a.address_type='Business'
in this case I have to create string REGISTERED_BY by php.
reason I did it as example 1 is because I read in few books and online
people saying do whatever you can using query in mysql rather then using
php. it's faster, better, more secure,...
thoughts?
-afan
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
-Original Message-
From: Afan Pasalic [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 14, 2008 11:53 AM
To: mysql@lists.mysql.com
Subject: CONCAT doesn't work with NULL?
hi,
I have query
SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization,
'\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ',
a.zip, '\n', r.email)
FROM registrants r, addresses a
WHERE r.reg_id=121
if any of columns has value (e.g. title) NULL, I'll get as result 0
records.
If query doesn't have concat() - it works fine.
Why is that?
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Match/No Match query
I have a list of codes. Some, but not all, of these codes will match the entries in a product table. Here's what the data would look like: List of Codes: The rows in the product table look like prod_num code 222 333 What I want to is get a list of ALL of the codes, with the associated prod_num if it exists or a flag if it does not: code prod_num 222 xxx 333 I need to preserve the empty rows in order to match the data against an Excel worksheet (which is where the list of codes came from). I have done this by putting all of the codes into a temporary table and doing a LEFT JOIN against the product table. Works fine, lasts a long time. However, it seems that I ought to be able to do this without the temporary table, by using derived tables. I just can't figure out how. This is mostly a curiosity question. Thanks. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Match/No Match query
Jerry Schwartz wrote: I have a list of codes. Some, but not all, of these codes will match the entries in a product table. Here's what the data would look like: List of Codes: The rows in the product table look like prod_num code 222 333 What I want to is get a list of ALL of the codes, with the associated prod_num if it exists or a flag if it does not: code prod_num 222 xxx 333 I need to preserve the empty rows in order to match the data against an Excel worksheet (which is where the list of codes came from). I have done this by putting all of the codes into a temporary table and doing a LEFT JOIN against the product table. Works fine, lasts a long time. However, it seems that I ought to be able to do this without the temporary table, by using derived tables. I just can't figure out how. This would be easier if you gave your table structure. But something like this would work SELECT c.code, p.prod_num FROM CodeTable c LEFT OUTER JOIN ProductNumTable p USING (code) This will return null for prod_num if there is no association in the ProductNumTable. Not having more details on your data I can't say for sure but I am guessing a group by *might* be needed. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DESC index column
On Tue, May 20, 2008 at 11:20 AM, Bof [EMAIL PROTECTED] wrote: Hi all - Is there a good workaround for mysql's lack of 'DESC' functionality when creating an index? I'm looking at migrating an Oracle RAC database to mysql (InnoDB or Cluster - testing both at the moment), and the Oracle database uses a lot of multi-column indexes with some colums indexed in descending order. If I can't emulate the descending index colums somehow it's likely to seriously impact performance and possibly derail the prospect of migration - help! cheers Iain I have not yet run into performance issues with indexes not being stored ascending. Running queries with ORDER BY indexed_field ASC appears to me to have the same or near the same performance characteristics as ORDER BY indexed_field DESC. What are the circumstances where this crops up as an issue? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Match/No Match query
From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 12:25 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Match/No Match query Jerry Schwartz wrote: I have a list of codes. Some, but not all, of these codes will match the entries in a product table. Here's what the data would look like: List of Codes: The rows in the product table look like prod_num code 222 333 What I want to is get a list of ALL of the codes, with the associated prod_num if it exists or a flag if it does not: code prod_num 222 xxx 333 I need to preserve the empty rows in order to match the data against an Excel worksheet (which is where the list of codes came from). I have done this by putting all of the codes into a temporary table and doing a LEFT JOIN against the product table. Works fine, lasts a long time. However, it seems that I ought to be able to do this without the temporary table, by using derived tables. I just can't figure out how. This would be easier if you gave your table structure. But something like this would work SELECT c.code, p.prod_num FROM CodeTable c LEFT OUTER JOIN ProductNumTable p USING (code) [JS] That is what I am doing now. I was wondering if I could eliminate what you have designated as CodeTable, and do this all in a single (probably nested) query. This will return null for prod_num if there is no association in the ProductNumTable. Not having more details on your data I can't say for sure but I am guessing a group by *might* be needed. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Match/No Match query
Jerry Schwartz wrote: From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 12:25 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Match/No Match query Jerry Schwartz wrote: I have a list of codes. Some, but not all, of these codes will match the entries in a product table. Here's what the data would look like: List of Codes: The rows in the product table look like prod_num code 222 333 What I want to is get a list of ALL of the codes, with the associated prod_num if it exists or a flag if it does not: code prod_num 222 xxx 333 I need to preserve the empty rows in order to match the data against an Excel worksheet (which is where the list of codes came from). I have done this by putting all of the codes into a temporary table and doing a LEFT JOIN against the product table. Works fine, lasts a long time. However, it seems that I ought to be able to do this without the temporary table, by using derived tables. I just can't figure out how. This would be easier if you gave your table structure. But something like this would work SELECT c.code, p.prod_num FROM CodeTable c LEFT OUTER JOIN ProductNumTable p USING (code) [JS] That is what I am doing now. I was wondering if I could eliminate what you have designated as CodeTable, and do this all in a single (probably nested) query. In that case I must be missing something. What data do you have in the database that can be used to create the result. Some table structure would help and some more sample data that you want to use. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for a Mysql Guru/DBA
Hi... Got a small personal project that I'm considering, and I realize that I need a mysql database guru/dba to talk to to figure out the best approach to implementing a database for my needs of the project. The basic goal of the project is to be able to track the sites that I'm visiting via a Firefox extension. I want to be able to implement something like the breadcrumbs extension, but I want to be able to go a lot further. If you're interested, and you're reasonably good at mysql, and devising database structures/schema then let's talk! Given that this is my own personal project, I've got a small amount of $$$ for your time! Also, I apologize in advance if this is an inappropriate post for the list. And if the list isn't the right place, let me know of a better place to post!! Thanks -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication for reporting
Is it true that a single mysql server can be a slave to only one master? So if you need to replicate a dozen databases into a single reporting server, you need to have a chain of a dozen servers in between? Someone shared that to me, but it didn't make much sense. In oracle we have the following options to replicate: Snapshots (also known as materialized views) for individual tables Standby (similar to mysql, but replicates the entire db) Streams (pick whatever you need out of sql stream) So for reporting purposes from multiple db's, you'd mostly likely pick snapsohts or streams. Thanks, andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Match/No Match query
chris, you're going to need a source for all the distinct codes that you may see in your product tableie: if you don't have a full list somewhere (like your temp table) you cannot do your query because you have nothing to compare against. assuming you had some table X which had a list of all the possible codes and you could generate the complete list by doing something like : select distinct code from X; you could use this in your sql statement like : select p.prod_num, p.code from products p left join (select distinct code from X) as codes on p.code = codes.code; Yong. -Original Message- From: Chris W [mailto:[EMAIL PROTECTED] Sent: May 21, 2008 10:14 AM To: Jerry Schwartz; MYSQL General List Subject: Re: Match/No Match query Jerry Schwartz wrote: From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 12:25 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Match/No Match query Jerry Schwartz wrote: I have a list of codes. Some, but not all, of these codes will match the entries in a product table. Here's what the data would look like: List of Codes: The rows in the product table look like prod_num code 222 333 What I want to is get a list of ALL of the codes, with the associated prod_num if it exists or a flag if it does not: code prod_num 222 xxx 333 I need to preserve the empty rows in order to match the data against an Excel worksheet (which is where the list of codes came from). I have done this by putting all of the codes into a temporary table and doing a LEFT JOIN against the product table. Works fine, lasts a long time. However, it seems that I ought to be able to do this without the temporary table, by using derived tables. I just can't figure out how. This would be easier if you gave your table structure. But something like this would work SELECT c.code, p.prod_num FROM CodeTable c LEFT OUTER JOIN ProductNumTable p USING (code) [JS] That is what I am doing now. I was wondering if I could eliminate what you have designated as CodeTable, and do this all in a single (probably nested) query. In that case I must be missing something. What data do you have in the database that can be used to create the result. Some table structure would help and some more sample data that you want to use. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication for reporting
yes, one master to multiple slaves. I believe u can set up multiple mysql instances on a single machine with each mysql instance collecting from a different master. Yong. -Original Message- From: Andrey Dmitriev [mailto:[EMAIL PROTECTED] Sent: May 21, 2008 11:29 AM To: mysql@lists.mysql.com Subject: Replication for reporting Is it true that a single mysql server can be a slave to only one master? So if you need to replicate a dozen databases into a single reporting server, you need to have a chain of a dozen servers in between? Someone shared that to me, but it didn't make much sense. In oracle we have the following options to replicate: Snapshots (also known as materialized views) for individual tables Standby (similar to mysql, but replicates the entire db) Streams (pick whatever you need out of sql stream) So for reporting purposes from multiple db's, you'd mostly likely pick snapsohts or streams. Thanks, andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Match/No Match query
-Original Message- From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 1:14 PM To: Jerry Schwartz; MYSQL General List Subject: Re: Match/No Match query Jerry Schwartz wrote: From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 12:25 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Match/No Match query Jerry Schwartz wrote: I have a list of codes. Some, but not all, of these codes will match the entries in a product table. Here's what the data would look like: List of Codes: The rows in the product table look like prod_num code 222 333 What I want to is get a list of ALL of the codes, with the associated prod_num if it exists or a flag if it does not: code prod_num 222 xxx 333 I need to preserve the empty rows in order to match the data against an Excel worksheet (which is where the list of codes came from). I have done this by putting all of the codes into a temporary table and doing a LEFT JOIN against the product table. Works fine, lasts a long time. However, it seems that I ought to be able to do this without the temporary table, by using derived tables. I just can't figure out how. This would be easier if you gave your table structure. But something like this would work SELECT c.code, p.prod_num FROM CodeTable c LEFT OUTER JOIN ProductNumTable p USING (code) [JS] That is what I am doing now. I was wondering if I could eliminate what you have designated as CodeTable, and do this all in a single (probably nested) query. In that case I must be missing something. What data do you have in the database that can be used to create the result. Some table structure would help and some more sample data that you want to use. [JS] Here's the thing. There is only one table in the data base. So far as this matter is concerned, it has two columns: prod_num and code. What I thought might be possible is to derive a table from a list of codes. That might not be possible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Match/No Match query
-Original Message- From: Yong Lee [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 3:10 PM To: 'Chris W'; 'Jerry Schwartz'; 'MYSQL General List' Subject: RE: Match/No Match query chris, you're going to need a source for all the distinct codes that you may see in your product tableie: if you don't have a full list somewhere (like your temp table) you cannot do your query because you have nothing to compare against. [JS] That's the conclusion I reached, but I wasn't sure that there wasn't some trick I was missing. assuming you had some table X which had a list of all the possible codes and you could generate the complete list by doing something like : select distinct code from X; you could use this in your sql statement like : select p.prod_num, p.code from products p left join (select distinct code from X) as codes on p.code = codes.code; [JS] Actually, I don't want distinct values; I'm actually looking for duplicates, so I use GROUP BY ... HAVING Thanks for confirming my conclusions. Yong. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
indexes and speeds
hi all... just wondering what is the performance difference between: PRIMARY KEY [/|index_type|/] (/|index_col_name|/1,/|index_col_name|/2) at the time of the table creation or create index index_name1 on table_name (/|index_col_name|/1); create index index_name2 on table_name (/|index_col_name|/2); after the table has been made? the question i guess is: is there performance advantage to have a primary key defined on two (or more) fields (columns) at the time of table creation or is it better to have different indexes (keys) defined separately for each column that needs to be indexed? thanks
Re: Replication for reporting
You might be able to do it with the federated engine: http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html Fire a trigger on your main tables which update some row in a foreign MySql database used for accounting. I've not tried this but the theory is sound. Ben Andrey Dmitriev wrote: Is it true that a single mysql server can be a slave to only one master? So if you need to replicate a dozen databases into a single reporting server, you need to have a chain of a dozen servers in between? Someone shared that to me, but it didn't make much sense. In oracle we have the following options to replicate: Snapshots (also known as materialized views) for individual tables Standby (similar to mysql, but replicates the entire db) Streams (pick whatever you need out of sql stream) So for reporting purposes from multiple db's, you'd mostly likely pick snapsohts or streams. Thanks, andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Floor Decimal Math
On May 16, 2008, at 2:42 AM, Adam de Zoete wrote: Thanks for your responses, i thought it was a float problem so i was trying to CAST as a DECIMAL to fix it. It turns out (and the manual does not document this) that casting as decimals doesn't actually work in mysql 4.1.20. ROUND() is needed instead. DECIMAL is not listed for CAST() in the 4.1 manual because it is not supported in 4.1. DECIMAL is listed for CAST() in the 5.0 as supported from 5.0.8 on. mysql select ROUND(11.11-FLOOR(11.11),2)=0.11; +---+ | ROUND(11.11-FLOOR(11.11),2)=0.11 | +---+ | 1 | +---+ Thanks for all your help, Adam Jerry Schwartz wrote: Don't feel bad, many an experience programmer has been bitten by this. The problem is that many decimal fractions do not have exact representations as binary fractions. .01 is an example of this. I'm not sure how MySQL does arithmetic internally, but (11.11 - 11) is just a hair under .11: mysql SELECT (11.11 - 11) = .10; +-+ | (11.11 - 11) = .10 | +-+ | 1 | +-+ You need to allow for a slight fudge factor. This is even a problem at the hardware design level. The best way to handle this is to make sure all of your operators are type DECIMAL. You might have to cast them. Apparently MySQL 5+ introduced some algorithms that helped with these problems, although not with yours. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Looking for a Mysql Guru/DBA
In china? On Thu, May 22, 2008 at 1:43 AM, bruce [EMAIL PROTECTED] wrote: Hi... Got a small personal project that I'm considering, and I realize that I need a mysql database guru/dba to talk to to figure out the best approach to implementing a database for my needs of the project. The basic goal of the project is to be able to track the sites that I'm visiting via a Firefox extension. I want to be able to implement something like the breadcrumbs extension, but I want to be able to go a lot further. If you're interested, and you're reasonably good at mysql, and devising database structures/schema then let's talk! Given that this is my own personal project, I've got a small amount of $$$ for your time! Also, I apologize in advance if this is an inappropriate post for the list. And if the list isn't the right place, let me know of a better place to post!! Thanks -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
subquery error with no result
Hi, I tried to look for records from a table with this query: SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT binfo FROM sent_sms WHERE momt = 'DLR' ) But MySQL returns this error: #1242 - Subquery returns more than 1 row I tried also with ANY, IN, EXISTS. And modified the query into: SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR') AS t1 WHERE momt = 'MT'. But none works. What I want to view is, all records which has momt = 'MT' and binfo from the same table where has momt = 'DLR' and has the same binfo. TIA Regards, Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery error with no result
Would this work for you? SELECT msgdata FROM sent_sms WHERE momt = 'MT' AND binfo IN (SELECT binfo FROM sent_sms WHERE momt = 'DLR') David On 5/21/08 10:30 PM, sangprabv [EMAIL PROTECTED] wrote: Hi, I tried to look for records from a table with this query: SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT binfo FROM sent_sms WHERE momt = 'DLR' ) But MySQL returns this error: #1242 - Subquery returns more than 1 row I tried also with ANY, IN, EXISTS. And modified the query into: SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR') AS t1 WHERE momt = 'MT'. But none works. What I want to view is, all records which has momt = 'MT' and binfo from the same table where has momt = 'DLR' and has the same binfo. TIA Regards, Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP ORDER BY Question
Hello MySQL Users- I am pretty sure this is a simple question and I am over thinking how to solve the problem, so I am hoping the community can help. I am selecting a pretty straightforward aggregation from a single stats table with the following format: SELECT Description LongDescription Detail SUM(Volume) FROM StatsTable GROUP BY Description LongDescription Detail What I am trying to limit this query to is the top 100 details ordered by SUM(Volume) DESC for each unique LongDescription This is what I am trying now but its not quite correct, it simply returns 100 of the top details. SELECT Description LongDescription Detail SUM(Volume) FROM StatsTable GROUP BY Description LongDescription Detail ORDER BY SUM(Volume) DESC LIMIT 100 What I believe would work is a function in MySQL that is equivalent to the CUBE function in Oracle. Any direction would be greatly appreciated! David
