Re: upgrading problem
Yes Eric You are correct. Thankyou for your reply. It helped me. Bala
Re: Social Network, linking members
Hi, 2006/4/8, Dan Buettner [EMAIL PROTECTED]: Seems like what you're looking for is a way to query your database more efficiently/quickly, and still find all links in either direction. I think the use of a UNION statement should allow this. Basically write your query twice, joining on id1 the first time and id2 the second time, with UNION in the middle. To select reciprocal friends of Dan (that is a friends relationship in both ways), I would do that : select p.name from friends f1, friends f2, people p where p.peopleid=f1.id1 and f1.id2=1 AND f1.id2=f2.id1 AND f1.id1=f2.id2 UNION select p.name from friends f1, friends f2, people p where p.peopleid=f1.id2 and f1.id1=1 AND f1.id1=f2.id2 AND f1.id2=f2.id1; +--+ | name | +--+ | Matt | +--+ But I don't think it's the best solution in term of performance :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)
Hello, I wanted only to report that I removed and re-added the Index as Martijn suggested and now it's OK. Thanks again for your help Regards, Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bulk Duplicate Inserts
Are you wanting to insert into two tables (messages, social_networking) with one statement? If not, an INSERT / SELECT might work well for you. Like so: INSERT INTO messages (author, recipient, subject, body, timestamp) SELECT 1, social_networking.user_id, 'dfdf', 'adfgdf', 1144463208 FROM social_networking WHERE social_networking.id=1 AND social_networking.user_id IN(11,10,24,43,124,2,7) Hope this helps. Dan Hi, In a messaging system I'm working on, I will allow user's to send the same identical message to numerous other people. Kind of like in email you can separate recipients with ; or use CC. It would be nice to be able to perform a query like so: INSERT INTO messages, social_networking (author, recipient, subject, body, messages.timestamp) VALUES(1, social_networking.user_id, 'dfdf', 'adfgdf', 1144463208) WHERE social_networking.id=1 AND social_networking.user_id IN(11,10,24,43,124,2,7) However it doesn't appear that can be done. Does anyone know a method to do bulk dupe inserts without using an INSERT, compacted INSERT for each individual recipient or require the use of a temp table? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Web Farm Design
We are currently building a webfarm to replace our all in one box solution. We are experiencing problems when we get linked to a site like the drudgereport and we are seeing 1000's of hits a minute. Both apache and mysql start chocking and we get dropped because we cant deliver content. I am trying to understand what is the best way to redeploy our Mysql. I am thinking of two possible designs, if feasible. The clients are read only there is no inserting or deleting. The database is updated nightly from a single source. Fig 1 _ | http| |Mysql | |_| _ | http| ___ _ |Mysql | _ | PIX | | Load Balance | |_| | | || ---| |-- _ ---| SAN | |___|| | | http| | | |Mysql | |_| _ | http| |Mysql | |_| In Fig 1 I am thinking that each server runs both apache and the mysql engine and the database files will live on the SAN. Is this possible? can multiple engines talk to one database file on the SAN? Fig 2 _ | http| | | |_| _ | http| ___ _ | | _ __ | PIX | | Load Balance | |_| | | | | || ---| |-- _ ---| Mysql | |SAN| |___|| | | http| | ||_ | | | |_| In fig 2 the 3 apache servers would query the single myql server. All the data and scripts would live on the SAN. Or would it be better and faster to have the data live on the mysql server and the scripts live on the SAN? We are going to deploy HP proliant ml 585 with quad AMD 2.2 Opterons with 12 gigs of ram. With that in mind how many mysql sessions can I be able to support? Any suggestions, comments, thoughts of clarity would be greatly appreciated. Regards to all, Michael
Fw: Web Farm Design
My block digrams didn't come out so well. Fig 1. Pix --- Load Blanace --- 4 servers running apache and mysql SAN holdin the data and scripts Fig 2. Pix--- Load Balance - 3 servers running apache --- 1 server runnin mysql SAN holding data and scripts - Original Message - From: Elias [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Sent: Saturday, April 08, 2006 10:20 AM Subject: Web Farm Design We are currently building a webfarm to replace our all in one box solution. We are experiencing problems when we get linked to a site like the drudgereport and we are seeing 1000's of hits a minute. Both apache and mysql start chocking and we get dropped because we cant deliver content. I am trying to understand what is the best way to redeploy our Mysql. I am thinking of two possible designs, if feasible. The clients are read only there is no inserting or deleting. The database is updated nightly from a single source. Fig 1 _ | http| |Mysql | || _ | http | ___ | Mysql | _ | PIX | | Load Balance | || | | || ---||-- ___--| SAN | |___||___ | | http | | | |Mysql | || _ | http| |Mysql | |_| In Fig 1 I am thinking that each server runs both apache and the mysql engine and the database files will live on the SAN. Is this possible? can multiple engines talk to one database file on the SAN? Fig 2 _ | http| | | |_| _ | http| ___ _ | | _ __ | PIX | | Load Balance | |_| | | | | || ---| |-- _ ---| Mysql | |SAN |___|| | | http| | ||_ | | | |_| In fig 2 the 3 apache servers would query the single myql server. All the data and scripts would live on the SAN. Or would it be better and faster to have the data live on the mysql server and the scripts live on the SAN? We are going to deploy HP proliant ml 585 with quad AMD 2.2 Opterons with 12 gigs of ram. With that in mind how many mysql sessions can I be able to support? Any suggestions, comments, thoughts of clarity would be greatly appreciated. Regards to all, Michael -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. MailScanner thanks transtec Computers for their support. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 04.04.2006 23:17 (+0100), Eric Braswell wrote: my.cnf: bind-address = ip Will probably do the trick. How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
I would be tempted to say bind-address = Comma seperated list of IP's or Just a list of such statements one per line bind-address = IP1 bind-address = IP2 . . But since you said this is not properly documented, I would encourage you to give this a shot and find it out for yourself on a Spare/Test Mysql Box. Kishore Jalleda http://kjalleda.googlepages.com/projects On 4/8/06, Yves Goergen [EMAIL PROTECTED] wrote: On 04.04.2006 23:17 (+0100), Eric Braswell wrote: my.cnf: bind-address = ip Will probably do the trick. How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Web Farm Design
We are currently building a webfarm to replace our all in one box solution. We are experiencing problems when we get linked to a site like the drudgereport and we are seeing 1000's of hits a minute. Both apache and mysql start chocking and we get dropped because we cant deliver content. I am trying to understand what is the best way to redeploy our Mysql. I am thinking of two possible designs, if feasible. The clients are read only there is no inserting or deleting. The database is updated nightly from a single source. The first thing I would suggest to to run your apache and mysql on a different box, also you said your clients are read only and your DB would be updated nightly from a single source, so keeping that in mind figure 2 seems to be a better soution, use MYISAM as your tables as you only need reads. In Fig 1 I am thinking that each server runs both apache and the mysql engine and the database files will live on the SAN. Is this possible? can multiple engines talk to one database file on the SAN? No I dont think this is possible in Mysql, even if it is it might not be feasible. We are going to deploy HP proliant ml 585 with quad AMD 2.2 Opterons with 12 gigs of ram. With that in mind how many mysql sessions can I be able to support? refer to http://kjalleda.googlepages.com/maxconnbymysql to calculate this value I would also advice you to have some kind of Failover or High Availabilty in place for Mysql. Kishore Jalleda http://kjalleda.googlepages.com/projects On 4/8/06, Elias [EMAIL PROTECTED] wrote: We are currently building a webfarm to replace our all in one box solution. We are experiencing problems when we get linked to a site like the drudgereport and we are seeing 1000's of hits a minute. Both apache and mysql start chocking and we get dropped because we cant deliver content. I am trying to understand what is the best way to redeploy our Mysql. I am thinking of two possible designs, if feasible. The clients are read only there is no inserting or deleting. The database is updated nightly from a single source. Fig 1 _ | http| |Mysql | |_| _ | http| ___ _ |Mysql | _ | PIX | | Load Balance | |_| | | || ---| |-- _ ---|SAN | |___|| | | http| | | |Mysql | |_| _ | http| |Mysql | |_| In Fig 1 I am thinking that each server runs both apache and the mysql engine and the database files will live on the SAN. Is this possible? can multiple engines talk to one database file on the SAN? Fig 2 _ | http| | | |_| _ | http| ___ _ | | _ __ | PIX | | Load Balance | |_| | | | | || ---| |-- _ ---|Mysql | |SAN| |___|| | | http| | ||_ | | | |_| In fig 2 the 3 apache servers would query the single myql server. All the data and scripts would live on the SAN. Or would it be better and faster to have the data live on the mysql server and the scripts live on the SAN? We are going to deploy HP proliant ml 585 with quad AMD 2.2 Opterons with 12 gigs of ram. With that in mind how many mysql sessions can I be able to support? Any suggestions, comments, thoughts of clarity would be greatly appreciated. Regards to all, Michael
Re: mysql on tmpfs
If there is plenty of free memory... Can't you completly disable SWAP ? -- Gabriel PREDA Senior Web Developer On 4/7/06, Atle Veka [EMAIL PROTECTED] wrote: However, even though there is plenty of free memory linux makes weird decisions from time to time, temporarily killing performance, swapping in/out to make room.
Re: Problem running multi master replication
On Sat, 8 Apr 2006, Leonardus Setiabudi wrote: when i log into server 2 using port 3307, i can see the data in db2 updated, which means the replication succeed (show slave status tells the same thing), and the same thing with server 3, i can see db3 updated, also with server 4 where db4 updated but when i log into server1, where i can access all the dbs, db2-4 were never updated (it should be, its the same file) in this time, if i run repair table on server1 to all tables in db2-4 .. bingo, the data appears .. with the info, found xxx rows of 0 rows so, to be clear ... server1 is NOT a SLAVE, its just plain server server2-4 runs well, and replicate well ... the problem lies within server1, where it supposed to be able to read the FILE (tables) without the need to issue repair command (at least in my assumption :) ) Hold on, (it should be, its the same file), you're saying that db2 on server1 and server2 are using the same database files (datadir)? If that's the case, why? That doesn't make any sense. Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql on tmpfs
That would be pretty dangerous if I ever needed to say rebuild a table and run out of memory. Atle - Flying Crocodile Inc, Unix Systems Administrator On Sat, 8 Apr 2006, Gabriel PREDA wrote: If there is plenty of free memory... Can't you completly disable SWAP ? -- Gabriel PREDA Senior Web Developer On 4/7/06, Atle Veka [EMAIL PROTECTED] wrote: However, even though there is plenty of free memory linux makes weird decisions from time to time, temporarily killing performance, swapping in/out to make room. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what is this? -- errno=2006 errmsg=Server gone
Hi, I get this weird error message: ErrNo: 2006 Error: MySQL server has gone away. What does it mean? I couldn't find anything useful on google and the error message isn't exactly verbose.. :) regards, martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Web Farm Design
I found High Performance MySQL http://highperformancemysql.com/ very useful. Also, throwing some money at the people at MySQL should get you a highly informed answer. One of the first tests I run for a customer is to see if the structure of their data and indices match the needs of the queries being submitted. Can you use a divide and conquer strategy on the problem? e.g., are all of the queries the problem or are just a subset causing the problem? Is there any information you can get to focus in on the problem? Have you run samples of problem queries against the database using EXPLAIN? Are responses taking multiple queries? Can the number of queries by reduced, say, by co-locating data items instead of using a join or multiple queries to pull together everything needed for a response? The architecture in figure 1 might work fine if you can structure the data so that the various servers answer different questions. That way you might be able to partition the databases to eliminate some queries or indices. Perhaps the Load Balancer can be made smarter and feed each query to the server that's best able to respond. That way you might be able to make multiple servers smarter instead of just more iron. Since you update in batch mode there might be some optimizing that can be done to prepare responses to common queries, analyze the nature of the queries to respond to changes in the data being requested, etc. Every once in a while getting facts about the problem leads to a better solution. Hope this helps. Eldon Ziegler Atlantic Database Systems, Inc. www.atlanticdb.com At 10:20 am 4/8/2006, Elias wrote: We are currently building a webfarm to replace our all in one box solution. We are experiencing problems when we get linked to a site like the drudgereport and we are seeing 1000's of hits a minute. Both apache and mysql start chocking and we get dropped because we cant deliver content. I am trying to understand what is the best way to redeploy our Mysql. I am thinking of two possible designs, if feasible. The clients are read only there is no inserting or deleting. The database is updated nightly from a single source. Fig 1 _ | http| |Mysql | |_| _ | http| ___ _ |Mysql | _ | PIX | | Load Balance | |_| | | || ---| |-- _ ---|SAN | |___|| | | http| | | |Mysql | |_| _ | http| |Mysql | |_| In Fig 1 I am thinking that each server runs both apache and the mysql engine and the database files will live on the SAN. Is this possible? can multiple engines talk to one database file on the SAN? Fig 2 _ | http| | | |_| _ | http| ___ _ | | _ __ | PIX | | Load Balance | |_| | | | | || ---| |-- _ ---|Mysql | |SAN| |___|| | | http| | ||_ | | | |_| In fig 2 the 3 apache servers would query the single myql server. All the data and scripts would live on the SAN. Or would it be better and faster to have the data live on the mysql server and the scripts live on the SAN? We are going to deploy HP proliant ml 585 with quad AMD 2.2 Opterons with 12 gigs of ram. With that in mind how many mysql sessions can I be able to support? Any suggestions, comments, thoughts of clarity would be greatly
Re: Restrict MySQL server 4/5 to single IP
On 08.04.2006 18:31 (+0100), Kishore Jalleda wrote: bind-address = Comma seperated list of IP's Doesn't work. MySQL binds to address 255.255.255.255 instead. Also a colon-separated list does this. bind-address = IP1 bind-address = IP2 This always takes the last option, so if I add 127.0.0.1 after the other IP, it will only bind to this one. Any more suggestions? Maybe someone from the dev team? Should I install a local port forwarder? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
Yves Goergen wrote: On 04.04.2006 23:17 (+0100), Eric Braswell wrote: my.cnf: bind-address = ip Will probably do the trick. How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. Why do you want to do this? Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Web Farm Design
It is not possible to make specific suggestions based on the information you've provided. The list would need to know the nature and platform of your application, the amount and type of the traffic you see, your current mysql vs. apache load (like queries, requests per sec), current server config, what you mean by choking, etc. But there are always some general rules that apply, some of which have been mentioned... I'll second the recommendation for the book High Performance MySQL by Zawodny. It will answer many of your questions and help guide you through this process. We have two sample chapters from the book -- Replication and Server Performance Tuning on our Developer Zone. We also have several other great articles on performance tuning in our articles section: http://dev.mysql.com/tech-resources/articles/ I'd also like to recommend our Consulting (Professional Services) folks, led by Josh Chamas. You can get any kind of consulting gig, big or small, for a very fair price -- it will save you money. These guys are absolutely top notch and among the best in the business when it comes to scale-out -- they do regular consulting for very well known, high traffic sites I'm not allowed to mention. See http://www.mysql.com/consulting/ Some general principles I go by: - If you can, always separate http and MySQL (as in your option 2). - If you are read heavy as you say (and who isn't?), spread that load over one or more mysql slaves. You don't mention replication. Why not use it? - People tend to assume that there is nothing wrong with their application, but everything wrong with their hardware or software. The -first- place I look is in the slow query log, because for web applications usually built by non-DBAs, that is so often the biggest bang for your buck. Sometimes 20 minutes using explain to make sure you have the right indexes in place and are not doing sub-optimal joins can give you a 20-30% increase in application performance. Next I spend some time in my.cnf making sure that MySQL server is making the best use of memory. If you give us more details I think we can probably give more specific advice on what sort of architecture might be an appropriate fit. -- Eric Braswell Web Manager MySQL AB Cupertino, USA Elias wrote: We are currently building a webfarm to replace our all in one box solution. We are experiencing problems when we get linked to a site like the drudgereport and we are seeing 1000's of hits a minute. Both apache and mysql start chocking and we get dropped because we cant deliver content. I am trying to understand what is the best way to redeploy our Mysql. I am thinking of two possible designs, if feasible. The clients are read only there is no inserting or deleting. The database is updated nightly from a single source. Fig 1 _ | http| |Mysql | |_| _ | http| ___ _ |Mysql | _ | PIX | | Load Balance | |_| | | || ---| |-- _ ---| SAN | |___|| | | http| | | |Mysql | |_| _ | http| |Mysql | |_| In Fig 1 I am thinking that each server runs both apache and the mysql engine and the database files will live on the SAN. Is this possible? can multiple engines talk to one database file on the SAN? Fig 2 _ | http| | | |_| _ | http| ___ _ | | _ __ | PIX | | Load Balance | |_| | | | | || ---| |-- _ ---| Mysql | |SAN| |___|| | |
Re: Restrict MySQL server 4/5 to single IP
On 08.04.2006 23:14 (+0100), Eric Braswell wrote: Yves Goergen wrote: How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. Why do you want to do this? Currently, I have one IP address on my server, with one MySQL server. In the near future, I'll have a server with multiple IP addresses and I'm going to install MySQL 4.0 and 5.0 in parallel. My first design was to use different ports for both servers, but that's always a little complicated to configure for the clients. Now I want to use one IP for one MySQL server. Both are external addresses, which can be assigned with a DNS name for simple access. But the 4.0 server still has some system tasks and also for legacy reasons, I'd like to keep the 4.0 server listening on the localhost interface. This also allows me to assign more strict access rights for these system-related tasks. They can be limited to the local host instead of any host. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Does the movement of the trees make the wind blow? http://newsboard.unclassified.de - Unclassified NewsBoard Forum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
COUNT() Efficiency
Hi, If I did a query like: SELECT COUNT(id) AS count1, COUNT(id) AS count2 FROM table WHERE id100 Would MySQL run the COUNT() calculation once or twice? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT() Efficiency
- Original Message - From: Martin Gallagher [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 08, 2006 6:34 PM Subject: COUNT() Efficiency Hi, If I did a query like: SELECT COUNT(id) AS count1, COUNT(id) AS count2 FROM table WHERE id100 Would MySQL run the COUNT() calculation once or twice? I don't know the answer to your question but why would you want to count in the same column of the same table twice in the same query? I'm not very knowledgeable about MySQL performance - I'm mostly a DB2 guy - but doesn't MySQL have an Explain command that will tell you what access path you are getting? If it does, your best bet would be to try your query and do an Explain to see what it actually does; it should be pretty clear whether the count() is being done once or twice. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
You can't specify multiple IPs for bind-address. Let's back up. You want to run both 4.0 and 5.0, and you want to be able to set it up so it's 'easy' to connect to each separate instance, i.e. without having to specify different ports. This is pretty easy to do. In this case you use separate configurations for each instance (5.0 has an instance manager for this, but you can start mysql server with any arbitrary configuration file or configuration options). An example: start each mysqld4 --bind-address=192.168.1.1(more options for each basedir, datadir, etc) mysqld5 --bind-address=192.168.1.2(more options for each basedir, datadir, etc) Now if 192.168.1.1 resolves to mysql4.somedomain.com, you simply connect to that for version 4, and mysql5.somedomain.com for version 5. If you want one copy to -only- listen locally and not be accessible from an external IP, you can use --bind-address=127.0.0.1 (or some other internal-only IP). Alternatively you can use --skip-networking to not use TCP/IP, and connect to your 'local-only' copy via a socket/name pipes. See: http://dev.mysql.com/doc/refman/4.1/en/server-options.html and: http://dev.mysql.com/doc/refman/4.1/en/option-files.html If this were me, I would create two configuration files, my4.cnf and my5.cnf with these options, as well as unique settings for data directories and so on, and start the two instances by telling safe_mysqld to use the appropriate configuration file, and the appropriate mysqld binary, for each instance. Before the instance manager in 5.0, there is also mysqld_multi: http://dev.mysql.com/doc/refman/4.1/en/mysqld-multi.html, which might be of help here. Does that make sense? Did I misunderstand? -- Eric Braswell Web Manager MySQL AB Cupertino, USA Yves Goergen wrote: On 08.04.2006 23:14 (+0100), Eric Braswell wrote: Yves Goergen wrote: How can I enter multiple IP addresses there? This isn't documented online. I need to bind it to one specific external address and additionally to localhost (127.0.0.1). The other server is only bound to another external address. Why do you want to do this? Currently, I have one IP address on my server, with one MySQL server. In the near future, I'll have a server with multiple IP addresses and I'm going to install MySQL 4.0 and 5.0 in parallel. My first design was to use different ports for both servers, but that's always a little complicated to configure for the clients. Now I want to use one IP for one MySQL server. Both are external addresses, which can be assigned with a DNS name for simple access. But the 4.0 server still has some system tasks and also for legacy reasons, I'd like to keep the 4.0 server listening on the localhost interface. This also allows me to assign more strict access rights for these system-related tasks. They can be limited to the local host instead of any host. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: COUNT() Efficiency
I'm sending this back to the mailing list where it belongs; that way, other people can jump in to help and others can learn from the discussion, either now or via the list archive. - Original Message - From: Martin Gallagher [EMAIL PROTECTED] To: 'Rhino' [EMAIL PROTECTED] Sent: Saturday, April 08, 2006 6:57 PM Subject: RE: COUNT() Efficiency Sorry about the previous empty message. This is the actual query: SELECT messages.id, subject, IF(COUNT(messages.id)=1, CONCAT(members.surname, ', ', members.forename), CONCAT(COUNT(messages.id), ' Recipients')) AS `to` FROM messages LEFT JOIN members ON members.id=messages.recipient WHERE author='1' AND messages.flag IN(0, 2, 4) GROUP BY checksum ORDER BY messages.timestamp DESC LIMIT 0,10 The problem is I can seem to assign COUNT(messages.id) to a MySQL variable like so: Do you mean 'can' or 'cannot'? It isn't usually a problem if you _can_ assign a COUNT() expression to a variable SELECT messages.id, subject, @count:=COUNT(messages.id), IF(@count =1, CONCAT(members.surname, ', ', members.forename), CONCAT(@count, ' Recipients')) AS `to` FROM messages LEFT JOIN members ON members.id=messages.recipient WHERE author='1' AND messages.flag IN(0, 2, 4) GROUP BY checksum ORDER BY messages.timestamp DESC LIMIT 0,10 This results in NULL for `to` It might have something to do with the GROUP clause? How does any of this have anything to do with the efficiency of COUNT()? You've explained why you need to have the same COUNT() expression in the SELECT twice and I accept that this looks like it might be reasonable in this case. But your real problem seems to be the null in the 'to' column, which has nothing to do with efficiency. As for your GROUP BY clause, something looks rather odd there; I don't see checksum in the SELECT at all yet you're grouping by it. Normally, a GROUP BY names one or more columns from the SELECT that aren't in column functions so that you can get summarization of the rows returned by the query. This query doesn't seem to be operating that way. That is not to say that it is invalid in some way - it might be exactly the right solution to your problem - but it makes me question what you're doing. Unfortunately, I'm behind with my own work and don't have the time I would need to wheedle out enough information to figure out if you are doing the right thing or the wrong thing. I just jumped in because it appeared that you had a short simple question; apparently, that is not the case. I'll leave the others on the list to help you. Be patient, there isn't much activity on the weekend but things start to pick up once the work week starts again. Sorry I can't be more help. -- Rhino -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: 08 April 2006 15:50 To: Martin Gallagher; mysql@lists.mysql.com Subject: Re: COUNT() Efficiency - Original Message - From: Martin Gallagher [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, April 08, 2006 6:34 PM Subject: COUNT() Efficiency Hi, If I did a query like: SELECT COUNT(id) AS count1, COUNT(id) AS count2 FROM table WHERE id100 Would MySQL run the COUNT() calculation once or twice? I don't know the answer to your question but why would you want to count in the same column of the same table twice in the same query? I'm not very knowledgeable about MySQL performance - I'm mostly a DB2 guy - but doesn't MySQL have an Explain command that will tell you what access path you are getting? If it does, your best bet would be to try your query and do an Explain to see what it actually does; it should be pretty clear whether the count() is being done once or twice. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert speed on table with 7M rows but small row size.
On 4/8/06, Kevin Burton [EMAIL PROTECTED] wrote: Is there anyway I can tune MySQL to improve this operation? There's a fundamental problem with B-trees - inserts in a big B-tree can be very slow, cause it may require tree rebalancing. One workaround for this problem is partitioning. MySQL 5.1 will have built-in partitioning support, but the idea itself is very simple, and you can achieve the same effect by manually spreading data among tables. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem running multi master replication
Hold on, (it should be, its the same file), you're saying that db2 on server1 and server2 are using the same database files (datadir)? If that's the case, why? That doesn't make any sense. Atle - Flying Crocodile Inc, Unix Systems Administrator Im replying from another account here .. that's the whole point ... server1 supposed to be read only .. while other server (2-4) the ones that take the role in writing it i know it's kind of weird, but hey, if it should work in theory, why not trying it .. that's why what i ask in the first place is is it a locking issue? is it mysql part or filesystem issue) i already found a work around for this problem with scheduling, but that's not the prettiest solution that i hoped for :) or maybe there are other solution?
