Help with formatting of 1:n relationship
Hello all I have a database with a 1:n relationship as follows: Item (1) fld1 fld2 ... Item_info (n) - fld1 fld2 ... My client wants me to export these linked records into an Excel file in the following format. item.fld1 item.fld2 ... item_info.fld1 item_info.fld2 ... item_info.fld1 item_info.fld2 .. ie. each item and all the records that make up its info together in a single Excel row. I'm using an excel library that accepts a SELECT as input and generates an XLS file with the records as output. I'd like to use this where possible. However I don't know if it's possible to write a SELECT that compresses a 1:n relationship into a single row. Is this possible, and if yes, could someone show me how? Or could you suggest another way in which I could achieve the above required output? Thanks, Vikram -- Operator: So what do you need? Besides a miracle. Neo: Guns. Lots of guns. -- The Matrix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Optimizing IN queries?
I have an app that joins results from a MySQL query with the results of a lookup against an external search engine, which returns its results in the form of primary-key id's of one of the tables in my database. I handle this by adding these results with an IN query. (My impression had been that this is faster than a long chain of OR's.) In the simplest case, if I'm _only_ searching against these results, the query will look something like this (I've removed some columns from the SELECT list for readability): SELECT me.id, me.quotation_id, quotation.qt, part.id, part.cit FROM cwGroup me JOIN quotation ON ( quotation.id = me.quotation_id ) JOIN part ON ( part.id = quotation.part_id ) WHERE ( quotation.id IN (107037, 304650, 508795, 712723, 1054653)) When I ran this on a query that generated a moderate number of results (over 1000, but not millions), it took MySQL 26 seconds to reply on my dev box. Can someone suggest what I can look at to speed this up? The section of the manual that talked about optimizing range queries spent a lot of time explaining how they work but very little on how to speed them up. The EXPLAIN didn't really help--only one column got a lot of results, and it's not clear to me why MySQL would take 26 seconds to fetch 1214 records. The EXPLAIN looks like this: --- *** 1. row *** id: 1 select_type: SIMPLE table: me type: range possible_keys: quotation_id key: quotation_id key_len: 4 ref: NULL rows: 1214 Extra: Using where *** 2. row *** id: 1 select_type: SIMPLE table: quotation type: eq_ref possible_keys: PRIMARY,part_id key: PRIMARY key_len: 4 ref: rqs_incs.me.quotation_id rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: part type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: rqs_incs.quotation.part_id rows: 1 --- Thanks very much. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Optimizing IN queries?
If you are running MySQL 5, try moving the WHERE condition into the JOIN condition, which is really where you want the filter since it's part of the join. SELECT me.id, me.quotation_id, quotation.qt, part.id, part.cit FROM cwGroup me JOIN quotation ON ( quotation.id = me.quotation_id AND quotation.id IN (107037, 304650, 508795, 712723, 1054653)) JOIN part ON ( part.id = quotation.part_id ) That may or may not help, check if the explain changes. Brent Baisley On Mon, Jan 26, 2009 at 6:16 AM, Jesse Sheidlower jes...@panix.com wrote: I have an app that joins results from a MySQL query with the results of a lookup against an external search engine, which returns its results in the form of primary-key id's of one of the tables in my database. I handle this by adding these results with an IN query. (My impression had been that this is faster than a long chain of OR's.) In the simplest case, if I'm _only_ searching against these results, the query will look something like this (I've removed some columns from the SELECT list for readability): SELECT me.id, me.quotation_id, quotation.qt, part.id, part.cit FROM cwGroup me JOIN quotation ON ( quotation.id = me.quotation_id ) JOIN part ON ( part.id = quotation.part_id ) WHERE ( quotation.id IN (107037, 304650, 508795, 712723, 1054653)) When I ran this on a query that generated a moderate number of results (over 1000, but not millions), it took MySQL 26 seconds to reply on my dev box. Can someone suggest what I can look at to speed this up? The section of the manual that talked about optimizing range queries spent a lot of time explaining how they work but very little on how to speed them up. The EXPLAIN didn't really help--only one column got a lot of results, and it's not clear to me why MySQL would take 26 seconds to fetch 1214 records. The EXPLAIN looks like this: --- *** 1. row *** id: 1 select_type: SIMPLE table: me type: range possible_keys: quotation_id key: quotation_id key_len: 4 ref: NULL rows: 1214 Extra: Using where *** 2. row *** id: 1 select_type: SIMPLE table: quotation type: eq_ref possible_keys: PRIMARY,part_id key: PRIMARY key_len: 4 ref: rqs_incs.me.quotation_id rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: part type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: rqs_incs.quotation.part_id rows: 1 --- Thanks very much. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=brentt...@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: Help with formatting of 1:n relationship
-Original Message- From: blue.trapez...@gmail.com [mailto:blue.trapez...@gmail.com] On Behalf Of Vikram Vaswani Sent: Monday, January 26, 2009 7:02 AM To: mysql@lists.mysql.com Subject: Help with formatting of 1:n relationship Hello all I have a database with a 1:n relationship as follows: Item (1) fld1 fld2 ... Item_info (n) - fld1 fld2 ... My client wants me to export these linked records into an Excel file in the following format. item.fld1 item.fld2 ... item_info.fld1 item_info.fld2 ... item_info.fld1 item_info.fld2 .. ie. each item and all the records that make up its info together in a single Excel row. I'm using an excel library that accepts a SELECT as input and generates an XLS file with the records as output. I'd like to use this where possible. However I don't know if it's possible to write a SELECT that compresses a 1:n relationship into a single row. Is this possible, and if yes, could someone show me how? Or could you suggest another way in which I could achieve the above required output? [JS] I do something similar with a GROUP BY and GROUP_CONCAT(), but that would put all of your ns into one field. In my case, that's what I want; but it doesn't exactly solve yours because MySQL doesn't seem to have an explode function. Does whatever tool you are using have any place where you can manipulate the data between the SELECT and the creation of the XLS? If not,I think you need a user-defined function for this, or perhaps you can do it with a user-defined procedure. Probably someone has a better idea. Thanks, Vikram -- Operator: So what do you need? Besides a miracle. Neo: Guns. Lots of guns. -- The Matrix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.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: Help with formatting of 1:n relationship
Hi Jerry Thanks for your input on this! I'm using an excel library that accepts a SELECT as input and generates an XLS file with the records as output. I'd like to use this where possible. However I don't know if it's possible to write a SELECT that compresses a 1:n relationship into a single row. Is this possible, and if yes, could someone show me how? Or could you suggest another way in which I could achieve the above required output? Does whatever tool you are using have any place where you can manipulate the data between the SELECT and the creation of the XLS? If not,I think you need a user-defined function for this, or perhaps you can do it with a user-defined procedure. Unfortunately the tool doesn't let me manipulate the data. It simply reads the result set and pops each field into a separate column in the XLS. So any formatting I do has to be part of the SELECT. I did consider a procedure but the problem is that the client is still using MySQL 4.x, which afaik doesn't support stored procedures. An upgrade is not something they can do at this point, as they're using s shared host so the server isn't really under their control. Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with formatting of 1:n relationship
Hi, On Mon, Jan 26, 2009 at 11:29 AM, Vikram Vaswani bacc...@vsnl.com wrote: Hi Jerry Thanks for your input on this! I'm using an excel library that accepts a SELECT as input and generates an XLS file with the records as output. I'd like to use this where possible. However I don't know if it's possible to write a SELECT that compresses a 1:n relationship into a single row. Is this possible, and if yes, could someone show me how? Or could you suggest another way in which I could achieve the above required output? Does whatever tool you are using have any place where you can manipulate the data between the SELECT and the creation of the XLS? If not,I think you need a user-defined function for this, or perhaps you can do it with a user-defined procedure. Unfortunately the tool doesn't let me manipulate the data. It simply reads the result set and pops each field into a separate column in the XLS. So any formatting I do has to be part of the SELECT. I did consider a procedure but the problem is that the client is still using MySQL 4.x, which afaik doesn't support stored procedures. An upgrade is not something they can do at this point, as they're using s shared host so the server isn't really under their control. I think what you're really looking for is a pivot table or crosstab report in SQL itself, right? Since you're manipulating this data in Excel, maybe you can do it there, because honestly it's better suited for that than MySQL is. But, if you need to do it in SQL, you can search the list archives -- there is something about it pretty much every week or so :) -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL University session on January 29: Scalability Challenges in an InnoDB-based Replication Environment
MySQL University: Scalability Challenges in an InnoDB-based Replication Environment This Thursday (January 29th), we're continuing our series of sessions on MySQL performance measuring and improvements with David Lutz' presentation titled Scalability Challenges in an InnoDB-based Replication Environment. David works in the Performance and Applications Engineering department at Sun Microsystems, so again, expect to get some deep insights into the inner workings of the MySQL Server. David is based in California, so note that this session will take place in the morning (America) or evening (Europe), respectively. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. Here's the schedule for the upcoming weeks (see http://forge.mysql.com/wiki/MySQL_University for a better format of this list): January 29, 200916:00 UTC / 8am PDT (Pacific) / 10am CST (Central) / 11am EST (Eastern) / 16:00 GMT (London) / 17:00 CET (Berlin) / 19:00 MDT (Moscow)Scalability Challenges in an InnoDB-based Replication Environment David Lutz February 5, 200908:00 UTC / 8:00 GMT / 9:00 CET / 11:00 MDT (Moscow) / 13:30 IST (India) / 16:00 CST (Beijing) / 17:00 JST (Tokyo) / 19:00 EDT (Melbourne) MySQL Performance and Scalability Project - Issues and Opportunities Allan Packer February 12, 2008 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Using DTrace with MySQL MC Brown February 19, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Developing MySQL on Solaris MC Brown Trond Norbye February 26, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Backing up MySQL using file system snapshotsLenz Grimmer March 5, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow)Good Coding Style Konstantin Osipov March 12, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) MySQL and ZFS MC Brown The session address (Dimdim URL) for all sessions is: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity Please bookmark this address, since it will remain valid for all future MySQL University sessions. Remember, though, that the meeting room will open only 15 minutes before the session starts. Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording (slides and voice), and more. All you need to do to attend MySQL University sessions is point your browser to the address given above. All MySQL University sessions are recorded, that is, slides and voice can be viewed as a Flash file (.flv). You can find those recordings on the respective MySQL University session pages which are listed on the MySQL University home page: http://forge.mysql.com/wiki/MySQL_University Cheers, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.de/mysql mailto: stefan.h...@sun.com Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how can trace stored procedure usage?
I am trying to track the usage of stored procedures on our system. My solution so far is to parse the general log for call queries. This works well for procedures that are called from the command line, but the general log does not seem to report procedures called from within other procedures. Is there a way to do that? Is there a better overall way to track procedure calls? What about function calls? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
RE: how can trace stored procedure usage?
Jim- you can try to port Oracles utldtree.sql Caveat Emptor: Hasnt been worked on since 92 and is VERY buggy! Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Mon, 26 Jan 2009 11:01:58 -0600 Subject: how can trace stored procedure usage? From: jlyons4...@gmail.com To: mysql@lists.mysql.com I am trying to track the usage of stored procedures on our system. My solution so far is to parse the general log for call queries. This works well for procedures that are called from the command line, but the general log does not seem to report procedures called from within other procedures. Is there a way to do that? Is there a better overall way to track procedure calls? What about function calls? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com _ Windows Live™: E-mail. Chat. Share. Get more ways to connect. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_allup_howitworks_012009
RE: Help with formatting of 1:n relationship
-Original Message- From: blue.trapez...@gmail.com [mailto:blue.trapez...@gmail.com] On Behalf Of Vikram Vaswani Sent: Monday, January 26, 2009 11:29 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Help with formatting of 1:n relationship Hi Jerry Thanks for your input on this! I'm using an excel library that accepts a SELECT as input and generates an XLS file with the records as output. I'd like to use this where possible. However I don't know if it's possible to write a SELECT that compresses a 1:n relationship into a single row. Is this possible, and if yes, could someone show me how? Or could you suggest another way in which I could achieve the above required output? Does whatever tool you are using have any place where you can manipulate the data between the SELECT and the creation of the XLS? If not,I think you need a user-defined function for this, or perhaps you can do it with a user-defined procedure. Unfortunately the tool doesn't let me manipulate the data. It simply reads the result set and pops each field into a separate column in the XLS. So any formatting I do has to be part of the SELECT. [JS] There is one other possibility that occurs to me: you could use the GROUP_CONCAT technique to get the data into Excel, and then use the Excel text to columns command to do the rest of the work. Any way you slice it, you're going to have to torture your procedures to get your result. I did consider a procedure but the problem is that the client is still using MySQL 4.x, which afaik doesn't support stored procedures. An upgrade is not something they can do at this point, as they're using s shared host so the server isn't really under their control. Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.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: Help with formatting of 1:n relationship
Hi, I did consider a procedure but the problem is that the client is still using MySQL 4.x, which afaik doesn't support stored procedures. An upgrade is not something they can do at this point, as they're using s shared host so the server isn't really under their control. I think what you're really looking for is a pivot table or crosstab report in SQL itself, right? I have used pivot tables before but I'm far from an expert on them. I wasn't able to understand how I could apply a pivot table structure to this problem. Can you give me some more details on what you had in mind? Thanks, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Looking for MySQL Speakers
Hello, I run PostgreSQL Conference (long announcement below). We are looking for some people to speak on why MySQL at East 09 in Philly. Anybody interested should submit a talk (or better yet Tutorial). Yes this would be a little different for a MySQL person but I believe in reaching out. Specifically I would love to see one or two people be willing to convince PostgreSQL people to switch. Begin long infomercial: PostgreSQL Conference, U.S., East 09 will be held in Philadelphia at historic Drexel University from April 3rd through 5th. The call for papers is now out at: http://www.postgresqlconference.org/2009/east/ . As always we let our submissions define our actual tracks. If you have something you want to talk about it. Submit it. As long as it is about PostgreSQL (or doing something with PostgreSQL) we will consider it. We are seeking creative ideas about things we can do at the conference. At West we had a code sprint. The sprint was very successful as it was about all things postgresql and open source. It wasn't just hacking back end code but people worked on all kinds of things.. Is someone up for running a code sprint? There has also been specific interest in having us add (in addition to our advanced topics) a newbie track. Please do not be afraid to submit a talks on items such as: * Backing up PostgreSQL * Understanding and Configuring Autovacuum * Normalization * Trigger Happy (how to use triggers ;) * PITR -- happiness is a shipped transaction log Other topics we are interested in beyond the standard PostgreSQL architectural fanfare are: * Groovy/Grails * Django * PHP * Postgis * Pylons * SQL Alchemy So don't delay, PostgreSQL Conference, U.S. is the premeire PostgreSQL conference series for the United States PostgreSQL community! Please submit your talk here: http://www.postgresqlconference.org/2009/east/ . Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
