Re: Reply-to is to originator rather than to list
Hi Rob, I agree this is a problem for one major reason: I never know, if someone replies to my post, without the CC to the list, whether the writer wants to take the conversation off the list, or if he just hit reply (instead of reply-to-all) out of habit. If the default is to reply to the list and CC to the originator, then it is obvious when a reply does not go to the list that the person that replied does not want his response made public! On Oct 21, 2007, at 9:18 PM, Rob Wultsch wrote: I was previously on a list where the reply-to was setup as it is on the mysql list, with the originator receiving a response rather than list. It ended up that that setting was the default, and had not been changed when the list was setup. Is there a good reason why the reply-to is setup as it is on this list? I forget to change the destination address for most every email I write, I would guess I am not alone, and I do not think that this is good for the list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a scheme for the future mysql-protocol, Re: [ANN] New PBXT/MyBS release enables JDBC-based BLOB streaming!
Hi Chad, Yes, I agree. This is a really good idea. I think that it makes a lot of sense to make streaming part of the standard MySQL protocol. Your suggestion solves the problem that always comes with a transition to a new protocol. On Oct 19, 2007, at 6:20 PM, Chad MILLER wrote: On 19 Oct 2007, at 04:35, Paul McCullagh wrote: For this release I have completed changes to the MySQL Connector/J 5.0.7, to allow BLOB data to be transparently stored and retrieved from the MyBS BLOB repository. The new version of the driver is called MySQL Connector/J SE (streaming enabled). That's a beautiful hack, Paul. Of course, you know what I'm going to say, but most of your readers weren't at MySQLCamp-US-East where I proposed it: We can use this experience to change mysql-proxy, as a stepping stone to making the next mysql protocol (labled p+1 below) that has (at least) streaming built-in: now: client - server next: client (p+1) - proxy=server + mybs : client - proxy-server (p+1) : client (p+1)- server (p+1) so, older versions of client and server could speak to newer versions of server and client, perhaps with proxy as an intermediary. Alas, this is a long-term dream. I'm not proposing it for anyone soon, but it's fun to think about. - chad -- Chad Miller, Software Developer [EMAIL PROTECTED] MySQL Inc., www.mysql.com Orlando, Florida, USA13-20z, UTC-0400 Office: +1 408 213 6740 sip:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
High Traffic Sites
A colleague and I are having a discussion about the best way to handle high traffic sites. For example, take a social networking site with 1 million users. I think it would be better to have multiple web servers with multiple database servers in a master/slave scenario with replication. My colleague supports the idea of clustering, with MySQL running on the same box as the web server. From a MySQL standpoint, are there any best practices for building high traffic sites with a MySQL back end? Neither my colleague nor I are DBA's, nor are we sys admin experts. Any thoughts are most welcome. -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need ideas on handling aliases and a.k.a.s
Thinking about his very briefly, I suspect you need two tables and a flag. The first table would be something like person: === person_id other unique information, such as pointer to image, date of birth, etc. names: == person_id is_primary_name (Boolean) name For each person, you'd have one or more names with the one you want as the working name flagged. To retrieve a person, you'd search on names.name and might present the user with a list of choices, primary name first, sorted by person_id to group the alternative names together. Another presentation would be to group by person_id and use GROUP_CONCAT to present all of the person's names as one field. That might be easier for the user, rather than presenting a list to choose from. If a search turns up more than one person (as a search for Pamela might), then grouping by person_id gives the user the option of choosing the person they are looking for. When presenting the image, filmography, or such, you'd use the primary name. To change the primary name, your management interface would present each name separately with a checkbox or some such. Conceptually this seems reasonable to me, but I'm interested to see what others have come up with. 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 -Original Message- From: Ian M. Evans [mailto:[EMAIL PROTECTED] Sent: Friday, October 19, 2007 9:04 PM To: mysql@lists.mysql.com Subject: Need ideas on handling aliases and a.k.a.s I'm trying to wrap my head around dealing with people in a table that have multiple names or akas. I run an entertainment news site and have to deal with people like Pamela Anderson (who was once Pamela Lee, Pamela Anderson Lee, Pamela Denise Anderson), Eva Longoria (who's now Eva Longoria Parker) and Courteney Cox, who's Courteney Cox Arquette. I haven't really dealt with this yet, but I guess now I better handle it before I get stung too badly. Right now I have a people table that has: PeopleID First Middle Last Display URL So as an example you'd have: PeopleID: 1078 First: Eva Middle: Last: Longoria Display: Eva Longoria URL: evalongoria It's worked well for me. I have a peopleinphotos table...add Eva to a photo caption and it's just a matter of grabbing her id number (1078) and putting it in the table with the photoid #. She gets nominated, the input form looks up her id# and adds it to the nomination table. I've been lucky in that most entertainers keep their public and personal names separate. But suddenly Eva wants her credits to read Eva Longoria Parker. Sure I can add Parker to the Last field and remember to always use Longoria Parker when I input new info, but what happens if she gets divorced? Just wondering how some of you have handled akas/aliases/divorces for things like customer databases. How do you ensure that a name change doesn't actually cause a brand new record for the person if the data entry person uses the old name, etc. Thanks for any advice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Traffic Sites
I highly recommend you hire a consultant or a freelance DBA to try to work this out. You are talking about something pretty extensive. I would recommend replication across quite a few high-end servers (all 64 bit of course with plenty of RAM). Clustering can be a pretty big headache. A lot of this is going to depend on what you see the most of (reads/writes? etc.). You are correct in your thoughts, though, about having multiple database servers A project like this will most likely require much more initial information and evaluations to find out a solution suitable to your needs. On 10/22/07, Erich C. Beyrent [EMAIL PROTECTED] wrote: A colleague and I are having a discussion about the best way to handle high traffic sites. For example, take a social networking site with 1 million users. I think it would be better to have multiple web servers with multiple database servers in a master/slave scenario with replication. My colleague supports the idea of clustering, with MySQL running on the same box as the web server. From a MySQL standpoint, are there any best practices for building high traffic sites with a MySQL back end? Neither my colleague nor I are DBA's, nor are we sys admin experts. Any thoughts are most welcome. -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Traffic Sites
Craig, Thank you for your insight. Having never worked with clustering solutions, what kinds of headaches might one see with that solution? Does anyone know of any white papers that might shed some light into the pros and cons of each solution? -Erich- Craig Huffstetler wrote: I highly recommend you hire a consultant or a freelance DBA to try to work this out. You are talking about something pretty extensive. I would recommend replication across quite a few high-end servers (all 64 bit of course with plenty of RAM). Clustering can be a pretty big headache. A lot of this is going to depend on what you see the most of (reads/writes? etc.). You are correct in your thoughts, though, about having multiple database servers A project like this will most likely require much more initial information and evaluations to find out a solution suitable to your needs. On 10/22/07, Erich C. Beyrent [EMAIL PROTECTED] wrote: A colleague and I are having a discussion about the best way to handle high traffic sites. For example, take a social networking site with 1 million users. I think it would be better to have multiple web servers with multiple database servers in a master/slave scenario with replication. My colleague supports the idea of clustering, with MySQL running on the same box as the web server. From a MySQL standpoint, are there any best practices for building high traffic sites with a MySQL back end? Neither my colleague nor I are DBA's, nor are we sys admin experts. Any thoughts are most welcome. -Erich- -- 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: High Traffic Sites
Hi, There are a lot of clustering you techniques you can use. Read on livejournal attack on this problem. You need to start from the schema poorly constructed schema will kill your hardware. Go to Mysql Site search for whitepapers and friendster (I'm not saying it the best implementation but it will give you an idea.) Hth, -Original Message- From: Erich C. Beyrent [mailto:[EMAIL PROTECTED] Sent: Monday, October 22, 2007 10:10 AM To: MySQL General Subject: Re: High Traffic Sites Craig, Thank you for your insight. Having never worked with clustering solutions, what kinds of headaches might one see with that solution? Does anyone know of any white papers that might shed some light into the pros and cons of each solution? -Erich- Craig Huffstetler wrote: I highly recommend you hire a consultant or a freelance DBA to try to work this out. You are talking about something pretty extensive. I would recommend replication across quite a few high-end servers (all 64 bit of course with plenty of RAM). Clustering can be a pretty big headache. A lot of this is going to depend on what you see the most of (reads/writes? etc.). You are correct in your thoughts, though, about having multiple database servers A project like this will most likely require much more initial information and evaluations to find out a solution suitable to your needs. On 10/22/07, Erich C. Beyrent [EMAIL PROTECTED] wrote: A colleague and I are having a discussion about the best way to handle high traffic sites. For example, take a social networking site with 1 million users. I think it would be better to have multiple web servers with multiple database servers in a master/slave scenario with replication. My colleague supports the idea of clustering, with MySQL running on the same box as the web server. From a MySQL standpoint, are there any best practices for building high traffic sites with a MySQL back end? Neither my colleague nor I are DBA's, nor are we sys admin experts. Any thoughts are most welcome. -Erich- -- 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] This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom it is addressed. It may contain sensitive and private proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you are not the intended recipient, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. FXDirectDealer, LLC reserves the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal confirmation. FXDirectDealer, LLC is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--shared-memory, ??
Hi friends, I'm read somethings about the MySQL works with the option --shared-memory on Windows and about this I have some doubts. Anybody here know explain what are the chages compered without the parameter? Thk`s... Wagner Bianchi Diretor de Tecnologia - INFODBA Technologies Consulting [EMAIL PROTECTED] - (31) 3272 - 0226 / 8427 - 8803 Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
Res: german datetime format?
You can treat it with DATE_FORMAT() and TIME_FORMAT(), change the format of MySQL variable, don't have way. You have to make a explicity convertion. ;-) Wagner Bianchi Diretor de Tecnologia - INFODBA Technologies Consulting [EMAIL PROTECTED] - (31) 3272 - 0226 / 8427 - 8803 - Mensagem original De: Baron Schwartz [EMAIL PROTECTED] Para: Ralf Hüsing [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Enviadas: Sexta-feira, 19 de Outubro de 2007 22:56:02 Assunto: Re: german datetime format? Ralf Hüsing wrote: Hi, can i change the datetime format on mysql in a german format? At the moment the dates are stored like 2007-10-19 19:06:17 but if i send a query (which comes from user input) the query looks like WHERE Datum = '19.10.2007' and i got not what i want. iam using mysql (5.0.45) on (german) windows 2000, clients are connected via ODBC-Driver (3.51.21.00) and the application is using ADODB (mdac-lastest version). Try converting the user input to the correct type with STR_TO_DATE(), which despite its name can return a DATETIME value. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
group by two cols 1st desc and 3nd asc
I have a query to extract some data. Two columns include a setup date (setupdt) and a completed data (compdt). I would like the data grouped to get sub-totals but shown in different orders/ I would like the compdt with the newest first i.e. desc order and setup date with the oldest first i.e. asc order I have tried various combinations of SELECT . WHERE . GROUP BY compdt DESC, setupdt ASC Or I have GROUP BY with ORDER BY mixed with various combinations. I have yet to get the correct results. I am using 5.0.45 under WAMP. Can someone tell me if what I want is possible? Kerry
Replication still stopping...
I tried posting this on the Replication list, and got no response. Maybe someone here can help... OK. Still battling this issue after weeks of working with it. I'm racking my brains. I re-set the slave again on Saturday, and got replication started again. It was working fine until this afternoon some time. Before starting things up, I cleaned the error log out completely, so it would be clean before I started. Here is my error log in total: 071020 14:43:51 InnoDB: Started; log sequence number 0 142497221 071020 14:43:51 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.45-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 071020 14:43:51 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.06' at position 98, relay log 'C:\Program Files\MySQL\MySQL Server 5.0\Data\dlgsrv-relay-bin.02' position: 235 071020 14:43:52 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.06' at position 98 071020 15:43:32 [Note] Slave: received end packet from server, apparent master shutdown: 071020 15:43:32 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.06' position 98 071020 15:43:33 [ERROR] Slave I/O thread: error reconnecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'webserver' (10061)' errno: 2003 retry-time: 60 retries: 86400 071020 15:45:56 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.06' at position 98 071021 15:02:21 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.07' at position 195 I checked periodically on the server, and everything seemed to be working. The last time I checked was this morning sometime around 8:00 pr so. Still running. As you can see, however, it juststopped processing at 15:02:21 this afternoon. The master server was not down. I was in and out of web sites that use the MySQL database on the master several times, and it always worked just fine, and never gave me an error. It almost appears as though the slave cannot communicate with the master. It looks like it tried 86,400 times, which I guess took almost a day to do, and just gave up. Why would it be able to connect initially to the server, then suddenly not be able to connect any more? Any help or suggestions anyone can offer is greatly appreciated! Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication still stopping...
Hi Jesse, Jesse wrote: I tried posting this on the Replication list, and got no response. Maybe someone here can help... OK. Still battling this issue after weeks of working with it. I'm racking my brains. I re-set the slave again on Saturday, and got replication started again. It was working fine until this afternoon some time. Before starting things up, I cleaned the error log out completely, so it would be clean before I started. Here is my error log in total: 071020 14:43:51 InnoDB: Started; log sequence number 0 142497221 071020 14:43:51 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.45-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 071020 14:43:51 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.06' at position 98, relay log 'C:\Program Files\MySQL\MySQL Server 5.0\Data\dlgsrv-relay-bin.02' position: 235 071020 14:43:52 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.06' at position 98 071020 15:43:32 [Note] Slave: received end packet from server, apparent master shutdown: 071020 15:43:32 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.06' position 98 071020 15:43:33 [ERROR] Slave I/O thread: error reconnecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'webserver' (10061)' errno: 2003 retry-time: 60 retries: 86400 071020 15:45:56 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'mysql-bin.06' at position 98 071021 15:02:21 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.07' at position 195 I checked periodically on the server, and everything seemed to be working. The last time I checked was this morning sometime around 8:00 pr so. Still running. As you can see, however, it juststopped processing at 15:02:21 this afternoon. The master server was not down. I was in and out of web sites that use the MySQL database on the master several times, and it always worked just fine, and never gave me an error. It almost appears as though the slave cannot communicate with the master. It looks like it tried 86,400 times, which I guess took almost a day to do, and just gave up. Why would it be able to connect initially to the server, then suddenly not be able to connect any more? A couple of thoughts. Do you have slaves with duplicated server IDs? That seems most likely to me. If that's not it, is the max_packet_size mismatched on the master and slave? Can you connect to the master and view the binary log event at the position it's trying to read, with SHOW BINLOG EVENTS? Can you use the mysqlbinlog tool to verify that the binary log isn't corrupted on the master? Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OT] Memory Usage on Windows? Re: Replication still stopping...
Hi Jesse, 071020 14:43:51 InnoDB: Started; log sequence number 0 142497221 071020 14:43:51 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. as i can see you are running mysql on windows. If i start my db server (5.0.45/innodb/win2k) the server uses about ~80K handles (as seen in taskmgr) and memory usage increases around 1g. Taskmgr.exe says that there is some swapping (the box has only 1gb ram). The DB itself is small (~50mb or so). My Question is, did you have the same things on your box? Did you have performace issues which resultes from the memory usage? Thanks Ralf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reply-to is to originator rather than to list
I've seen this debate on a lot of lists. I firmly believe having a list munge reply-to is almost universally a very bad idea (the main exception being very small lists of people who know each other). Most email programs allow you to tell them the names of the lists you subscribe to, and/or can autodetect what the list name is from the appropriate headers, and give you an easy list-reply command. So you have your usual individual reply command, group reply / reply to all, and list reply. If you're annoyed by having to edit headers to reply to the list, then learn how to use your email program's list-reply command. Yes, sometimes someone accidentally replies to you and not the list. You write them back and ask, did you mean to send this just to me, or to the list? Mildly annoying, but either of you can send the message on to the list and the rest of the list members lose nothing. If you go the other way, though, the error case is that sometimes someone sends to the list a message they intended to be private. Not only does it increase list volume mostly with noise, but it occasionally leads to embarrassment, confusion, or breach of privacy. It makes all of the above more likely to happen accidentally, and when they do happen, there's no way to take it back. -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reply-to is to originator rather than to list
On 10/22/07, Ofer Inbar [EMAIL PROTECTED] wrote: I've seen this debate on a lot of lists. I firmly believe having a list munge reply-to is almost universally a very bad idea (the main exception being very small lists of people who know each other). Most email programs allow you to tell them the names of the lists you subscribe to, and/or can autodetect what the list name is from the appropriate headers, and give you an easy list-reply command. So you have your usual individual reply command, group reply / reply to all, and list reply. If you're annoyed by having to edit headers to reply to the list, then learn how to use your email program's list-reply command. Yes, sometimes someone accidentally replies to you and not the list. You write them back and ask, did you mean to send this just to me, or to the list? Mildly annoying, but either of you can send the message on to the list and the rest of the list members lose nothing. If you go the other way, though, the error case is that sometimes someone sends to the list a message they intended to be private. Not only does it increase list volume mostly with noise, but it occasionally leads to embarrassment, confusion, or breach of privacy. It makes all of the above more likely to happen accidentally, and when they do happen, there's no way to take it back. -- Cos Thank you for a well thought response. It sounds like a decent part of the list agrees with me, and a decent part disagree. However you are the first anti-munger to not refer to a FAQ which refers to unpersuasive arguments by a 3rd party. I would wager that on a up or down vote that the the mungers would have it, however the the support is not overwhelming, which it ought to be for a change like this to be made. Oh well. INSERT INTO `mysql_list` SET `forum`= 'RELIGION', `topic` = 'reply-to'; -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Traffic Sites
Erich C. Beyrent wrote: Craig, Thank you for your insight. Having never worked with clustering solutions, what kinds of headaches might one see with that solution? Does anyone know of any white papers that might shed some light into the pros and cons of each solution? You *did* look at the mysql website right? under white papers http://mysql.com/why-mysql/white-papers/mysql_db_high_availability.php Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Subquery
You are using a correlated subquery, which MySQL is terrible at. Whenever you find yourself doing a correlated subquery, see if you can switch it to a derived table with a join, which MySQL is far better at. A derived table is like a virtual table you create on the fly. It's very simple, just assign a name to your query and then treat it as if it is a regular table. So your query would look something like this: SELECT projects.* FROM projects JOIN (SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id) AS ptagids ON project.id=ptagids.project_id Your IN has become a JOIN and mysql optimizes it far better. On Oct 19, 2007, at 6:57 PM, Ryan Bates wrote: I'm trying to determine why a subquery is slower than running two separate queries. I have a simple many-to-many association using 3 tables: projects, tags and projects_tags. Here's the query I'm using to find the projects with a given tag: SELECT * FROM projects WHERE id IN (SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id); (0.36 sec) Compare that with splitting it into two queries: SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id (0.00 sec) /* returns 1, 2, 3 */ SELECT * FROM projects WHERE id IN (1, 2, 3); (0.00 sec) Why is it so much faster? Looking at the explain statement (below) of the one with the subquery, it appears it's not using the primary key index on the projects table. Why is it that MySQL doesn't perform this simple optimization? And is there a solution that will allow me to still use a subquery? I realize I can use a join instead of a subquery, but this is a simplified example. Here's the explain statement: *** 1. row *** id: 1 select_type: PRIMARY table: projects type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 15433 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: tags type: ref possible_keys: PRIMARY,index_tags_on_name key: index_tags_on_name key_len: 258 ref: const rows: 1 Extra: Using where; Using index *** 3. row *** id: 2 select_type: DEPENDENT SUBQUERY table: projects_tags type: ref possible_keys: tag_id key: tag_id key_len: 5 ref: my_database.tags.id rows: 10 Extra: Using where Here's the table dumps: CREATE TABLE `projects` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `tags` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `index_tags_on_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `projects_tags` ( `project_id` int(11) default NULL, `tag_id` int(11) default NULL, KEY `tag_id` (`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I'm using MySQL 5.0.37. Thanks in advance. Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Subquery
Indeed, as you say, Brent, correlated subqueries are not well-optimized in MySQL. The specific subquery (the IN() subquery) demonstrated in the original post is, however, optimized in MySQL 6.0 :) More comments inline. Brent Baisley wrote: You are using a correlated subquery, which MySQL is terrible at. Whenever you find yourself doing a correlated subquery, see if you can switch it to a derived table with a join, which MySQL is far better at. A derived table is like a virtual table you create on the fly. It's very simple, just assign a name to your query and then treat it as if it is a regular table. Actually, in this case, no need for a derived table. A simple join will suffice: SELECT * FROM projects p JOIN project_tags pt ON p.project_id = pt.project_id JOIN tags t ON pt.tag_id = t.tag_id WHERE tags.name='foo'; Make sure you've got indexes on p (project_id), pt (project_id, tag_id), t (name) Cheers, Jay So your query would look something like this: SELECT projects.* FROM projects JOIN (SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id) AS ptagids ON project.id=ptagids.project_id Your IN has become a JOIN and mysql optimizes it far better. On Oct 19, 2007, at 6:57 PM, Ryan Bates wrote: I'm trying to determine why a subquery is slower than running two separate queries. I have a simple many-to-many association using 3 tables: projects, tags and projects_tags. Here's the query I'm using to find the projects with a given tag: SELECT * FROM projects WHERE id IN (SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id); (0.36 sec) Compare that with splitting it into two queries: SELECT projects_tags.project_id FROM tags, projects_tags WHERE tags.name='foo' AND projects_tags.project_id=projects.id (0.00 sec) /* returns 1, 2, 3 */ SELECT * FROM projects WHERE id IN (1, 2, 3); (0.00 sec) Why is it so much faster? Looking at the explain statement (below) of the one with the subquery, it appears it's not using the primary key index on the projects table. Why is it that MySQL doesn't perform this simple optimization? And is there a solution that will allow me to still use a subquery? I realize I can use a join instead of a subquery, but this is a simplified example. Here's the explain statement: *** 1. row *** id: 1 select_type: PRIMARY table: projects type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 15433 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: tags type: ref possible_keys: PRIMARY,index_tags_on_name key: index_tags_on_name key_len: 258 ref: const rows: 1 Extra: Using where; Using index *** 3. row *** id: 2 select_type: DEPENDENT SUBQUERY table: projects_tags type: ref possible_keys: tag_id key: tag_id key_len: 5 ref: my_database.tags.id rows: 10 Extra: Using where Here's the table dumps: CREATE TABLE `projects` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `tags` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `index_tags_on_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `projects_tags` ( `project_id` int(11) default NULL, `tag_id` int(11) default NULL, KEY `tag_id` (`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I'm using MySQL 5.0.37. Thanks in advance. Ryan -- 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]
How to know the number of rows used in a SELECT MAX() query? FOUND_ROWS() not working.
Is there a way to know how many rows were used in a computation?
I tried this 'trick' but I still get 1, when I know that there are 3 rows
used...
SELECT SQL_CALC_FOUND_ROWS
MAX(DATE_ADD('2007-10-18 18:04:45', INTERVAL user_access_hours
HOUR)),
MAX(access_expire)
FROM
end_user_groups
JOIN end_user_group_links ON gid = id
WHERE
enabled = 1 AND uid = 16;
select FOUND_ROWS();
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_
found-rows
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to know the number of rows used in a SELECT MAX() query? FOUND_ROWS() not working.
Hi,
Daevid Vincent wrote:
Is there a way to know how many rows were used in a computation?
I tried this 'trick' but I still get 1, when I know that there are 3 rows
used...
SELECT SQL_CALC_FOUND_ROWS
MAX(DATE_ADD('2007-10-18 18:04:45', INTERVAL user_access_hours
HOUR)),
MAX(access_expire)
FROM
end_user_groups
JOIN end_user_group_links ON gid = id
WHERE
enabled = 1 AND uid = 16;
You can use COUNT(*). FOUND_ROWS() works a little differently, as you
know -- it lets you know how many rows would have been returned without
a LIMIT. But this query has no LIMIT of course.
select FOUND_ROWS();
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_
found-rows
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
