Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On 20 January 2011 19:20, Dotan Cohen dotanco...@gmail.com wrote: On Thu, Jan 20, 2011 at 19:21, Richard Quadling rquadl...@gmail.com wrote: That is terrific, at least the first half. The second half, with the Venn diagrams, is awkward! When you get heavily nested data, the adjacent list model (where you have a parentid for every uniqueid), you very quickly get into complicated logic trying to traverse n-levels. The nested set model is specifically built to handle this issue. I'd recommend getting to grips with it. It will make finding items belonging to a group (or a super group) a LOT easier. Especially if you have multiple tag hierarchies. Is that strategy widely deployed, then? It seems so unruly having to change on average half the database records for every new leaf. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com Changing data in a database is the role of the database engine. It is much more efficient to have the cost on the insert than it is on the select. The adjacent list model is very expensive at n-levels for the select, but trivial cost for the insert. If you are inserting millions of rows but only occasionally looking at the data, then stick with the adjacent list model. But if tags and n-levels are regularly accessed and form a main part to the functionality of the app, then you may want to reconsider. Sure, the insert for the nested set model is more expensive in terms of the number of rows to amend, but indexing will certainly should certainly help. If you have tools to help optimize the tag table and the queries you use, then I'd follow the recommendations (I use MS SQL, so my Query Optimization tools help me here). The nested set model is extremely efficient on the select. It is a trade off that you have to decide upon, based upon your data and needs. If, as I suspect, you are going to be doing a LOT of selects on the tags and (in the future) to multiple levels, then this aspect needs to be very efficient. For me it is well worth the effort of moving from the adjacent list model to the nested set model. Both mechanisms work. In my opinion, the adjacent list model is for truly simply lookups, not for complicated n-levels. One of the changes I made to the nested set model was for a Bill Of Materials module. The client made complex machinery (industrial lathes). The sum quantity for all the parts were in the 20,000 region. Each nut, bolt, screw, etc. Many sub-assemblies. The parent/child node logic was massive in dealing with retrieving questions like How many machines can we build?, What stock do we need to buy/make to complete an order of 20 lathes?. Lot's of recursion into each level to build the list. Getting the results would take 3 or 4 minutes (this is in a non SQL environment using a peer-to-peer modified D-ISAM database - it was already slow because of all that). When I moved to the nested set model, no recursion and 1 query (more or less) and I have all the results I needed. It was seconds in comparison. Regards, Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
Actually, I'm the customer! But assuming that a customer exists, that implies compensation, and therefore fair bait. Then that's different altogether. you get to decide what information is displayed, and what information is 'sensed', and on what platform. Yes, but before I get to that stage (relatively easy today with high level languages such as PHP or C#) I need to decide how to organise the data. What do you want to sense and what do you want to display(not to say I'm an expert, but I like to think in CS)? The application will pull calendar records by category: entertainment, food, gov, transportation, etc. The idea is that the user could query for, say, postal offices open after 17:00 on Thursday, or films between 20:00 and 22:00 on Monday, or buses leaving Amsterdam to Rotterdam on Monday morning. By the way, I figured out what the troll issue was. I was confusing your signature for the body of the post. It was familiar, too, so I thought that it may have been a troll post on /. or such. It turns out that I had seen it on the Python-tutor list, and their I also mistook it for a troll as the length of the sig far outweighs the length of your typically concise and to-the-point post. Constructive advice: trim the sig! Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.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: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
If you are doing this often, you could leave spaces in the left and right values so that you could minimize the number of rows that need to be updated. The article makes every leaf use x and x+1 for left and right which forces another update to add a child. If instead you used x and x+20 you'd leave space for more children without any updates. This could be applied from top to bottom, starting with the root category getting 0 and MAX_INT for its values. Then I would have to check what values are available when inserting, and possibly normalise every so often. I'll think about that, and when I have enough data in the database I'll set up a test system to play with the possibility. However, it's probably not even worth applying that complexity until you prove that frequent category additions are causing problems. Most systems will be querying against the categories table far more frequently, and that's where this model pays off. If you want to see all products in category X and its subcategories, it's a single *non-recursive* query. That's huge if you are doing a lot of searches like this. You are right, that non-recursive bit is important. In fact, I think that I'm convinced. Thanks! But what a mess this would be if the two methods go out of sync! Sure, but these values would be maintained by your code--not end-users. It just comes down to making sure your code is correct through appropriate unit tests. By moving the logic to a stored procedure, you can ensure the table is locked during the updates to keep two users from adding a new category simultaneously. So long as it is in fact my code, that's fine. But when others start maintaining it and not reading comments, it may get ugly. That does not apply to this particular pet project, but it is a consideration for future projects. That pays off more? For the guy writing code or for the database memory requirement? Performance-wise. The nested set method looks to be moderately more complex code-wise, but luckily that is done just once while querying the database is done again and again. As with all optimizations, it's best to measure and make sure there's a problem before trying to solve it. Once you've built a few hierarchical systems, you'll be able to make a gut call up front. I see, thanks. Good point about making sure that the problem exists before trying to fix it, I've seen people optimise away where there is no bottleneck. Only two update statements, but they are affecting on average half the database's rows! Of a single table: categories. Hopefully you have far more items that get categorized than you do categories. True. Which do you call the hierarchical model? That term is not used in the linked article. Well, both models are hierarchical in the sense that there's a parent-child relationship. By hierarchical here I mean that the method of implementation involves each category pointing to its parent directly via a parent_id column. Searching for all subcategories of category X requires searching first for all children, then all grandchildren, and so on, resulting in a recursive query. Using the nested sets model requires a single non-recursive query to get the same data. I do agree that the non-recursive method at retrieval time advantage far outweighs the update-half-the-table issue upon addition of an additional category. Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.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: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
Yes, and an edge list model may perform better in other respects too: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html Thanks. I am currently reading Trees and Hierarchies in SQL for Smarties by Joe Celko, which also deals with a similar model. It is revealing and an interesting way of looking at the issue of organising data. In addition to those two links, this one also seems relevant: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html -- Dotan Cohen http://gibberish.co.il http://what-is-what.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: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Fri, Jan 21, 2011 at 12:29, Richard Quadling rquadl...@gmail.com wrote: Changing data in a database is the role of the database engine. It is much more efficient to have the cost on the insert than it is on the select. Agreed. On insert I could even delegate the operation to another thread which does not timeout with the pageload. The adjacent list model is very expensive at n-levels for the select, but trivial cost for the insert. If you are inserting millions of rows but only occasionally looking at the data, then stick with the adjacent list model. But if tags and n-levels are regularly accessed and form a main part to the functionality of the app, then you may want to reconsider. I've already reconsidered after some sleep and coffee! Sure, the insert for the nested set model is more expensive in terms of the number of rows to amend, but indexing will certainly should certainly help. If you have tools to help optimize the tag table and the queries you use, then I'd follow the recommendations (I use MS SQL, so my Query Optimization tools help me here). The nested set model is extremely efficient on the select. Interesting. I am using MySQL for this application, but another hat I wear is learning C# with MS tools and I will have to look into the Query Optimisation. It is a trade off that you have to decide upon, based upon your data and needs. If, as I suspect, you are going to be doing a LOT of selects on the tags and (in the future) to multiple levels, then this aspect needs to be very efficient. You suspect correctly. For me it is well worth the effort of moving from the adjacent list model to the nested set model. Both mechanisms work. In my opinion, the adjacent list model is for truly simply lookups, not for complicated n-levels. One of the changes I made to the nested set model was for a Bill Of Materials module. The client made complex machinery (industrial lathes). The sum quantity for all the parts were in the 20,000 region. Each nut, bolt, screw, etc. Many sub-assemblies. The parent/child node logic was massive in dealing with retrieving questions like How many machines can we build?, What stock do we need to buy/make to complete an order of 20 lathes?. Lot's of recursion into each level to build the list. Getting the results would take 3 or 4 minutes (this is in a non SQL environment using a peer-to-peer modified D-ISAM database - it was already slow because of all that). When I moved to the nested set model, no recursion and 1 query (more or less) and I have all the results I needed. It was seconds in comparison. Thanks, I enjoy reading these real-life scenarios. This was a terrific example. -- Dotan Cohen http://gibberish.co.il http://what-is-what.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: best way to have a unique key
-Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Friday, January 21, 2011 1:22 AM To: Anthony Pace Cc: Michael Dykman; mysql. Subject: Re: best way to have a unique key I have to say, something similar was my first thought, too - you never mention uuid in your original post. As already stated, uuid() should be a Universal Unique IDentifier. It's afaik a random 128-bit number; given the space to choose from it should be rather unique. I have to admit that I'm not entirely confident about that myself, either, though: as Pratchett put it, one-in-a-million chances tend to pop up nine times out of ten. [JS] A UUID (what Microsoft calls a GUID) is based in part on the MAC address of the generating device. Since MAC addresses are supposed to be unique across the known universe, so should a UUID. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com The code should have bits for handling duplicate primaries regardless of the method used to generate it, tough, so there's no reason to not do it. Having two subsequent UUID() calls generate pre-existing numbers seems to me to be likely in the same way as having Bush return his dirty oil dollars to Irak. On Thu, Jan 20, 2011 at 8:10 PM, Anthony Pace anthony.p...@utoronto.cawrote: Dude, come on. I know that all primary keys have to be unique; however, I was obviously referring to the use of uuid over auto incrementation. On 1/20/2011 1:36 PM, Michael Dykman wrote: It is axiomatic in the relational model that a primary must be unique. This is not a quirk put forth by your current employer. Neither MySQL nor any other RDBMS will allow you to establish a primary key that is not unique. - michael dykman On Thu, Jan 20, 2011 at 1:32 PM, Anthony Paceanthony.p...@utoronto.ca wrote: Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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 query
Thanks for the suggestions everybody. I added in columns to store the day, month and year of the created_at value, and then added in an index on (newsletter_id, created_month, created_day), and the the slow queries reduced from around 20 seconds to 0.5 seconds! I also removed the redundant indexes. Cheers, Simon On 19 January 2011 02:11, Steve Meyers steve-mysql-l...@spamwiz.com wrote: On 1/18/11 10:22 AM, Simon Wilkinson wrote: SELECT articles.* FROM articles INNER JOIN newsletters ON articles.newsletter_id = newsletters.id INNER JOIN users ON users.id = newsletters.user_id WHERE users.id =12 AND MONTH(articles.created_at) = '12' AND DAY(articles.created_at) = '5' ORDER BY YEAR(articles.created_at), LENGTH(articles.body); Simon - There are a few issues that are slowing down your query. First, you're running functions to calculate the month and day of each article that is looked at. As an aside, are you sure you don't want the DAYOFMONTH() function? Second, it's ideal to have the where clause in your query filter down (using an index) to as few rows as possible of the first table. Other tables you join should ideally be 1 to 1 from the first table. To accomplish this, you would probably need the user_id in your articles table. Another aside -- I noticed you have index_articles_on_newsletter_id as well as index_articles_on_newsletter_id_and_created_at. The first index is redundant, the second index will take care of it. This will slow down your INSERT/UPDATE/DELETE queries to some degree. Steve
Re: best way to have a unique key
One of the components of the UUID is drawn form the mac address of the server.. While in practice this is not true of all systems (except from http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid) Currently, the MAC address of an interface is taken into account only on FreeBSD and Linux. On other operating systems, MySQL uses a randomly generated 48-bit number. (end except) that potentially 48-bit random portion has 281474976710656 possibilities, which makes in far, far more likely that your server is hit by a meteor during a snowstorm in August while the Dalai Lama is doing an Elvis impression for the Chinese premier. - michael dykman On Fri, Jan 21, 2011 at 1:22 AM, Johan De Meersman vegiv...@tuxera.be wrote: I have to say, something similar was my first thought, too - you never mention uuid in your original post. As already stated, uuid() should be a Universal Unique IDentifier. It's afaik a random 128-bit number; given the space to choose from it should be rather unique. I have to admit that I'm not entirely confident about that myself, either, though: as Pratchett put it, one-in-a-million chances tend to pop up nine times out of ten. The code should have bits for handling duplicate primaries regardless of the method used to generate it, tough, so there's no reason to not do it. Having two subsequent UUID() calls generate pre-existing numbers seems to me to be likely in the same way as having Bush return his dirty oil dollars to Irak. On Thu, Jan 20, 2011 at 8:10 PM, Anthony Pace anthony.p...@utoronto.cawrote: Dude, come on. I know that all primary keys have to be unique; however, I was obviously referring to the use of uuid over auto incrementation. On 1/20/2011 1:36 PM, Michael Dykman wrote: It is axiomatic in the relational model that a primary must be unique. This is not a quirk put forth by your current employer. Neither MySQL nor any other RDBMS will allow you to establish a primary key that is not unique. - michael dykman On Thu, Jan 20, 2011 at 1:32 PM, Anthony Paceanthony.p...@utoronto.ca wrote: Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
CURRENT insert ID
Here it is in a nutshell: I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don’t know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE? Here’s a more concrete description of the problem: CREATE TABLE t ( id INT(11) AUTO-INCREMENT PRIMARY, xxx INT(11) ); When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.) Is there anything clever I can do? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com
RE: best way to have a unique key
-Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, January 21, 2011 11:35 AM To: Johan De Meersman Cc: Anthony Pace; mysql. Subject: Re: best way to have a unique key One of the components of the UUID is drawn form the mac address of the server.. While in practice this is not true of all systems (except from http://dev.mysql.com/doc/refman/5.0/en/miscellaneous- functions.html#function_uuid) Currently, the MAC address of an interface is taken into account only on FreeBSD and Linux. On other operating systems, MySQL uses a randomly generated 48-bit number. (end except) that potentially 48-bit random portion has 281474976710656 possibilities, which makes in far, far more likely that your server is hit by a meteor during a snowstorm in August while the Dalai Lama is doing an Elvis impression for the Chinese premier. [JS] Boy, if I had a nickel for every time I've heard that... Seriously, though I've written and used enough pseudo-random number generators to have a tiny nagging doubt. I didn't realize that MySQL didn't use the MAC address on Windows platforms. Also, it is possible to programmatically change a MAC address (for system failover, for example). If you're not the kind of person who stays up nights worrying about proton decay, you're probably justified in shrugging this off. You'll likely be collecting your pension before anything nasty happens. - michael dykman On Fri, Jan 21, 2011 at 1:22 AM, Johan De Meersman vegiv...@tuxera.be wrote: I have to say, something similar was my first thought, too - you never mention uuid in your original post. As already stated, uuid() should be a Universal Unique IDentifier. It's afaik a random 128-bit number; given the space to choose from it should be rather unique. I have to admit that I'm not entirely confident about that myself, either, though: as Pratchett put it, one-in-a-million chances tend to pop up nine times out of ten. The code should have bits for handling duplicate primaries regardless of the method used to generate it, tough, so there's no reason to not do it. Having two subsequent UUID() calls generate pre-existing numbers seems to me to be likely in the same way as having Bush return his dirty oil dollars to Irak. On Thu, Jan 20, 2011 at 8:10 PM, Anthony Pace anthony.p...@utoronto.cawrote: Dude, come on. I know that all primary keys have to be unique; however, I was obviously referring to the use of uuid over auto incrementation. On 1/20/2011 1:36 PM, Michael Dykman wrote: It is axiomatic in the relational model that a primary must be unique. This is not a quirk put forth by your current employer. Neither MySQL nor any other RDBMS will allow you to establish a primary key that is not unique. - michael dykman On Thu, Jan 20, 2011 at 1:32 PM, Anthony Paceanthony.p...@utoronto.ca wrote: Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CURRENT insert ID
I think an ON INSERT TRIGGER would take care of this; can't think of any other way. Using last_insert_id() in the argument list would likely yield you the previous value (which might not even related to your table. Having siad that.. odd requirement. - michael dykman ps -- sorry for the duplicate Jerry, reply-to policy on this list is forever tripping me up. On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz je...@gii.co.jp wrote: Here it is in a nutshell: I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don’t know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE? Here’s a more concrete description of the problem: CREATE TABLE t ( id INT(11) AUTO-INCREMENT PRIMARY, xxx INT(11) ); When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.) Is there anything clever I can do? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: CURRENT insert ID
-Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, January 21, 2011 11:50 AM To: MySql Subject: Re: CURRENT insert ID I think an ON INSERT TRIGGER would take care of this; can't think of any other way. Using last_insert_id() in the argument list would likely yield you the previous value (which might not even related to your table. Having siad that.. odd requirement. [JS] You don't know the half of it. Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com - michael dykman ps -- sorry for the duplicate Jerry, reply-to policy on this list is forever tripping me up. On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz je...@gii.co.jp wrote: Here it is in a nutshell: I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don't know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE? Here's a more concrete description of the problem: CREATE TABLE t ( id INT(11) AUTO-INCREMENT PRIMARY, xxx INT(11) ); When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.) Is there anything clever I can do? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: CURRENT insert ID
-Original Message- From: Jerry Schwartz [mailto:je...@gii.co.jp] Sent: Friday, January 21, 2011 11:56 AM To: 'Michael Dykman'; 'MySql' Subject: RE: CURRENT insert ID -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, January 21, 2011 11:50 AM To: MySql Subject: Re: CURRENT insert ID I think an ON INSERT TRIGGER would take care of this; can't think of any other way. Using last_insert_id() in the argument list would likely yield you the previous value (which might not even related to your table. [JS] Alas, you cannot update a record in a trigger if the record is in the same table as the trigger. CREATE TRIGGER xx AFTER UPDATE ON t FOR EACH ROW UPDATE t SET f1 = 7; That's illegal. Right now, I'm stumped. Having siad that.. odd requirement. [JS] You don't know the half of it. Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com - michael dykman ps -- sorry for the duplicate Jerry, reply-to policy on this list is forever tripping me up. On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz je...@gii.co.jp wrote: Here it is in a nutshell: I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don't know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE? Here's a more concrete description of the problem: CREATE TABLE t ( id INT(11) AUTO-INCREMENT PRIMARY, xxx INT(11) ); When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.) Is there anything clever I can do? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: CURRENT insert ID
I made a typo in my previous message. -Original Message- From: Jerry Schwartz [mailto:je...@gii.co.jp] Sent: Friday, January 21, 2011 12:20 PM To: 'Jerry Schwartz'; 'Michael Dykman'; 'MySql' Subject: RE: CURRENT insert ID -Original Message- From: Jerry Schwartz [mailto:je...@gii.co.jp] Sent: Friday, January 21, 2011 11:56 AM To: 'Michael Dykman'; 'MySql' Subject: RE: CURRENT insert ID -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, January 21, 2011 11:50 AM To: MySql Subject: Re: CURRENT insert ID I think an ON INSERT TRIGGER would take care of this; can't think of any other way. Using last_insert_id() in the argument list would likely yield you the previous value (which might not even related to your table. [JS] Alas, you cannot update a record in a trigger if the record is in the same table as the trigger. CREATE TRIGGER xx AFTER UPDATE ON t FOR EACH ROW UPDATE t SET f1 = 7; [JS] That should have read AFTER INSERT; but it's still illegal. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CURRENT insert ID
I can´t think about how useful for you would be to have two fields with the same value. -- João Cândido de Souza Neto Jerry Schwartz je...@gii.co.jp escreveu na mensagem news:007501cbb98a$177acba0$467062e0$@co.jp... Here it is in a nutshell: I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don't know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE? Here's a more concrete description of the problem: CREATE TABLE t ( id INT(11) AUTO-INCREMENT PRIMARY, xxx INT(11) ); When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.) Is there anything clever I can do? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.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: CURRENT insert ID
@Joao - I'm currently building a database out right now that has this scenario. One field can be the primary key, that has a purpose for holding the record id, another field can hold the value. Let say there are two fields, id, s_id. Initially, you insert a record and `id` is now 100 and you update s_id to be 100. But for whatever reason, later down the road you need s_id to be 200. You can just update the s_id field instead of deleting the entire record and inserting an entire new one with X amount of fields. Updating one field is a lot less work than deleting and inserting. I have my tables set up so I won't have to use the primary key for queries, I will only use the s_id field. 2011/1/21 João Cândido de Souza Neto j...@consultorweb.cnt.br I can´t think about how useful for you would be to have two fields with the same value. -- João Cândido de Souza Neto Jerry Schwartz je...@gii.co.jp escreveu na mensagem news:007501cbb98a$177acba0$467062e0$@co.jp... Here it is in a nutshell: I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don't know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE? Here's a more concrete description of the problem: CREATE TABLE t ( id INT(11) AUTO-INCREMENT PRIMARY, xxx INT(11) ); When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.) Is there anything clever I can do? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- -- May the Source be with you.
Re: CURRENT insert ID
Ok, you must have your own reasons to do that. The fact is: You can´t set the auto_incremente value field to another field in the same table and record even in a trigger. So, the best way is a second update. -- João Cândido de Souza Neto Darryle Steplight dstepli...@gmail.com escreveu na mensagem news:AANLkTim+fjYUoU+1A5RG9eFS+NnaZXT7K+Ho-q=f-...@mail.gmail.com... @Joao - I'm currently building a database out right now that has this scenario. One field can be the primary key, that has a purpose for holding the record id, another field can hold the value. Let say there are two fields, id, s_id. Initially, you insert a record and `id` is now 100 and you update s_id to be 100. But for whatever reason, later down the road you need s_id to be 200. You can just update the s_id field instead of deleting the entire record and inserting an entire new one with X amount of fields. Updating one field is a lot less work than deleting and inserting. I have my tables set up so I won't have to use the primary key for queries, I will only use the s_id field. 2011/1/21 João Cândido de Souza Neto j...@consultorweb.cnt.br I can´t think about how useful for you would be to have two fields with the same value. -- João Cândido de Souza Neto Jerry Schwartz je...@gii.co.jp escreveu na mensagem news:007501cbb98a$177acba0$467062e0$@co.jp... Here it is in a nutshell: I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don't know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE? Here's a more concrete description of the problem: CREATE TABLE t ( id INT(11) AUTO-INCREMENT PRIMARY, xxx INT(11) ); When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.) Is there anything clever I can do? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Fri, Jan 21, 2011 at 4:44 AM, Dotan Cohen dotanco...@gmail.com wrote: Then I would have to check what values are available when inserting, and possibly normalise every so often. I'll think about that, and when I have enough data in the database I'll set up a test system to play with the possibility. Yes, it's a much more complicated process, and one that I wouldn't enter into without verifying there was a performance problem. The neat thing is you could do this at any point as you can renumber the left and right values at will--as long as you don't *reorder* them. I see, thanks. Good point about making sure that the problem exists before trying to fix it, I've seen people optimise away where there is no bottleneck. People who love to work on complicated problems often fall victim to premature optimization because it's fun to do. I was guilty of this myself earlier in my career, but as CPUs and memory and disk speeds increased over the decades, it's harder to justify it. At least back then the optimization that wasn't *necessary* still had a good payoff. Nowadays you can waste days shaving 10ms off of an operation that takes 1s. Congratulations, you've just poured $$ down the drain. But boy wasn't that fun! :) Good luck with the implementation. I'd love to hear how it pans out once you're done, and I'm sure the list would find it useful as well. David
Re: best way to have a unique key
On Friday, January 21, 2011 09:23:47 am Jerry Schwartz wrote: [JS] A UUID (what Microsoft calls a GUID) is based in part on the MAC address of the generating device. Since MAC addresses are supposed to be unique across the known universe, so should a UUID. Not entirely true - and even when true your conclusion doesn't follow. I'll be the first to admit that we are straining at gnats, here. Generating a UUID does not require a machine to have an Ethernet card. The presence of a Mac address is, therefore, not guaranteed. Even if the machine does have an ethernet card, the definition of the UUID suggests - but does not require - that it be used (I believe this is discussed in another reply). Even if it is used, there are still a (admittedly large) portion of it that is created by a random number generation. Assuming a reasonably good generator, it is still possible for a collision on a single machine. The question then becomes, how much of a problem is that? The odds are that I'll be LONG gone before that happens. But it could happen tomorrow. If it's just a matter of Oops, lets do a rollback and go again, who cares? If it's a life sustaining mission critical problem where an atomic war starts if the transaction fails ... you might want to at least think about another key. Personally, I wouldn't lose sleep over it. Another reasonable - and faster - source of a unique key would be to have a human set unique server id on each machine generating the key. In that case a two field primary key with the first being the server id and the second part being an auto_increment field should guarantee a unique key - at least until it wraps around. This, of course, assumes that the administrator that sets up the servers doesn't make a mistake with setting the server id. Which brings up the how much of a problem would that be question again. Bottom line ... exactly how paranoid are you? ---Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CURRENT insert ID
You don't need to do an update: ... new.xxx = new.id ... On Fri, Jan 21, 2011 at 12:20 PM, Jerry Schwartz je...@gii.co.jp wrote: -Original Message- From: Jerry Schwartz [mailto:je...@gii.co.jp] Sent: Friday, January 21, 2011 11:56 AM To: 'Michael Dykman'; 'MySql' Subject: RE: CURRENT insert ID -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, January 21, 2011 11:50 AM To: MySql Subject: Re: CURRENT insert ID I think an ON INSERT TRIGGER would take care of this; can't think of any other way. Using last_insert_id() in the argument list would likely yield you the previous value (which might not even related to your table. [JS] Alas, you cannot update a record in a trigger if the record is in the same table as the trigger. CREATE TRIGGER xx AFTER UPDATE ON t FOR EACH ROW UPDATE t SET f1 = 7; That's illegal. Right now, I'm stumped. Having siad that.. odd requirement. [JS] You don't know the half of it. Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com - michael dykman ps -- sorry for the duplicate Jerry, reply-to policy on this list is forever tripping me up. On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz je...@gii.co.jp wrote: Here it is in a nutshell: I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don't know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE? Here's a more concrete description of the problem: CREATE TABLE t ( id INT(11) AUTO-INCREMENT PRIMARY, xxx INT(11) ); When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.) Is there anything clever I can do? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Slow query on MySQL4 server doing simple inner join of two InnoDB tables
Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) These track information about email messages. Each message has many recipient records. The structure of the two tables (omitting irrelevant data fields) are as follows: +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | sent_at | datetime | | MUL | -00-00 00:00:00 || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | message_id | int(10) unsigned | | MUL | 0 || | employee_id | int(10) unsigned | YES | MUL | NULL || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. The query explanation is as follows: ++-+++--+--+-+-++--+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra| ++-+++--+--+-+-++--+ | 1 | SIMPLE | recipients | ref| messages_fk,employee_idx | employee_idx | 5 | const | 222640 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | messages | eq_ref | PRIMARY | PRIMARY | 4 | email_archive.recipients.message_id | 1 | | ++-+++--+--+-+-++--+ I've been doing some searching on the web and have no idea if/how this can be sped up. Most searches these days reference MySQL 5.x which I'm just not sure how much applies. I'm hoping that there is something obvious that I'm missing, or that one of you experts knows what I might be able to change to speed this query up. Anyhow, thanks in advance for even so much as reading my message, let alone replying :). -- Kendall Gifford zettab...@gmail.com
Is is possible to update a column based on a REGEXP on another column?
I have a table which contains a username column which may be constructed something like somename[A] or [DDD]someothername The A or DDD can be anything at all. I've added a new column to the table to which I'd like to populate with the value within the square brackets. I could write something in perl or php to run through each and update them but was wondering if there is a way to do this within mysql itself? The regexp only returns a boolean so I can't see how to use that. Regards Phil -- Distributed Computing stats http://stats.free-dc.org
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
you need hughe ram / innodb_buffer_pool for large datasets in a perfect world the buffer_pool is as large as the data how looks your current config? how much RAM has the machine? Am 21.01.2011 20:21, schrieb Kendall Gifford: Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) These track information about email messages. Each message has many recipient records. The structure of the two tables (omitting irrelevant data fields) are as follows: +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | sent_at | datetime | | MUL | -00-00 00:00:00 || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | message_id | int(10) unsigned | | MUL | 0 || | employee_id | int(10) unsigned | YES | MUL | NULL || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. The query explanation is as follows: ++-+++--+--+-+-++--+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra| ++-+++--+--+-+-++--+ | 1 | SIMPLE | recipients | ref| messages_fk,employee_idx | employee_idx | 5 | const | 222640 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | messages | eq_ref | PRIMARY | PRIMARY | 4 | email_archive.recipients.message_id | 1 | | ++-+++--+--+-+-++--+ I've been doing some searching on the web and have no idea if/how this can be sped up. Most searches these days reference MySQL 5.x which I'm just not sure how much applies. I'm hoping that there is something obvious that I'm missing, or that one of you experts knows what I might be able to change to speed this query up. Anyhow, thanks in advance for even so much as reading my message, let alone replying :). -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ signature.asc Description: OpenPGP digital signature
RE: CURRENT insert ID
-Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Friday, January 21, 2011 12:47 PM To: mysql@lists.mysql.com Subject: Re: CURRENT insert ID Ok, you must have your own reasons to do that. The fact is: You can´t set the auto_incremente value field to another field in the same table and record even in a trigger. So, the best way is a second update. [JS] That's what I'm trying to do now, but I'm using MS Access through an ODBC connection and I haven't figured out how to retrieve last_insert_id. I should ask in the myodbc forum. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- João Cândido de Souza Neto Darryle Steplight dstepli...@gmail.com escreveu na mensagem news:AANLkTim+fjYUoU+1A5RG9eFS+NnaZXT7K+Ho-q=f-...@mail.gmail.com... @Joao - I'm currently building a database out right now that has this scenario. One field can be the primary key, that has a purpose for holding the record id, another field can hold the value. Let say there are two fields, id, s_id. Initially, you insert a record and `id` is now 100 and you update s_id to be 100. But for whatever reason, later down the road you need s_id to be 200. You can just update the s_id field instead of deleting the entire record and inserting an entire new one with X amount of fields. Updating one field is a lot less work than deleting and inserting. I have my tables set up so I won't have to use the primary key for queries, I will only use the s_id field. 2011/1/21 João Cândido de Souza Neto j...@consultorweb.cnt.br I can´t think about how useful for you would be to have two fields with the same value. -- João Cândido de Souza Neto Jerry Schwartz je...@gii.co.jp escreveu na mensagem news:007501cbb98a$177acba0$467062e0$@co.jp... Here it is in a nutshell: I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don't know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE? Here's a more concrete description of the problem: CREATE TABLE t ( id INT(11) AUTO-INCREMENT PRIMARY, xxx INT(11) ); When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.) Is there anything clever I can do? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: CURRENT insert ID
-Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Friday, January 21, 2011 1:27 PM To: Jerry Schwartz Cc: MySql Subject: Re: CURRENT insert ID You don't need to do an update: ... new.xxx = new.id ... [JS] I wish it were that easy. new.id is null until after the INSERT has completed: SHOW CREATE TABLE xxx\G *** 1. row ** Table: xxx Create Table: CREATE TABLE `xxx` ( `id` int(11) NOT NULL AUTO_INCREMENT, `vv` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 SHOW CREATE TRIGGER foo\G *** 1. row *** Trigger: foo sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`access`@`%` TRIGGER foo BEFORE UPDATE ON xxx FOR EACH ROW SET NEW.vv = NEW.id character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci INSERT INTO xxx VALUES (NULL,NULL); SELECT * FROM xxx; ++--+ | id | vv | ++--+ | 1 | NULL | ++--+ 1 row in set (0.00 sec) I'm tearing my hair out. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
On 1/21/2011 14:21, Kendall Gifford wrote: Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) These track information about email messages. Each message has many recipient records. The structure of the two tables (omitting irrelevant data fields) are as follows: +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | sent_at | datetime | | MUL | -00-00 00:00:00 || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | message_id | int(10) unsigned | | MUL | 0 || | employee_id | int(10) unsigned | YES | MUL | NULL || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. The query explanation is as follows: ++-+++--+--+-+-++--+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra| ++-+++--+--+-+-++--+ | 1 | SIMPLE | recipients | ref| messages_fk,employee_idx | employee_idx | 5 | const | 222640 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | messages | eq_ref | PRIMARY | PRIMARY | 4 | email_archive.recipients.message_id | 1 | | ++-+++--+--+-+-++--+ I've been doing some searching on the web and have no idea if/how this can be sped up. Most searches these days reference MySQL 5.x which I'm just not sure how much applies. I'm hoping that there is something obvious that I'm missing, or that one of you experts knows what I might be able to change to speed this query up. Anyhow, thanks in advance for even so much as reading my message, let alone replying :). You need to get rid of the GROUP BY to make this go faster. You can do that by running two queries, one to pick the list of unique recipients.message_id values that match your where condition then another to actually retrieve the message data. Something like this CREATE TEMPORARY TABLE tmpMessages (message_id INT UNSIGNED, PRIMARY KEY (message_id)) ENGINE=MEMORY; INSERT IGNORE tmpMessages SELECT message_id FROM recipients WHERE employee_id = X; SELECT messages.* FROM messages INNER JOIN tmpMessages ON tmpMessages.message_id = messages.id ORDER BY sent_at DESC LIMIT 0, 25; By pre-selecting a limited set of message_id values from the recipients table, you seriously reduce the number of rows that need to be scanned. Also, the INSERT IGNORE technique is faster than the GROUP BY because it uses an index to identify any duplicates instead of a scan of all previous unique values. Please let us all know if this is faster enough. (and don't forget to drop the temp table once you are through using it) -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
On Fri, Jan 21, 2011 at 2:01 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 1/21/2011 14:21, Kendall Gifford wrote: Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) These track information about email messages. Each message has many recipient records. The structure of the two tables (omitting irrelevant data fields) are as follows: +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | sent_at | datetime | | MUL | -00-00 00:00:00 || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | message_id | int(10) unsigned | | MUL | 0 || | employee_id | int(10) unsigned | YES | MUL | NULL || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. The query explanation is as follows: ++-+++--+--+-+-++--+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra| ++-+++--+--+-+-++--+ | 1 | SIMPLE | recipients | ref| messages_fk,employee_idx | employee_idx | 5 | const | 222640 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | messages | eq_ref | PRIMARY | PRIMARY | 4 | email_archive.recipients.message_id | 1 | | ++-+++--+--+-+-++--+ I've been doing some searching on the web and have no idea if/how this can be sped up. Most searches these days reference MySQL 5.x which I'm just not sure how much applies. I'm hoping that there is something obvious that I'm missing, or that one of you experts knows what I might be able to change to speed this query up. Anyhow, thanks in advance for even so much as reading my message, let alone replying :). You need to get rid of the GROUP BY to make this go faster. You can do that by running two queries, one to pick the list of unique recipients.message_id values that match your where condition then another to actually retrieve the message data. Something like this CREATE TEMPORARY TABLE tmpMessages (message_id INT UNSIGNED, PRIMARY KEY (message_id)) ENGINE=MEMORY; INSERT IGNORE tmpMessages SELECT message_id FROM recipients WHERE employee_id = X; SELECT messages.* FROM messages INNER JOIN tmpMessages ON tmpMessages.message_id = messages.id ORDER BY sent_at DESC LIMIT 0, 25; By pre-selecting a limited set of message_id values from the recipients table, you seriously reduce the number of rows that need to be scanned. Also, the INSERT IGNORE technique is faster than the GROUP BY because it uses an index to identify any duplicates instead of a scan of all previous unique values. Please let us all know if this is faster enough. (and don't forget to drop the temp table once you are through using it) -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN Thanks Shawn, I'm in the process of trying you're suggestion now. I'll let you know how
Re: CURRENT insert ID
Just an idear.. Don't auto_increment the main table.. create a unique Id table, auto_increment that, and grab that value first for use with both fields in your main table. Donovan -- D Brooke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CURRENT insert ID
Hi,
On 22/01/2011, at 11:27 AM, Donovan Brooke wrote:
Just an idear..
Don't auto_increment the main table.. create a unique Id table,
auto_increment that, and grab that value first for use with both fields in
your main table.
This can be wrapped into a trigger, so the main table functions as usual:
CREATE TABLE _sequence (
Name varchar(20) NOT NULL PRIMARY KEY,
Value INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;
CREATE TABLE dupkey (
id INT UNSIGNED NOT NULL DEFAULT 0 PRIMARY KEY,
DupKey INT UNSIGNED NOT NULL DEFAULT 0,
Value VARCHAR(20) NOT NULL DEFAULT ''
) ENGINE=InnoDB;
INSERT INTO _sequence
VALUES ('dupkey', 0);
DELIMITER //
CREATE TRIGGER befins_dupkey BEFORE INSERT ON dupkey FOR EACH ROW
BEGIN
DECLARE v_id INT UNSIGNED;
UPDATE _sequence SET Value = (LAST_INSERT_ID(Value+1)) where name = 'dupkey';
SET NEW.id := LAST_INSERT_ID(),
NEW.DupKey := LAST_INSERT_ID();
END//
DELIMITER ;
INSERT INTO dupkey (Value)
VALUES ('test 1'), ('test 2');
SELECT * FROM dupkey;
++++
| id | DupKey | Value |
++++
| 1 | 1 | test 1 |
| 2 | 2 | test 2 |
++++
2 rows in set (0.00 sec)
Cheers,
Jesper
Donovan
--
D Brooke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@wisborg.dk
