Re: MERGE table problem
Eric Anderson wrote: I've got a master (Master) with a MERGE table of foo_t (comprising of bar_a, bar_b, bar_c) in database 'Igloo'. There are 5 slaves that replicate the Igloo table, but ignore the Igloo.foo_t table. This setup was working fine. If the Master server crashed or had a prblem for some reason, I could always: STOP SLAVE RESET SLAVE FLUSH TABLES LOAD DATA FROM MASTER START SLAVE The Master's motherboard failed last night, and after getting the server back up, replication seems to be broken. The LOAD DATA FROM MASTER command fails on ALL slaves with: mysql load data from master\g ERROR 1188 (HY000): Error from master: 'Can't find file: 'foo_t.MRG' (errno: 2)' mysql $ perror 2 System error: 2 = No such file or directory The foo_t.MRG file doesn't exist. Not sure why that is (don't have any experience with replication), anyone else have some suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: First View
I was lucky enough to have gotten a copy of MySQL 10 (aka MySQL X) from the source tree before it was pulled. The query optimizer used predictive algorithms with temporal displacement logic, which meant that it could and did frequently return results in negative time, before the query was even asked. The full-text search used natural-language processing so effectively that you could search for breakfast and it would know whether to return bacon and eggs or a cheese danish depending on what you felt like that morning. By version 10.13, you could just search a full-text index for something or whatever and it would return exactly the rows you needed. The whole thing ran in under 2K of RAM. I had a copy running on the office coffee machine for a while until people started getting caffeine headaches just from reading reports. The BLOB type could store not only binary data but actual THINGS, like books or clothing or building materials. There was a weight limit, but you could get around that by setting something in the config file. Also, there was a bug in the refrigeration stored procedure, so you had to be very careful with perishables, like, say, fish. On Aug 25, 2006, at 12:50 AM, mos wrote: At 06:30 PM 8/24/2006, David Hillman wrote: On Aug 24, 2006, at 6:13 PM, Daniel Kasak wrote: That's just how software develops. People start with the later- versioned product, with full features and zero bugs, and progressively remove features and add bugs, while decreasing the version number. I've looked all over the web, and found nothing, so hopefully someone here can help... where can I download MySQL 10 from? ;) -- David, MySQL 10 came out quite a while ago and is now obsolete. MySQL 10 of course had been totally free and since it worked so well, it nearly killed off all the competition. Legislation was quickly passed in order to make things fair for other database manufacturers by forcing the programmers to make radical changes to the MySQL 10 engine. The engine had far too many features and worked too well so the programmers have been stripping out features and adding bugs for the past 5 years. They also had to slow it down because people were getting results in 0ms which led people to think the results were inaccurate even though they weren't. Delay loops had to be added along with random disk access to give people the impression a lot of work was being processed by the MySQL engine. They also discovered getting perfect answers each time had created its own problems. Accounting systems written in MySQL 10 produced highly accurate Income Statements and Balance Sheets correctly the first time they were run, causing much embarrassment at stock holders meetings for revealing improprieties by the board. Bugs had to be added to the MySQL engine and the SQL syntax had to be obfuscated in order to introduce calculation mistakes so the CEO's could have someone to blame and thereby avoid criminal prosecution. Since MySQL 10 was totally free for both personal and commercial use without any restrictions whatsoever, royalties were quickly introduced to try and limit the number of people using the product. All of this of course took years to accomplish. What we're left with is what we have now. g,dr Mike (Just kidding Monty - Please don't key my car!) -- 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]
MySQL NOW() function producing 0000-00-00 00:00:00
Hello all, We are using an NOW() function in our database and occasionally it produces odd results. There are entries where it states: -00-00 00:00:00 instead of the current time. Is this a bug, or are we using the function incorrectly? MySQL version info: mysql Ver 12.22 Distrib 4.0.26, for portbld-freebsd4.11 (i386) Thank you, Jeremiah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL NOW() function producing 0000-00-00 00:00:00
Jeremiah Foster wrote: Hello all, We are using an NOW() function in our database and occasionally it produces odd results. There are entries where it states: -00-00 00:00:00 instead of the current time. Is this a bug, or are we using the function incorrectly? MySQL version info: mysql Ver 12.22 Distrib 4.0.26, for portbld-freebsd4.11 (i386) Hum, the little devil is playing you tricks, uh ? It's easier that NOW() didn't run at all instead of producing Jesus' dates. Try a dummy insert on a dummy table just putting now() on a column and see if any of them gets out zero. If some does, it's a bug on MySQL, otherwise can be a problem while you're assembling your query. mysql use test; create table foo (a datetime); bash$ for ((i=1000; i; i--)); do echo insert into foo values (now()) | mysql test; done myslq use test; select * from dates where a = -00-00 00:00:00; Should return no results. cheers, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security settings won't take during installation
Hi Adrian, On 8/25/06, Adrian Greeman [EMAIL PROTECTED] wrote: The security settings could not be applied to the database because the .. I am pasting here the text of one of my earlier posts to this list: I got MySQL 5.0.22 running successfully on Win-XP-SP2 as follows: 1) Download the no-install zip package of MySQL-5.0.22 from the website. 2) Unpack it in the directory of your choice (C:\mysql5 - for example) 3) Create a top-level folder - C:\mysql5Data for the data directory. 4) Cut the contents of C:\mysql5\data directory and paste the same into C:\mysql5Data. 5) Delete C:\mysql5\data directory. 6) Create a copy of an appropriate mysql-xxx.ini file and rename it to MY.INI. 7) Change the value of the data-dir variable to C:/mysql5Data - NOTE FORWARD INSTEAD OF BACK-SLASHES. 8) Create another top-level C:\InnoDBData folder. 9) Change appropriate InnoDB data-directory variables in C:\my.ini (with forward slashes!) 10) Right-click MY COMPUTER icon on the desktop and select properties from the shortcut menu. Goto the Advanced tab and select Environment [Variables]. Select PATH in the \ system variables and add C:\mysql5\bin; at the beginning of it. Apply and OK. 11) Select RUN from the start menu, type CMD and press ENTER. 12) Right click the C:\InnoDBData folder, select Properties from the shortcut menu and add LOCAL SERVICE user-account in the security tab and give it Full Control access. 13) Do the same as in 12) with the C:/mysql5Data folder. 12) type mysqld-nt --install YourServiceName --defaults-file=C:\mysql5\my.ini --local-service and press ENTER. 13) type NET START YourServiceName AND PRESS ENTER. Now you have MYSQL-5.0.22 installed on WinXP-Pro-SP2. Forget about the automatic installer. I got similar errors like yours. Additionally, I have found that the MySQL ODBC driver installs best when you do it manually - that is, hand-copy files into the Windows System32 directory. In addition, as I have found, it complains that some MSVCR7.dll is missing. When I searched for the file on Google, the search engine led me to some DllFiles.com (or some similar webpage) where I got the file, downloaded the same to my computer and everything worked like a cinch! -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL NOW() function producing 0000-00-00 00:00:00
We are using an NOW() function in our database and occasionally it produces odd results. There are entries where it states: -00-00 00:00:00 instead of the current time. Is this a bug, or are we using the function incorrectly? After patiently injecting at about 400 queries per seconds, a couple of hours later, I had about 5 million records in a table. Not a single one of them experienced the above I'm pretty sure your -00-00 does not come from NOW() -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
David Lazo wrote: I'm sorry to bother you again with this. So we have the server but we have 4 Drives and now that I'm trying to set up the RAID10 I'm starting to think I needed 5 Drives one for the OS?. Please advise. David. snip We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. We have RAID 1 for the OS (requires 2 disks) If you are doing data redundancy for the DB, you'd want to also do data redundancy for the OS... If it is a windows server, 32GB drives should give you plenty of space to work with (save some money) and you can get away with 10Krpm or if budgets are tight, 7200rpm. Our layout is mentioned in my previous mail. -- Thanks, James Rallo Trusswood Inc. [EMAIL PROTECTED] www.Trusswood.Net Tele: (321) 383-0366 Fax: (321) 383-0362 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Testing Email
I have been having problems with my email, and I wanted to test to this list. Thanks, nick
Re: MySQL NOW() function producing 0000-00-00 00:00:00
Chris Knipe wrote: After patiently injecting at about 400 queries per seconds, a couple of hours later, I had about 5 million records in a table. Not a single one of them experienced the above It's one every 5 million and 1 entries... try again ;) Also, maybe (very improbable) it can be the way you are using NOW() within the query. Can you show us the query ? cheers, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Testing Email
Nicholas Vettese wrote: I have been having problems with my email, and I wanted to test to this list. Will let you know when I receive it... --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Testing Email
INSERT INTO a VALUES (NOW()) ? Regards, Chris. - Original Message - From: Renato Golin [EMAIL PROTECTED] To: Nicholas Vettese [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, August 25, 2006 4:31 PM Subject: Re: Testing Email Nicholas Vettese wrote: I have been having problems with my email, and I wanted to test to this list. Will let you know when I receive it... --renato -- 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: MySQL NOW() function producing 0000-00-00 00:00:00
Doh.. Wrong email ;) INSERT INTO a VALUES (NOW()) ? Regards, Chris. - Original Message - From: Renato Golin [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, August 25, 2006 4:30 PM Subject: Re: MySQL NOW() function producing -00-00 00:00:00 Chris Knipe wrote: After patiently injecting at about 400 queries per seconds, a couple of hours later, I had about 5 million records in a table. Not a single one of them experienced the above It's one every 5 million and 1 entries... try again ;) Also, maybe (very improbable) it can be the way you are using NOW() within the query. Can you show us the query ? cheers, --renato -- 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: Re: Windows Server Configuration
James, with just 4 drives, you can set up one big RAID 10 disk (usually called a logical disk, with Dell PERCs I think it's a container), and then partition it for your different needs. If you have 4 73 GB disks, you probably have around 135 GB formatted capacity with RAID 10; I'd do something like this for my own MySQL server in that situation: 20 GB C partition for OS and software binaries 10 GB D partition for MySQL temp space 20-40 GB E partition for MySQL binary logs (if you're using them) remainder F partiition for MySQL data directory Your needs will vary depending on whether this server does only MySQL or other serving as well, how big your databases are, whether you want to keep binary logs for some period of time, and how large those binary logs are. I agree with David's response that you want redundancy for the OS as well. Drives fail, plain and simple. The single best thing you can do with servers is plan for hardware failure. Having your data on redundant disks is great, but if your OS is on a single drive, when (not if, when) that one fails, your data is redundant but still unavailable. You may pay a small performance penalty having the OS on the same physical drives with your MySQL, but I'd make that sacrifice for the redundancy, no question. On the other hand if you want to add a couple of drives and make a separate RAID 1 pair for the OS, go for it. Best, Dan On 8/25/06, JamesDR [EMAIL PROTECTED] wrote: David Lazo wrote: I'm sorry to bother you again with this. So we have the server but we have 4 Drives and now that I'm trying to set up the RAID10 I'm starting to think I needed 5 Drives one for the OS?. Please advise. David. snip We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. We have RAID 1 for the OS (requires 2 disks) If you are doing data redundancy for the DB, you'd want to also do data redundancy for the OS... If it is a windows server, 32GB drives should give you plenty of space to work with (save some money) and you can get away with 10Krpm or if budgets are tight, 7200rpm. Our layout is mentioned in my previous mail. -- Thanks, James Rallo Trusswood Inc. [EMAIL PROTECTED] www.Trusswood.Net Tele: (321) 383-0366 Fax: (321) 383-0362 -- 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: Re: Re: Windows Server Configuration
Sorry, I think I had James and David backwards there! On 8/25/06, Dan Buettner [EMAIL PROTECTED] wrote: James, with just 4 drives, you can set up one big RAID 10 disk (usually called a logical disk, with Dell PERCs I think it's a container), and then partition it for your different needs. If you have 4 73 GB disks, you probably have around 135 GB formatted capacity with RAID 10; I'd do something like this for my own MySQL server in that situation: 20 GB C partition for OS and software binaries 10 GB D partition for MySQL temp space 20-40 GB E partition for MySQL binary logs (if you're using them) remainder F partiition for MySQL data directory Your needs will vary depending on whether this server does only MySQL or other serving as well, how big your databases are, whether you want to keep binary logs for some period of time, and how large those binary logs are. I agree with David's response that you want redundancy for the OS as well. Drives fail, plain and simple. The single best thing you can do with servers is plan for hardware failure. Having your data on redundant disks is great, but if your OS is on a single drive, when (not if, when) that one fails, your data is redundant but still unavailable. You may pay a small performance penalty having the OS on the same physical drives with your MySQL, but I'd make that sacrifice for the redundancy, no question. On the other hand if you want to add a couple of drives and make a separate RAID 1 pair for the OS, go for it. Best, Dan On 8/25/06, JamesDR [EMAIL PROTECTED] wrote: David Lazo wrote: I'm sorry to bother you again with this. So we have the server but we have 4 Drives and now that I'm trying to set up the RAID10 I'm starting to think I needed 5 Drives one for the OS?. Please advise. David. snip We built one pretty close to this recently. You definitely want to go with raid10, make sure the controller is hardware and not software raid (uses the CPU for everything, opposed to having a dedicated on board CPU) The more spindles the better, in order to use RAID10 you need an even set of disks, min 4. Raid10 gives you the best performance while keeping data redundancy. I would set it up like this: Raid1 -- OS (you could use slower/smaller drives here) Raid10 -- all of the mysql data -- as many spindles as you can afford. If you have to swap out 73GB drives for for the 146's to get more spindles, I would do that (that would increase cost a bit, but the disk sub system here would be the bottle neck, so you want to have it as fast as you can get it -- and still be affordable) This all depends on what your data environment looks like as well. We have RAID 1 for the OS (requires 2 disks) If you are doing data redundancy for the DB, you'd want to also do data redundancy for the OS... If it is a windows server, 32GB drives should give you plenty of space to work with (save some money) and you can get away with 10Krpm or if budgets are tight, 7200rpm. Our layout is mentioned in my previous mail. -- Thanks, James Rallo Trusswood Inc. [EMAIL PROTECTED] www.Trusswood.Net Tele: (321) 383-0366 Fax: (321) 383-0362 -- 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: MySQL NOW() function producing 0000-00-00 00:00:00
Chris Knipe wrote: Doh.. Wrong email ;) INSERT INTO a VALUES (NOW()) ? Sorry, it was not for you, I wanted Jeremiah's query... replied the wrong mail... my fault! ;) --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: MySQL NOW() function producing 0000-00-00 00:00:00
Only thing I could guess is that at some point in your code you are executing: INSERT INTO a VALUES (NOW()) instead, which for me on 5.0.21 results in -00-00 00:00:00 Perhaps you have a function in your code that automatically quotes/escapes data prior to inserting or updating... that would likely do it. Dan On 8/25/06, Chris Knipe [EMAIL PROTECTED] wrote: Doh.. Wrong email ;) INSERT INTO a VALUES (NOW()) ? Regards, Chris. - Original Message - From: Renato Golin [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, August 25, 2006 4:30 PM Subject: Re: MySQL NOW() function producing -00-00 00:00:00 Chris Knipe wrote: After patiently injecting at about 400 queries per seconds, a couple of hours later, I had about 5 million records in a table. Not a single one of them experienced the above It's one every 5 million and 1 entries... try again ;) Also, maybe (very improbable) it can be the way you are using NOW() within the query. Can you show us the query ? cheers, --renato -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Testing Email
Who knew this list had so many funny people. And I came here for the serious folks. Take me off this list! ;) Thanks for all the help peoples -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED] Sent: Friday, August 25, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: Testing Email INSERT INTO a VALUES (NOW()) ? Regards, Chris. - Original Message - From: Renato Golin [EMAIL PROTECTED] To: Nicholas Vettese [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, August 25, 2006 4:31 PM Subject: Re: Testing Email Nicholas Vettese wrote: I have been having problems with my email, and I wanted to test to this list. Will let you know when I receive it... --renato -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
Thanx again. For the time being, we will keep 4 drives with Dan's suggestion. OS and MySQL running from there. On 8/25/06 11:03 AM, Dan Buettner [EMAIL PROTECTED] wrote: James, with just 4 drives, you can set up one big RAID 10 disk (usually called a logical disk, with Dell PERCs I think it's a container), and then partition it for your different needs. If you have 4 73 GB disks, you probably have around 135 GB formatted capacity with RAID 10; I'd do something like this for my own MySQL server in that situation: 20 GB C partition for OS and software binaries 10 GB D partition for MySQL temp space 20-40 GB E partition for MySQL binary logs (if you're using them) remainder F partiition for MySQL data directory Your needs will vary depending on whether this server does only MySQL or other serving as well, how big your databases are, whether you want to keep binary logs for some period of time, and how large those binary logs are. I agree with David's response that you want redundancy for the OS as well. Drives fail, plain and simple. The single best thing you can do with servers is plan for hardware failure. Having your data on redundant disks is great, but if your OS is on a single drive, when (not if, when) that one fails, your data is redundant but still unavailable. You may pay a small performance penalty having the OS on the same physical drives with your MySQL, but I'd make that sacrifice for the redundancy, no question. On the other hand if you want to add a couple of drives and make a separate RAID 1 pair for the OS, go for it. Best, Dan On 8/25/06, JamesDR [EMAIL PROTECTED] wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
Just noticed that you said partitions. I am assuming that you meat multiple drives in a raid array. Bill David Lazo said: Thanx again. For the time being, we will keep 4 drives with Dan's suggestion. OS and MySQL running from there. On 8/25/06 11:03 AM, Dan Buettner [EMAIL PROTECTED] wrote: James, with just 4 drives, you can set up one big RAID 10 disk (usually called a logical disk, with Dell PERCs I think it's a container), and then partition it for your different needs. If you have 4 73 GB disks, you probably have around 135 GB formatted capacity with RAID 10; I'd do something like this for my own MySQL server in that situation: 20 GB C partition for OS and software binaries 10 GB D partition for MySQL temp space 20-40 GB E partition for MySQL binary logs (if you're using them) remainder F partiition for MySQL data directory Your needs will vary depending on whether this server does only MySQL or other serving as well, how big your databases are, whether you want to keep binary logs for some period of time, and how large those binary logs are. I agree with David's response that you want redundancy for the OS as well. Drives fail, plain and simple. The single best thing you can do with servers is plan for hardware failure. Having your data on redundant disks is great, but if your OS is on a single drive, when (not if, when) that one fails, your data is redundant but still unavailable. You may pay a small performance penalty having the OS on the same physical drives with your MySQL, but I'd make that sacrifice for the redundancy, no question. On the other hand if you want to add a couple of drives and make a separate RAID 1 pair for the OS, go for it. Best, Dan On 8/25/06, JamesDR [EMAIL PROTECTED] wrote: -- 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]
Web Seminar Events
Why is it that the Newsletter goes out after the Web Seminar's have already occurred. There have been several that I would have liked to see, but I got the news letter a day after the event occurred. It would be nice to know at least a day ahead of time when these things are going to happen. Is there another list somewhere of scheduled Web Seminars? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
file i/o operations...
hi... i'm trying to determine which is the better way/approach to go. should an app do a great deal of file i/o, or should it do a great deal of read/writes to a mysql db... my test app will create a number of spawned child processes, 1000's of simultaneous processes, and each child process will create data. the data will ultimately need to be inserted into a db. Approach 1 --- if i have each child app write to a file, i'm going to have a serious hit on the disk, for the file i/o, but i'm pretty sure Centos/RH could handle it. (although, to be honest, i don't know if there's a limit to the number of simultaneous file descriptors that the OS allows to be open at the same time.) i'm assuming that the number is multiples of magnitudes more than the number of simultaneous connections i can have with a db i could then have a process/app collect the information from each output file, writing the information to the db, and deleting the output files as required. Approach 2 -- i could have each child app write to a local db, with each child app, waiting to get the next open db connection. this is limited, as i'd run into the max connection limit for the db. i'd also have to implement a process to get the information from the local db, to the master db. .. Approach 3 --- i could have each child app write directly to the db.. the problem with this approach is that the db has a max regarding the number of simultaneous connections, based on system resources. this would be the cleanest solution.. so... anybody have any thoughts/comments as to how one can essentially accept 1000's-1's of simultaneous hits with an app... i've been trying to find out if there's any kind of distributed parent/child/tiered kind of app, where information/data is more or less collected and received at the node level... does anyone know of a way to create a distributed kind of db app, where i can enter information into a db on a given server, and the information is essentially pulled into the master server from the child server... thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Web Seminar Events
Hello, Although I can't speak to the timing of the newsletter getting sent out. A list of upcoming webinars can be found at: http://www.mysql.com/news-and-events/web-seminars/ Feel free to respond to me privately any suggestions for future topics of interest and we'll try to make them happen. Thanks, Jimmy Guerrero Sr Product Manager MySQL, Inc -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Friday, August 25, 2006 12:13 PM To: MySQL List Subject: Web Seminar Events Why is it that the Newsletter goes out after the Web Seminar's have already occurred. There have been several that I would have liked to see, but I got the news letter a day after the event occurred. It would be nice to know at least a day ahead of time when these things are going to happen. Is there another list somewhere of scheduled Web Seminars? Thanks, Jesse -- 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]
Best MySQL client for Linux?
What's the best MySQL client for Linux? I'm using FC5 and only yesterday did the MySQL Query Browser start working on FC5. Before that I've been using the *OLD* mysqlcc which works, but is lacking some features. I think the QB is a more polished product, but is lacking some featuers of the older mysqlcc. I've also tried the Aqua Data Studio which seems decent but it requires java is a little slower. http://www.aquafold.com/ Anyone out there have a GREAT mysql client tool they can recommend? -- Scott Baker - RHCE Canby Telcom System Administrator 503.266.8253 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best MySQL client for Linux?
You don´t like phpmyadmin? Scott Baker [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] What's the best MySQL client for Linux? I'm using FC5 and only yesterday did the MySQL Query Browser start working on FC5. Before that I've been using the *OLD* mysqlcc which works, but is lacking some features. I think the QB is a more polished product, but is lacking some featuers of the older mysqlcc. I've also tried the Aqua Data Studio which seems decent but it requires java is a little slower. http://www.aquafold.com/ Anyone out there have a GREAT mysql client tool they can recommend? -- Scott Baker - RHCE Canby Telcom System Administrator 503.266.8253 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: file i/o operations...
Just getting that number of processes running I think would be a challenge. A setup I recently worked on runs a few hundred processes per box, and that kind of maxes out the CPU. Approach 1, been there, done that. Too messy. Approach 2, considered it, but you may end up with processes that never connect. You would need a queueing/scheduling mechanism. Essentially you would be trying to do what an OS does, manage resources to make sure every process gets it's turn. Approach 3, what we currently use. The processes connect to the db, does a bulk insert and then disconnects. We decided to limit each process to blocks of 100. Inserting a single record at a time will quickly degrade. This setup actually moved the bottleneck from the database to the processes doing their job. When each process starts, it inserts a record into a table and gets it's id. The process then handles the autoincrement value. The unique id for each record is then the process id plus the increment value. To really scale, you may want to look into the black hole table format. Essentially it's a black hole, nothing is saved so there really isn't much overhead. But you set it up to be replicated and a replication log is generated. An easy setup would be to have multiple tables on a master server, each table replicating a black hole table from another server. Then create a merge table encompassing the multiple tables for easy querying. This is the next idea we are pursueing, so it may or may not work. - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, August 25, 2006 1:12 PM Subject: file i/o operations... hi... i'm trying to determine which is the better way/approach to go. should an app do a great deal of file i/o, or should it do a great deal of read/writes to a mysql db... my test app will create a number of spawned child processes, 1000's of simultaneous processes, and each child process will create data. the data will ultimately need to be inserted into a db. Approach 1 --- if i have each child app write to a file, i'm going to have a serious hit on the disk, for the file i/o, but i'm pretty sure Centos/RH could handle it. (although, to be honest, i don't know if there's a limit to the number of simultaneous file descriptors that the OS allows to be open at the same time.) i'm assuming that the number is multiples of magnitudes more than the number of simultaneous connections i can have with a db i could then have a process/app collect the information from each output file, writing the information to the db, and deleting the output files as required. Approach 2 -- i could have each child app write to a local db, with each child app, waiting to get the next open db connection. this is limited, as i'd run into the max connection limit for the db. i'd also have to implement a process to get the information from the local db, to the master db. .. Approach 3 --- i could have each child app write directly to the db.. the problem with this approach is that the db has a max regarding the number of simultaneous connections, based on system resources. this would be the cleanest solution.. so... anybody have any thoughts/comments as to how one can essentially accept 1000's-1's of simultaneous hits with an app... i've been trying to find out if there's any kind of distributed parent/child/tiered kind of app, where information/data is more or less collected and received at the node level... does anyone know of a way to create a distributed kind of db app, where i can enter information into a db on a given server, and the information is essentially pulled into the master server from the child server... thanks -bruce -- 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]
Installing MySQL on Windows Server 2003 Standard x64 Edition
I'm having problems with MySQLInstanceConfig.exe - Unable To Locate Component This application has failed to start because LIBMYSQL.dll was not found. Re-installing the application may fix this problem I have tried to re-install, but I get the same message. I read the following related threads but didn't help. http://lists.mysql.com/win32/14799 http://lists.mysql.com/mysql/167676 I looked for the file and it exists in: /mysql/bin/ /mysql/lib/debug/ /mysql/lib/opt/ This is the download I'm using for the installation: Windows Server 2003 (AMD64 / Intel EM64T)5.0.24 Please advise. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: file i/o operations...
A couple of comments: - Simultaneous connections can be increased, but at some point the user than runs the mysqld process will run out of file handles it can allocate (each table takes 2 or 3). - If we are talking about a database server and test server being the same box then what are you trying to test. Once you exceed the number of processors on the box, the OS will just queue up the various processes and that will be the limit of scalablity. Unless you overlap real I/O with computation there is not much gain beyond a certain point. When you run out of memory for processes, its page to disk time (not a pleasent site). Not sure what you are testing here. BTW: please expain the 'black hole table'. Jut my $0.1 worth. Bill Brent Baisley said: Just getting that number of processes running I think would be a challenge. A setup I recently worked on runs a few hundred processes per box, and that kind of maxes out the CPU. Approach 1, been there, done that. Too messy. Approach 2, considered it, but you may end up with processes that never connect. You would need a queueing/scheduling mechanism. Essentially you would be trying to do what an OS does, manage resources to make sure every process gets it's turn. Approach 3, what we currently use. The processes connect to the db, does a bulk insert and then disconnects. We decided to limit each process to blocks of 100. Inserting a single record at a time will quickly degrade. This setup actually moved the bottleneck from the database to the processes doing their job. When each process starts, it inserts a record into a table and gets it's id. The process then handles the autoincrement value. The unique id for each record is then the process id plus the increment value. To really scale, you may want to look into the black hole table format. Essentially it's a black hole, nothing is saved so there really isn't much overhead. But you set it up to be replicated and a replication log is generated. An easy setup would be to have multiple tables on a master server, each table replicating a black hole table from another server. Then create a merge table encompassing the multiple tables for easy querying. This is the next idea we are pursueing, so it may or may not work. - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, August 25, 2006 1:12 PM Subject: file i/o operations... hi... i'm trying to determine which is the better way/approach to go. should an app do a great deal of file i/o, or should it do a great deal of read/writes to a mysql db... my test app will create a number of spawned child processes, 1000's of simultaneous processes, and each child process will create data. the data will ultimately need to be inserted into a db. Approach 1 --- if i have each child app write to a file, i'm going to have a serious hit on the disk, for the file i/o, but i'm pretty sure Centos/RH could handle it. (although, to be honest, i don't know if there's a limit to the number of simultaneous file descriptors that the OS allows to be open at the same time.) i'm assuming that the number is multiples of magnitudes more than the number of simultaneous connections i can have with a db i could then have a process/app collect the information from each output file, writing the information to the db, and deleting the output files as required. Approach 2 -- i could have each child app write to a local db, with each child app, waiting to get the next open db connection. this is limited, as i'd run into the max connection limit for the db. i'd also have to implement a process to get the information from the local db, to the master db. .. Approach 3 --- i could have each child app write directly to the db.. the problem with this approach is that the db has a max regarding the number of simultaneous connections, based on system resources. this would be the cleanest solution.. so... anybody have any thoughts/comments as to how one can essentially accept 1000's-1's of simultaneous hits with an app... i've been trying to find out if there's any kind of distributed parent/child/tiered kind of app, where information/data is more or less collected and received at the node level... does anyone know of a way to create a distributed kind of db app, where i can enter information into a db on a given server, and the information is essentially pulled into the master server from the child server... thanks -bruce -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: Installing MySQL on Windows Server 2003 Standard x64 Edition
It could seems stupid, but you tried to put this file on c:\windows\system32 or the similar folder of your system? David Lazo [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I'm having problems with MySQLInstanceConfig.exe - Unable To Locate Component This application has failed to start because LIBMYSQL.dll was not found. Re-installing the application may fix this problem I have tried to re-install, but I get the same message. I read the following related threads but didn't help. http://lists.mysql.com/win32/14799 http://lists.mysql.com/mysql/167676 I looked for the file and it exists in: /mysql/bin/ /mysql/lib/debug/ /mysql/lib/opt/ This is the download I'm using for the installation: Windows Server 2003 (AMD64 / Intel EM64T)5.0.24 Please advise. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: file i/o operations...
hi brent here's what i'm playing around with... i'm writing a very limited web parsing/scraping app... rather than do a sequential process, that's time consuming.. i'ver created/tested a kind of parallel app that quickly spawns a child app for each url i need to fetch. this can quickly generate 1000s of child processes, each of which is fetching a given page i know, this could easily kill a web server, and the app limits the workload on the server.. however, since the app does multiple (100s) of sites, the app can still generate 1000s of pages that are being fetched. at the same time, i have a network of servers, (10-20) each of which is doing the same thing.. fetching pages. so i have a need to create an architecture/structure to handle this mass of information and to slam it into the db as fast as possible... if i have a single central db, the apps will be waiting waaay too long to get a connection.. if i have a separate db for each server, and have each app(s) on the server write to the local db, then i'd have to have a process that somehow collects the local db information, and writes it to the master db.. doable, but this solution would also potentially have a wait, given the max connection limit of the db. so this is the dilema i'm facing. in searching google/academic articles.. i haven't come across a solution for this kind of issue... in looking at other crawlers (lucene/nutch/etc...) can't figure out if these apps have a solution that i can use. the basic problem as i've stated, boils down to trying to accept as much data as possible such that this aspect of the whole system isn't the bottleneck yeah, i know.. i'm greedy.. trying to download all of my required information from a given site in 10-20 mins! as opposed to hours -bruce -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Friday, August 25, 2006 1:45 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: file i/o operations... Just getting that number of processes running I think would be a challenge. A setup I recently worked on runs a few hundred processes per box, and that kind of maxes out the CPU. Approach 1, been there, done that. Too messy. Approach 2, considered it, but you may end up with processes that never connect. You would need a queueing/scheduling mechanism. Essentially you would be trying to do what an OS does, manage resources to make sure every process gets it's turn. Approach 3, what we currently use. The processes connect to the db, does a bulk insert and then disconnects. We decided to limit each process to blocks of 100. Inserting a single record at a time will quickly degrade. This setup actually moved the bottleneck from the database to the processes doing their job. When each process starts, it inserts a record into a table and gets it's id. The process then handles the autoincrement value. The unique id for each record is then the process id plus the increment value. To really scale, you may want to look into the black hole table format. Essentially it's a black hole, nothing is saved so there really isn't much overhead. But you set it up to be replicated and a replication log is generated. An easy setup would be to have multiple tables on a master server, each table replicating a black hole table from another server. Then create a merge table encompassing the multiple tables for easy querying. This is the next idea we are pursueing, so it may or may not work. - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, August 25, 2006 1:12 PM Subject: file i/o operations... hi... i'm trying to determine which is the better way/approach to go. should an app do a great deal of file i/o, or should it do a great deal of read/writes to a mysql db... my test app will create a number of spawned child processes, 1000's of simultaneous processes, and each child process will create data. the data will ultimately need to be inserted into a db. Approach 1 --- if i have each child app write to a file, i'm going to have a serious hit on the disk, for the file i/o, but i'm pretty sure Centos/RH could handle it. (although, to be honest, i don't know if there's a limit to the number of simultaneous file descriptors that the OS allows to be open at the same time.) i'm assuming that the number is multiples of magnitudes more than the number of simultaneous connections i can have with a db i could then have a process/app collect the information from each output file, writing the information to the db, and deleting the output files as required. Approach 2 -- i could have each child app write to a local db, with each child app, waiting to get the next open db connection. this is limited, as i'd run into the max connection limit for the db. i'd also have to implement a process to get the information from the local db, to the master db. .. Approach 3 --- i
Re: Installing MySQL on Windows Server 2003 Standard x64 Edition
I didn't add it anywhere because the file exists in mysql/bin folder c:/program files/mysql/bin Does it need to be somewhere else?? On 8/25/06 5:03 PM, João Cândido de Souza Neto [EMAIL PROTECTED] wrote: It could seems stupid, but you tried to put this file on c:\windows\system32 or the similar folder of your system? David Lazo [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I'm having problems with MySQLInstanceConfig.exe - Unable To Locate Component This application has failed to start because LIBMYSQL.dll was not found. Re-installing the application may fix this problem I have tried to re-install, but I get the same message. I read the following related threads but didn't help. http://lists.mysql.com/win32/14799 http://lists.mysql.com/mysql/167676 I looked for the file and it exists in: /mysql/bin/ /mysql/lib/debug/ /mysql/lib/opt/ This is the download I'm using for the installation: Windows Server 2003 (AMD64 / Intel EM64T)5.0.24 Please advise. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing MySQL on Windows Server 2003 Standard x64 Edition
On 8/25/06, David Lazo [EMAIL PROTECTED] wrote: I didn't add it anywhere because the file exists in mysql/bin folder c:/program files/mysql/bin Does it need to be somewhere else?? mm.. i don't know, you tell us !!! try it. !!! -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installing MySQL on Windows Server 2003 Standard x64 Edition
Go to Control PanelSystemAdvanced. Click Environmental Variables and Find the PATH variable under System Variables and add the full path, i.e. c:\Program Files\...\mysql\bin to the variable. -Original Message- From: David Lazo [mailto:[EMAIL PROTECTED] Sent: Friday, August 25, 2006 2:54 PM To: mysql@lists.mysql.com Subject: Installing MySQL on Windows Server 2003 Standard x64 Edition I'm having problems with MySQLInstanceConfig.exe - Unable To Locate Component This application has failed to start because LIBMYSQL.dll was not found. Re-installing the application may fix this problem I have tried to re-install, but I get the same message. I read the following related threads but didn't help. http://lists.mysql.com/win32/14799 http://lists.mysql.com/mysql/167676 I looked for the file and it exists in: /mysql/bin/ /mysql/lib/debug/ /mysql/lib/opt/ This is the download I'm using for the installation: Windows Server 2003 (AMD64 / Intel EM64T)5.0.24 Please advise. David. -- 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: Installing MySQL on Windows Server 2003 Standard x64 Edition
I still get the same error. Not sure what I'm doing wrong. On 8/25/06 5:49 PM, Greg Joss [EMAIL PROTECTED] wrote: Go to Control PanelSystemAdvanced. Click Environmental Variables and Find the PATH variable under System Variables and add the full path, i.e. c:\Program Files\...\mysql\bin to the variable. -Original Message- From: David Lazo [mailto:[EMAIL PROTECTED] Sent: Friday, August 25, 2006 2:54 PM To: mysql@lists.mysql.com Subject: Installing MySQL on Windows Server 2003 Standard x64 Edition I'm having problems with MySQLInstanceConfig.exe - Unable To Locate Component This application has failed to start because LIBMYSQL.dll was not found. Re-installing the application may fix this problem I have tried to re-install, but I get the same message. I read the following related threads but didn't help. http://lists.mysql.com/win32/14799 http://lists.mysql.com/mysql/167676 I looked for the file and it exists in: /mysql/bin/ /mysql/lib/debug/ /mysql/lib/opt/ This is the download I'm using for the installation: Windows Server 2003 (AMD64 / Intel EM64T)5.0.24 Please advise. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best MySQL client for Linux?
Omnipilot, the makers of the LASSO application server now offer a free developer version of lasso professional. It has the best web-based database browser and SQL utility that I've ever seen. It has native MySQL connectors, it's a snap to get up and running. Cory Scott Baker [EMAIL PROTECTED] wrote: What's the best MySQL client for Linux? I'm using FC5 and only yesterday did the MySQL Query Browser start working on FC5. Before that I've been using the *OLD* mysqlcc which works, but is lacking some features. I think the QB is a more polished product, but is lacking some featuers of the older mysqlcc.
Anyone tried solidDB for MySQL?
I've heard absolutely wonderful things about this transaction-safe storage engine. We're using InnoDB now and are always looking for the best solution as we scale our DB operations. Has anyone tried the SolidDB for MySQL beta stuff yet? What do you think of it? Cory.
Re: Occurrence-based ranking [solved?]
I managed to figure out a query that does what I want. SELECT listID, COUNT(*) AS Occurrences FROM componentsToLists WHERE componentID = ANY( SELECT components.id FROM components WHERE components.name IN(nut,bolt)) GROUP BY listID ORDER BY Occurrences DESC; This ranks a set of lists based on the number of named parts the contain in them. anyone either a) know how to rewrite this for version 4.1 or b) make it faster. -Ben On Aug 24, 2006, at 2:27 AM, Ben Lachman wrote: I am fairly new to SQL and have run into a problem I can't figure out. I am trying to construct a query that returns an occurrence ranked list of results. I have the following tables and fields: components id name componentsToPartsList --- componentID listID partsLists -- id name I want to get results ranked by the number of components listed in a query that are in the same list. For example, list id 1 with the name 'robot' has parts bolt (component id 1), pincer (component id 3), and wheel (component id 4). List id 2 with name 'nuts and bolts' has bolt and nut (component id 2) only in it. If I pass in bolt, pincer and wheel I'd like to get back first 'robot' and then 'nuts and bolts.' If I pass in nut and bolt I'd like to get back 'nuts and bolts' and then 'robot.' I have a query that will return the relevant partsListIDs: SELECT componentsToPartsList.listID FROM componentsToPartsList WHERE componentsToPartsList.componentID IN( (SELECT components.id FROM components WHERE components.name = nut), (SELECT components.id FROM components WHERE components.name = bolt); } But in the case of this query there are duplicates in the result. I would like to use these duplicates to rank (I assume using ORDER BY) the results based on the number of duplicates. Can anyone recommend a good method of doing this? -Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Occurrence-based ranking [solved?]
Ben
This ranks a set of lists based on the number of named parts
the
contain in them. anyone either a) know how to rewrite this
for version
4.1 or b) make it faster.
SELECT l.listID, COUNT(*) AS Occurrences
FROM componentsToLists AS l
INNER JOIN components AS c ON l.componentID=c.ID
WHERE c.name='nut' OR c.name='bolt'
GROUP BY l.listID
ORDER BY Occurrences DESC;
PB
-
Ben Lachman wrote:
I managed to figure out a query that does what I want.
SELECT listID, COUNT(*) AS Occurrences
FROM componentsToLists
WHERE componentID = ANY(
SELECT components.id
FROM components
WHERE components.name IN("nut","bolt"))
GROUP BY listID
ORDER BY Occurrences DESC;
This ranks a set of lists based on the number of named parts the
contain in them. anyone either a) know how to rewrite this for version
4.1 or b) make it faster.
-Ben
On Aug 24, 2006, at 2:27 AM, Ben Lachman wrote:
I am fairly new to SQL and have run into a
problem I can't figure out. I am trying to construct a query that
returns an occurrence ranked list of results.
I have the following tables and fields:
components
id
name
componentsToPartsList
---
componentID
listID
partsLists
--
id
name
I want to get results ranked by the number of components listed in a
query that are in the same list. For example, list id 1 with the name
'robot' has parts bolt (component id 1), pincer (component id 3), and
wheel (component id 4). List id 2 with name 'nuts and bolts' has bolt
and nut (component id 2) only in it. If I pass in bolt, pincer and
wheel I'd like to get back first 'robot' and then 'nuts and bolts.' If
I pass in nut and bolt I'd like to get back 'nuts and bolts' and then
'robot.' I have a query that will return the relevant partsListIDs:
SELECT componentsToPartsList.listID
FROM componentsToPartsList
WHERE componentsToPartsList.componentID IN(
(SELECT components.id FROM components WHERE components.name
= "nut"),
(SELECT components.id FROM components WHERE components.name
= "bolt");
}
But in the case of this query there are duplicates in the result. I
would like to use these duplicates to rank (I assume using ORDER BY)
the results based on the number of duplicates. Can anyone recommend a
good method of doing this?
-Ben
--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.405 / Virus Database: 268.11.6/427 - Release Date:
8/24/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.6/427 - Release Date: 8/24/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
