Re: *****SPAM***** when I installed mysql 3.23.43
Dave [ mysql query] > when I installed mysql 3.23.43 it instructed me to be sure to add a password with >the following results- > : > seaport2:/# /usr/mysql/bin/mysqladmin -u root -p password 'new-password' > Enter password: > /usr/mysql/bin/mysqladmin: connect to server at 'localhost' failed > error: 'Access denied for user: 'root@localhost' (Using password: YES)' Read the man pages for mysqladmin for full details on all options (there are lots of them ;-P). The option "-p" is the password you use to connect to the server, however on a default installation you should specify no password. Essentially what you're doing is saying: "Connect me to the server at 'localhost' with the password 'password' and user 'root'" ...which is NOT what you want to do because the default databases don't expect a password _at all_ [which is why it is strongly suggested you change the passsword] Drop the "-p" so it looks like: /usr/mysql/bin/mysqladmin -uroot password 'newpassword' Also, don't forget to flush the privileges after changing the password. DSL -- We are not the United States' ally We are the 53rd sovereign state of the Federation - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: *****SPAM***** Query problem
Amit, > I am trying to run the following query:- > > select red,green,blue from colorchanger where > colorcode = select value from jobattributekit where > attributekit = "Color" and jobnumber = > "28200124RB4-001" and scenario="JN75K"; It doesn't support subselects. It's having a problem with the second select... I believe that "subselects" are on the wish list and will (eventually) appear but not at the moment in a stable release. DSL mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
when I installed mysql 3.23.43
when I installed mysql 3.23.43 it instructed me to be sure to add a password with the following results- : seaport2:/# /usr/mysql/bin/mysqladmin -u root -p password 'new-password' Enter password: /usr/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' what am I doing wrong? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB on FreeBSD-Alpha problem
Yuri, - Original Message - From: "Yuri" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Saturday, August 17, 2002 1:41 AM Subject: InnoDB on FreeBSD-Alpha problem > Anyone i running MySQL with InnoDB > on FreeBSD-Alpha? > > I get the mysqld compiling/running > ok. DB dump is restored ok locally. > Locally everything wirks. But it what do you mean with this? If you issue SQL statements from the same computer, they work ok? > crashes on the incoming network connection. > > unaligned access: va=0x11fff784 pc=. If this only happens in an incoming network connection, then probably your network library is buggy. In Compaq Alpha C and C++ variables have to be aligned to machine addresses divisible by 4 or 8, which is not required in Intel x86. Looks like some variable in the library is not aligned. > Anyone knows the solution? > > Thanx, > Yuri. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query problem
Hi all Does mysql support simple joins? I am trying to run the following query:- select red,green,blue from colorchanger where colorcode = select value from jobattributekit where attributekit = "Color" and jobnumber = "28200124RB4-001" and scenario="JN75K"; ColorChanger Table:- ColorCode ColorRedGreenBlue STBBLUE 58 110 165 JOBATTRIBUTEKIT Table:- Jobnumber AttributekitValueScenario 28200124RB4-001 Color STB JN75K The error that it gives is :- ERROR 1064: You have an error in your SQL syntax near 'select value from jobattr ibutekit where attributekit = "Color" and jobnumber = "' at line 1 Thanks Amit __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto increment as primary key
In the last episode (Aug 16), Fr. Robert Bower said: > Is it a good idea to use an auto increment field as a primary key in mysql? > I know in some products like paradox it is a bad idea because of possible > corruption problems down the road? If you get corrupt databases, don't blame the primary key. Blame the databse software. :) You almost always want your autoincrement field to be your primary key, no matter what database you're using. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: For all you people looking for mysql shirts ;-)
At 09:59 PM 8/16/2002, you wrote: >Hi guys, > >Just an FYI, We've finally decided to license out our logo to >CafePress.com >with the MySQL Developer logo. You can buy shirts, mugs, tote bags and >more >If you have a chance please check it out. Here is the link > > http://www.cafeshops.com/cp/store.aspx?s=mysqldeveloper.0 > > >P.S. Sorry in advance if anyone is offended by this post. What??? No boxer shorts?? Bummer. Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto increment as primary key
Father, [ mysql, query ] > Is it a good idea to use an auto increment field as a primary key in mysql? > I know in some products like paradox it is a bad idea because of possible > corruption problems down the road? I don't believe there would be any problems in the latest versions of MySQL; obviously you're not going to be manually resetting the index manually. Just remember, that primary keys really aren't reusable so choose a column type that will be sufficient for your purposes. Something like: ... id int unsigned not null auto_increment primary key ... DSL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How do I uninstall mySQL?
> How do I uninstall mySQL? MSWindows or *NIX? Which one? (And have you searched the archives? I think I've seen this question answered before, perhaps depending on your OS and version.) -- Joel Rees <[EMAIL PROTECTED]> - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
auto increment as primary key
Is it a good idea to use an auto increment field as a primary key in mysql? I know in some products like paradox it is a bad idea because of possible corruption problems down the road? Thanks In Christ, Father Robert St. Nectarios Orthodox Church [EMAIL PROTECTED] http://www.stnectarios.org For we preach not ourselves, but Christ Jesus the Lord; and ourselves your servants for Jesus' sake. (2 Cor 4:5) KJV --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.381 / Virus Database: 214 - Release Date: 8/2/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
For all you people looking for mysql shirts ;-)
Hi guys,
Just an FYI, We've finally decided to license out our logo to
CafePress.com
with the MySQL Developer logo. You can buy shirts, mugs, tote bags and
more
If you have a chance please check it out. Here is the link
http://www.cafeshops.com/cp/store.aspx?s=mysqldeveloper.0
P.S. Sorry in advance if anyone is offended by this post.
FILTER FODDER{ SQL, SELECT, DATABASE }
--
Colin Faber
(303) 736-5160
fpsn.net, Inc.
* Black holes are where God divided by zero. *
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
I'd like to thank everyone for giving such helpful and detailed responses; it's the sort of thing that shows me how good the support here can be! I did want to address some things that may not have been clear enough in my original message. When I said "not speed" I didn't mean that speed wasn't important at all. It's that I'm pretty confident that MySQL is more than fast enough for what we need, and when I see MySQL vs. Oracle questions here they always tend to be about benchmarking, about convincing people that MySQL is fast enough. I don't think that's the case, and I know that we'll never be in a situation where we need something monstrously huge (ten thousand simultaneous users doing complex queries on tables with millions of rows) that might genuinely require something much bigger. I'll respond to John's points but most of the things he asks have already been pointed out by other people. On Friday, August 16, 2002, at 08:25 AM, John Griffin wrote: > Hi Elizabeth, > > The first question I would ask why don't you want Oracle? If you can't > come up with a good business reason why your company shouldn't go with > Oracle I would say you have already lost the battle. As others have pointed out, Oracle is much more expensive, and that alone is enough of a business reason not to use it, all other things being equal. It's determining the extent to which other things _are_ equal that this is about-- if Oracle has a lot of features that we don't need, it doesn't matter that it has them. > The second question is who is making the purchasing decision? If it's > middle management or the bean counters then you have pretty well lost > again because nobody ever lost their job for buying Oracle. This is perhaps the biggest problem--they could just not listen to anything and go with the safe decision. > MySQL also has what some people consider fairly serious drawbacks. > MySQL does not support triggers or foreign key constraints (yet) so > data integrity is always at risk. There is no equivalent of PL/SQL in > MySQL, all database procedures etc. must be written in a 3GL, such as > C, and then linked in. Since we'd be doing everything from scratch, many of these don't really apply, as there are usually workarounds that can be written in the application language (and as someone pointed out, foreign keys are available right now in InnoDB anyway). And the more we rely on things like PL/SQL the more difficult it is to ever migrate. > If you feel your shop should become a MySQL shop I suggest you look at > the business reasons why and use those reasons to argue your case for > you. Technical coolness or altruistic support of the open source > movement doesn't cut it with most managers. Productivity, cost, and > support usually does. I appreciate that, and I do hope that I can get management to listen to my reasons. If I can convince them that productivity, cost, and support will be just fine with MySQL, it would be nice to have the technical coolness and altruistic support of the OS movement as icing on the cake! Thanks again to everyone who took the time to reply. I certainly didn't meant to start a MySQL vs. MS-SQL battle either. Elizabeth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed) - not part of the rant, but real information
Adam, [ mysql, query ] > However, you can do it manually (I think) by playing around with both > the order of the where clause and the order of the join clause Yes. > Also, MySQL has a really wimpy default configuration (I can't figure out > why). Here is my /etc/my.cnf (I don't know what the equivalent is on > windows) Most likely to cater for people running MySQL on servers without a lot of memory. Fancy having a key-buffer of 256M if you only had 128M of real memory... DSL (ps: how the world has this discussion gone from Oracl to Micorosft SQL Server...lol) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AUTO_INCREMENT PROBLEM
In the last episode (Aug 17), delz said: > I'm using phpmyadmin to create database on Mysql server. I want my > idx to start at 1800 then implement auto increment. How do i do it > using phpmyadmin? send the following command: ALTER TABLE mytable AUTO_INCREMENT = 1700; In general, phpmyadmin questions should go to a phpmyadmin mailing list. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AUTO_INCREMENT PROBLEM
Hi All, Good day !!! I'm using phpmyadmin to create database on Mysql server. I want my idx to start at 1800 then implement auto increment. How do i do it using phpmyadmin? Regards, Delz, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqlbinlog - doesn't work on some log files
Hi, I deleted a db I didn't mean to. I am using mysql 4.0.1 Alpha and some innodb and some myisam tables. I have the log files I need to restore my data since I have been running since the last backup with binlog enabled in my.cnf. The problem is that mysqlbinlog shows some log files, but other log files choke. In VI I can see this at the top of one of one log file: þbin¢>.=.K.. 0010 00 01 00 00 00 00 00 02 00 34 2e 30 2e 31 2d 61 .4.0.1-a 0020 6c 70 68 61 2d 6c 6f 67 00 00 00 00 00 00 00 00 lpha-log 0030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0040 00 00 00 00 00 00 00 00 00 00 00 a2 3e 06 3d 77 ...¢>.=w 0050 40 06 3d 02 01 00 00 00 f2 00 00 00 02 00 00 00 @.=.ò... 0060 00 00 c1 1b 00 00 00 00 00 00 0a 00 00 73 65 6e ..Á..sen 0070 73 69 74 69 6c 6c 65 00 55 50 44 41 54 45 20 75 sitille.UPDATE u 0080 61 5f 70 72 69 6d 61 72 79 5f 68 69 74 73 20 53 a_primary_hits S 0090 45 54 20 31 31 64 61 79 20 3d 20 28 31 31 64 61 ET 11day = (11da 00a0 79 20 2b 20 31 29 2c 20 31 31 75 5f 64 61 79 20 y + 1), 11u_day 00b0 3d 20 28 31 31 75 5f 64 61 79 20 2b 20 31 29 2c = (11u_day + 1), 00c0 20 4d 6f 6e 74 68 5f 54 6f 74 61 6c 20 3d 20 28 Month_Total = ( 00d0 4d 6f 6e 74 68 5f 54 6f 74 61 6c 20 2b 20 31 29 Month_Total + 1) 00e0 2c 20 4d 6f 6e 74 68 5f 55 6e 69 71 75 65 20 3d , Month_Unique = 00f0 20 28 4d 6f 6e 74 68 5f 55 6e 69 71 75 65 20 2b (Month_Unique + but mysqlbinlog only shows this for this 4.8M file: root@www194:/home/back# mysqlbinlog www194-bin.001 # at 4 #020611 11:17:06 server id 1 Start: binlog v 1, server v created 691231 16:00:00 # at 73 #030419 19:03:44 server id 104888125 Query thread_id=15859712 exec_time=131072 use ; LOAD DATA INFILE '' REPLACE INTO TABLE nsitille OPTIONALLY ENCLOSED BY '\0' ESCAPED BY '\0' LINES STARTING BY '\0'; I had been running this machine as a Master to a remote slave. And some of the logs are large because I ran LOAD DATA FROM MASTER a few times from the slave machine. So I have the data I need but I can't access it. I have a total of 8 log files, only two work with mysqlbinlog. The rest return very shortly like above. This is really freaking me out, if this doesn't work, then I will have to start not trusting the log at all, which would suck. I have been able to restore data before, in the same way, from this same database and config. Thanks, Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Urgent: lost connections,
Lo everyone, I've implemented replication on my MySQL Server... The master is mysql 3.23 (FreeBSD Ports), and my one and only slave is mysql 3.23 (Win32). I added the slave so that I can have a hardcopy / backup database in the event that I have a data loss on the master. This is all very well, and working like a charm. However, mysql client, php, apache, anything that connects to the master database now all of a sudden randomly and intermittently loses their connection to the master MySQL Server, and the master server also crashes quite often. Taken from the error log: /usr/local/libexec/mysqld: ready for connections mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=12288 record_buffer=131072 sort_buffer=1048568 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 115211 K bytes of memory Hope that's ok, if not, decrease some variables in the equation And the browser reports a standard PHP error: Lost connection to MySQL server during query Any advice would be gladly appreciated. --me - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Server Error
Thanks alot! -James -Original Message- From: Oscar Rylin [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 3:05 PM To: 'James Kelty'; [EMAIL PROTECTED] Subject: RE: Server Error http://www.geocrawler.com/archives/3/8/2002/6/0/9054036/ / Oscar Rylin -Original Message- From: James Kelty [mailto:[EMAIL PROTECTED]] Sent: den 16 augusti 2002 22:57 To: [EMAIL PROTECTED] Subject: Server Error Hello, Does anyone know, off the top of their head, what server_errno=1159 on a SLAVE means? Replication is working, but I just want to know what that is. Thanks. -James sql query James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Manual availability
Course, you could just be v. nice to me - I made it into separate pages in 2 frames for much the same reason. :-) donna ( v. 3.23.34) On Friday 16 August 2002 9:21 pm, Frank Shute wrote: > I'm using 3.23.51 and I wondered if the manual was available as > separate pages of html in a tarball. > > The manual as shipped is a single page and as this machine's dog slow > and takes an age to jump from one part of the manual to another, > I thought separate pages might improve matters. > > I can only find the manual for 4.0 at mysql.com in the format I > desire. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Persisten Connections
At 08:40 AM 8/14/2002, you wrote: >I am running a website which generates around 100,000 pageviews a day and I >am wondering if I stop using persistent conections to the MySQL database and >use >normal open and close conections this would reduce the load onto my server? > >Most conections are either made through my "main" file or the phorum message >board system. > >Thanks in advance >John Wards John, There are other ways to speed up your PHP application besides (not) using persistent connections. Take a look at http://php.weblogs.com/tuning_apache_unix for some ideas. :) Mike I would certainly like to see a benchmark that shows "when" persistent connections work for MySQL. If it were graphed properly, perhaps with session_timeout vs memory/cpu and is plotting pages/second, we could figure out if persistent connections would benefit our particular application with the hardware we're using. I think there are too many variables to say "yes it works", or "no it doesn't" in all cases. By graphing it, we'd get a better understanding of how database performance is affected by persistent connections vs hardware (# CPU's & memory). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Server Error
http://www.geocrawler.com/archives/3/8/2002/6/0/9054036/ / Oscar Rylin -Original Message- From: James Kelty [mailto:[EMAIL PROTECTED]] Sent: den 16 augusti 2002 22:57 To: [EMAIL PROTECTED] Subject: Server Error Hello, Does anyone know, off the top of their head, what server_errno=1159 on a SLAVE means? Replication is working, but I just want to know what that is. Thanks. -James sql query James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
slow query in MySQL 4
A simple query in MySQL 4 that gets a count from 3 inner joined tables: select count(*) as C from quotes q inner join articles a using (article_id) inner join media m using (media_id) article_id and media_id are int(11), and are the primary keys for their respective tables. When used as foreign keys, they are also int(11), and are indexed. It's taking about 10 seconds to return the count, ~3000, from tables that aren't all that big (quotes: 3000, articles: 100,000; media: 1500). I plan to use the new MySQL 4 row count feature soon, but the code I'm working on needs to work on both MySQL 3 and 4 for now. I believe that the query works significantly faster on MySQL 3. Any ideas? Thx, Tac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
The explain command is still a little confusing new to me, but... does this > ;tempsap;ALL;10019; ... not mean that MySQL is not recognizing the indexes on the tempsap - AdminProducer join? > > -Original Message- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 10:21 AM > To: Mary Stickney; Francisco; Elizabeth Bogner; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Hi, > > Which columns are indexed in your table ? > What does EXPLAIN into MySQL return ? > > Regards, > Jocelyn > - Original Message - > From: "Mary Stickney" <[EMAIL PROTECTED]> > To: "Francisco" <[EMAIL PROTECTED]>; "Elizabeth Bogner" > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 4:31 PM > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > I am not for one or the other I just hate to wait > > I need speed... > > > > we already have a MS-SQL server , so no more money needs to me spent... > > > > I did a 4 table join , drwing 3 years of sales data > > > > table one 6.5 million records --- AdminHierarchy > > table two 1.5 million records AdminCoverage > > table three 10191 records --- AdminProcuder > > table four 19823 records --- AdminProduct > > > > SELECT AdminHierarchy.WritingAgentID, AdminHierarchy.WritingAgentSlot, > > AdminHierarchy.ProducerID, > > tempsap.taxid, (ModalPremium * BillModeID * (PercentOfCase / 100)) AS > > TotalPaidPremium, > > AdminCoverage.CoverageID, AdminCoverage.CoverageIDSbc, > > AdminHierarchy.RegionCode, > > AdminProduct.LobId, AdminCoverage.StatusID, AdminCoverage.StatusDate, > > AdminCoverage.InitialPremiumDate, AdminCoverage.PaidToDate, > tempsap.GROUPID > > FROM AdminHierarchy > > INNER JOIN AdminCoverage ON > > AdminHierarchy.CoverageID=AdminCoverage.CoverageID > > AND AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc > > LEFT JOIN AdminProducer ON > > AdminProducer.ProducerID=AdminHierarchy.WritingAgentID > > LEFT JOIN AdminProduct ON AdminCoverage.ProductID=AdminProduct.ProductID > > Left join tempsap on AdminProducer.taxid = tempsap.taxid > > WHERE AdminCoverage.InitialPremiumDate >= '2101' AND > > AdminCoverage.InitialPremiumDate <= '20020430' > > ORDER BY AdminHierarchy.WritingAgentSlot,AdminCoverage.CoverageId, > > AdminCoverage.CoverageIdSbc, > > AdminHierarchy.ProducerID > > > > > > > > -Original Message- > > From: Francisco [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 9:21 AM > > To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > Hi Mary, > > > > I am not specially against or pro MySQL, Microsoft SQL > > Server, Oracle or any other database. Teams make their > > choices based on the project needs such as budget (is > > your team ready to spend thousands of dollars on > > Oracle and marry that corporation forever?), > > deployment (do you want your product with Oracle's > > price tag attached to it?), functionality: does the > > database server provide a viable solution technically > > speaking?, etc, etc. So it is not an issue of good or > > bad. > > > > But regardless of all that, what we should have, at > > least, is a great respect for people that has been > > working so hard to provide an affordable and viable > > alternative to the database server giants. I don't > > think that throwing those numbers without any other > > explanations about your test environments, SQL, > > tables, etc is a good practice. Do you think that you > > always get what you pay when you spend thousand of > > dollars in software and services without leaving you > > any other choices? > > > > I know that you did not put bad intentions behind your > > comment but should be more careful and precise. > > > > Whoever is interested on some benchmarks can go to: > > http://www.mysql.com/information/benchmarks.html > > > > There is an interesting article comparing Ms-SQL, > > Oracle, DB2 and MySQL in: > > http://www.pcmag.com/article2/0,4149,7279,00.asp > > > > Sincerely, > > > > Francisco > > > > --- Mary Stickney <[EMAIL PROTECTED]> wrote: > > > > > > I have been doing speed tests the same query > > > ran on MYSQL took 45 > > > minutes > > > on MS-SQL it took 11 minutes.. > > > > > > yes you do get what you pay for > > > > > > -Original Message- > > > From: Francisco [mailto:[EMAIL PROTECTED]] > > > Sent: Friday, August 16, 2002 8:47 AM > > > To: Mary Stickney; Elizabeth Bogner; > > > [EMAIL PROTECTED] > > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > > > Hi, > > > > > > I am beging using MySQL for quite a while and it is > > > a > > > very good choice if you don't really need stored > > > procedures. MySQL provides a pretty good > > > implementation of a subset of MySQL-92, performance > > > is > > > great, it is cross-platform, provides transactions, > > > and its price... well is free. > > > > > > Hope it helps. > > > --- Mary Stickney <[EMAIL PROTECTED]> wrot
How do I get rid of the MySql column in Welcome to phpMyAdmin 2.2.6page?
How do I get rid of the MySql column in Welcome to phpMyAdmin 2.2.6 page? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Access to MySQL
Take a look at MyODBC... .mark > -Original Message- > From: Oladejo, Tokunboh [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 10:48 AM > To: [EMAIL PROTECTED] > Subject: Access to MySQL > > > Hi All, > > Does anyone knows of any easy way/short cut of exporting > Microsoft access > tables into MySql database. > > Thanx > All > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB on FreeBSD-Alpha problem
Anyone i running MySQL with InnoDB on FreeBSD-Alpha? I get the mysqld compiling/running ok. DB dump is restored ok locally. Locally everything wirks. But it crashes on the incoming network connection. unaligned access: va=0x11fff784 pc=. Anyone knows the solution? Thanx, Yuri. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Access to MySQL
If your table(s) are already set to comply with MySQL's much stricter formatting standards (e.g., spaces in field names, etc), then you can simply create an ODBC connection and "export" your table to the MySQL database. That does not always work, and does not always work well. But it is pretty simple. A sounder approach is to create the table in MySQL, then export the table as a tab-delimited text file, and use the "load data...infile" statement -- though I suspect you're looking for a shortcut from doing just exactly that. There are some tools out there that will help. There's an Access module you can use (the name escapes me now) that takes an Access table and does a dump of it into the proper format for import into MySQL. Mascon, which is shareware but relatively cheap, has an outstanding import tool. Best I've seen, anyway. HTH aron > -Original Message- > From: Oladejo, Tokunboh [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 1:48 PM > To: [EMAIL PROTECTED] > Subject: Access to MySQL > > > Hi All, > > Does anyone knows of any easy way/short cut of exporting Microsoft access > tables into MySql database. > > Thanx > All > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
High Availability and High Performance with MySQL
Hi, We have a MySQL+InnoDB server on Linux-Debian as ISP mail solution in conjunction with Postfix and DBmail and a Webmail in PHP/MySQL. The mail database is now 4.8Gb size for about 210 users on IMAP and POP3. The database have to grow to more than 1000 or perhaps 2000 users. We must in a next future migrate the database in clustering mode, first to load balance the large amount of queries on several server to keep an high level of performance, and second to have an high availability system, this is critical for us. We have explored several solutions but we need some advises: 1- We don't agree with the standard MySQL (>4.O) replication system because it's a one way replication, master-->slave(s). 2- Does the LinuxDirector(load balancer) with several MySQL servers and Coda distributed filesystem can work safely ? 3- We look the OpenMosix + oMSF http://openmosix.sourceforge.net/ (CC Cluster) but at this time there is a problem with the shared memory, a DSM is in development, does the right way to explore ? 4- We have take a look at a Spread solution (www.spread.org) for MySQL as the Backhand project for Apache (www.backhand.org) to replicate the changes on whole servers and balance the queries with LinuxDirector. This solution have to be developed, if there is some volunteer :-) Or perhaps have you others propositions, otherwise we have to look PostgreSQL and DBBalancer :-( Since MySQL-Max and the future stable 4.x versions, MySQL become a major database server for critical applications and the need of high availability and high performance offer by cluster will be more and more useful. Thanks in advance for your comments. Sam. -- Sam Przyswa - Chef de projet Arial Concept - Intégrateur Internet 36, rue de Turin - 75008 - Paris Tel: 01 40 54 86 04 - Fax: 01 40 54 83 01 Web: http://www.arial-concept.com - Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How do I uninstall mySQL?
mySQL, How do I uninstall mySQL? Scott - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
ok fixed thoes every little bit helps... I hope. -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 1:00 PM To: Mary Stickney; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) I also noticed the field where you make the join are not exactly of the same type : e.g : AdminHierarchy.CoverageID=AdminCoverage.CoverageID in adminHierarchy : CoverageID varchar(15) NOT NULL in admincoverage : CoverageID varchar(10) NOT NULL AdminCoverage.ProductID=AdminProduct.ProductID in adminCoverage : ProductId varchar(11) NOT NULL , in adminProduct : ProductID varchar(15) NOT NULL , AdminProducer.taxid = tempsap.taxid in adminProducer : TaxID varchar(9) NOT NULL , in tempsap : TaxID varchar(10) NOT NULL , If you want to maximize the performance you should use the same field type. - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Jocelyn Fournier" <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 7:51 PM Subject: RE: MySQL vs. Oracle (not speed) > > I hope this makes it faster , the program just got done , it ran from 7:00 > am yesterday until noon today. > and guess what , one of my report columns is wrong , I made a boo boo in the > persistency calculation > > at least it friday... > > > -Original Message- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 12:25 PM > To: Mary Stickney > Subject: Re: MySQL vs. Oracle (not speed) > > > Ok, so it's already a MyISAM table. You should be able to add index on > WritingAgentSlot with > > ALTER TABLE adminhierarchy ADD INDEX (WritingAgentSlot); > > - Original Message - > From: "Mary Stickney" <[EMAIL PROTECTED]> > To: "Jocelyn Fournier" <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 7:22 PM > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > ;Table;Create Table > > ;Table;Create Table > > ;AdminHierarchy;CREATE TABLE `adminhierarchy` ( > > `Source` char(2) NOT NULL default '', > > `WritingAgentID` varchar(15) NOT NULL default '', > > `CoverageID` varchar(15) NOT NULL default '', > > `AgentLevelID` varchar(15) NOT NULL default '', > > `ProducerID` varchar(15) NOT NULL default '', > > `ProducerTypeID` varchar(5) NOT NULL default '', > > `PercentOfCase` double default NULL, > > `RegionCode` varchar(5) default NULL, > > `CoverageIdSbc` varchar(10) NOT NULL default '', > > `WritingAgentSlot` int(11) default NULL, > > `RadDistributionCode` varchar(5) default NULL, > > KEY `WritingAgentIDIndex` (`WritingAgentID`), > > KEY `AgentLevelIDIndex` (`AgentLevelID`), > > KEY `CoverageIDIndex` (`CoverageID`), > > KEY `ProducerIDIndex` (`ProducerID`), > > KEY `ProducerTypeIDIndex` (`ProducerTypeID`), > > KEY `CoverageIdSbcIndex` (`CoverageIdSbc`), > > KEY `CovIdCovIdSbcIndex` (`CoverageID`,`CoverageIdSbc`) > > ) TYPE=MyISAM > > > > > > > > -Original Message- > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 12:16 PM > > To: Mary Stickney > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > SHOW CREATE TABLE AdminHierarchy; > > > > It must show what kind of table you are using. > > > > I don't think it change anything for the other tools. Just try to create a > > table in MyISAM format and see if it's correctly handled by Segate > Analysis, > > etc. > > If not, just go back to ISAM (ALTER TABLE AdminHierarchy type=ISAM, and so > > on). > > > > > > - Original Message - > > From: "Mary Stickney" <[EMAIL PROTECTED]> > > To: "Jocelyn Fournier" <[EMAIL PROTECTED]> > > Sent: Friday, August 16, 2002 7:14 PM > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > > > > ok I am on a quest to find out what type we are using. and if changeing > > them > > > will affect > > > other things. like the Segate Analysis that the Clients use for adhoc > > > reporting > > > > > > -Original Message- > > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > > Sent: Friday, August 16, 2002 12:07 PM > > > To: Mary Stickney > > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > > > > Ok, so you have to convert them to MyISAM (you should have a great > > > improvement in speed then). > > > > > > Just use > > > > > > ALTER TABLE AdminHierarchy type=MyISAM; > > > ALTER TABLE AdminCoverage type=MyISAM; > > > ALTER TABLE AdminProducer type=MyISAM; > > > ALTER TABLE AdminProduct type=MyISAM; > > > ALTER TABLE tempsap type=MyISAM; > > > > > > Then you should be able to add the index on the WritingAgentSlot column. > > > > > > - Original Message - > > > From: "Mary Stickney" <[EMAIL PROTECTED]> > > > To: "Jocelyn Fournier" <[EMAIL PROTECTED]> > > > Sent: Friday, August 16, 2002 7:03 PM > > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > > > > > > > > no I am not.. I just started using this system 3 weeks ago. > > > > > > > > > > > > -Original Message- > > > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED
Re: Access to MySQL
This solution worked really well for me: www.cynergi.net/exportsql/ Donna On Friday 16 August 2002 6:48 pm, Oladejo, Tokunboh wrote: > Hi All, > > Does anyone knows of any easy way/short cut of exporting Microsoft access > tables into MySql database. > > Thanx > All > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> Trouble > unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Server Error
Hello, Does anyone know, off the top of their head, what server_errno=1159 on a SLAVE means? Replication is working, but I just want to know what that is. Thanks. -James sql query James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
You can index fields with nulls. You can't make into a primary key, that's all. > -Original Message- > From: Mary Stickney [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 12:38 PM > To: Jocelyn Fournier; Mark Matthews; [EMAIL PROTECTED] > Subject: RE: MySQL vs. Oracle (not speed) > > > > looks to me like I can only make indexes on fields that are not null... > this one is not , not null > > > -Original Message- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 11:34 AM > To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Hi, > > I see you do an order by on AdminHierarchy.WritingAgentSlot but > this column > is not indexed. > Please tell me if an index on this column improve the speed of the query a > bit. > > Regards, > Jocelyn > - Original Message - > From: "Mary Stickney" <[EMAIL PROTECTED]> > To: "Mary Stickney" <[EMAIL PROTECTED]>; "Jocelyn Fournier" > <[EMAIL PROTECTED]>; "Mark Matthews" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 6:29 PM > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > incidentaly... Primary keys cant not be made on most of these tables due > to > > the fact that > > the farther back in time you got the more screwed up the data is... > > Fields have been added and they did not have the info to fill in the > > blanks... > > > > > > -Original Message- > > From: Mary Stickney [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 11:17 AM > > To: Jocelyn Fournier; Mark Matthews; [EMAIL PROTECTED] > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > > I am getting the taxid's I need from here so as not to try ane merge the > > entire table.. > > and there are 833... > > > > CREATE TABLE tempsap ( > > TempSapRecNum mediumint(9) NOT NULL auto_increment, > > StatusCode char(3) , > > Company varchar(10) , > > FirstYear varchar(4) , > > SecondYear varchar(4) , > > ThruDate varchar(12) , > > WritingAgentID varchar(10) , > > GroupID varchar(10) , > > TaxID varchar(10) NOT NULL , > > RegionCode varchar(10) , > > RegionName varchar(50) , > > AgentName varchar(50) , > > NewCasePercent double , > > FirstYearSales decimal(10,2) , > > SecondYearSales decimal(10,2) , > > AnnualPremium decimal(10,2) , > > PercentOfCases double , > > PremiumsInforce decimal(10,2) , > > ThirdYear varchar(4) , > > ThirdYearSales decimal(10,2) , > > Producerid varchar(20) , > > DistributionCode varchar(10) , > > TotalPaidPremium decimal(10,2), > > PRIMARY KEY (TempSapRecNum), > > KEY taxid (TaxID) > > ); > > > > > > -Original Message- > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 11:01 AM > > To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > Could you please send also tempsap ? > > > > Thanks and regards, > > Jocelyn > > > > - Original Message - > > From: "Mary Stickney" <[EMAIL PROTECTED]> > > To: "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Friday, August 16, 2002 4:55 PM > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > this is a read only datawarehouse database we refresh monthly from > the > > > antiquated mainframe we have > > > > > > ok here are the tables > > > > > > Adminhierarchy 6.5 million records > > > CREATE TABLE adminhierarchy ( > > > Source char(2) NOT NULL , > > > WritingAgentID varchar(15) NOT NULL , > > > CoverageID varchar(15) NOT NULL , > > > AgentLevelID varchar(15) NOT NULL , > > > ProducerID varchar(15) NOT NULL , > > > ProducerTypeID varchar(5) NOT NULL , > > > PercentOfCase double , > > > RegionCode varchar(5) , > > > CoverageIdSbc varchar(10) NOT NULL , > > > WritingAgentSlot int(11) , > > > RadDistributionCode varchar(5), > > > KEY WritingAgentIDIndex (WritingAgentID), > > > KEY AgentLevelIDIndex (AgentLevelID), > > > KEY CoverageIDIndex (CoverageID), > > > KEY ProducerIDIndex (ProducerID), > > > KEY ProducerTypeIDIndex (ProducerTypeID), > > > KEY CoverageIdSbcIndex (CoverageIdSbc), > > > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc) > > > ); > > > > > > admin coverage 1.5 million > > > CREATE TABLE admincoverage ( > > > Source char(2) , > > > IsRider char(1) , > > > CoverageID varchar(10) NOT NULL , > > > CoverageIdSbc varchar(10) NOT NULL , > > > ParentCoverageID varchar(10) NOT NULL , > > > GroupID varchar(10) NOT NULL , > > > EmployeeID varchar(10) NOT NULL , > > > ProductId varchar(11) NOT NULL , > > > OriginalCertificateNumber varchar(20) , > > > StatusID varchar(10) NOT NULL , > > > ApplicationDate date , > > > effectivedate date NOT NULL , > > > PaidToDate date NOT NULL , > > > InitialPremiumDate date NOT NULL , > > > StatusDate date , > > > BenefitAmount double , > > > ModalPremium double , > > > AnnualPremium double , > > > AccidentElim smallint(6) , > >
RE: Replication Problem
Apparently I had list File permissions with my user. Thanks anyway. -James -Original Message- From: James Kelty [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 11:06 AM To: [EMAIL PROTECTED] Subject: Replication Problem Hello, I have this replication problem. I am getting this error on the slave: 020816 16:30:49 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020816 16:30:49 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 4 020816 16:30:49 Error reading packet from server: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (server_errno=1045) But I have verified through the command line that this user has the ability to contact the master, use the database, and that the password in /etc/my.cnf if correct. Any other reason that this is failing? -James sql query James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
I'm sorry, but are you and I reading the same thread here? :) > -Original Message- > From: Serge Paquin [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 12:01 PM > To: Mark Matthews > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > I think this is a touch on the negative side. I'm sure many > people get soured on MySQL when they to a post here and get > yelled at for not trying hard enough. It sounds like Mary is > having a valid problem. Her query works fast in one database and > slow in the other. Because she did not come out and put her > table schema and sql in the first email is no reason to insult > her. I think a helping hand is a better approach. Mabey a > couple general suggestions on MySQL tuning and a "We would need > to see the schema and SQL statement to help further". I agree > that it seems very strange that MS-SQL runs fater than MySQL on a > query. Microsoft seems to always be plaiged with performance > problems. Insulting someone will not help convert them to MySQL > it will drive people away. > > Anyway enough of my rant... > > Serge. > > On Fri, 16 Aug 2002 09:46:44 -0500 > "Mark Matthews" <[EMAIL PROTECTED]> wrote: > > > Mary Stickney wrote: > > > What exactly is Trolling > > > > > > > > > I find MYSQL to be slow , sorry if that doesn't met with your > approval. > > > > But you don't give examples. > > > > I've found MS-SQL to be slow at times, especially when used from JDBC, > > but I don't make crack comments about it newsgroups without backing up > > my claims. > > > > Why don't you show us your queries? Do you really care to learn > if MySQL > > could be faster and you're just using it wrong? > > > > -Mark > > > > > > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Manual availability
I'm using 3.23.51 and I wondered if the manual was available as separate pages of html in a tarball. The manual as shipped is a single page and as this machine's dog slow and takes an age to jump from one part of the manual to another, I thought separate pages might improve matters. I can only find the manual for 4.0 at mysql.com in the format I desire. -- Frank *-*-*-*-*-*-*-*-*-*-* Boroughbridge. - PGP keyID: 0xC0B341A3 *-*-*-*-*-*-*-*-*-*-* - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
for reports only -Original Message- From: Gelu Gogancea [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 2:14 PM To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) ...in this case...is very "sad". You use this query to generate report or it's for usually work ? _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Gelu Gogancea" <[EMAIL PROTECTED]>; "Elizabeth Bogner" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 9:58 PM Subject: RE: MySQL vs. Oracle (not speed) > > > I don't have a choice as to what database I use I WISH I did > > and I didnt design the tables, I inherited them > BUT I can do SOME redesign, but only by creating new tables. > I have to leave all the old tables as is , for the old stuff. > > > see my hands are tied behind my back. > > > -Original Message- > From: Gelu Gogancea [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 1:48 PM > To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Hi Mary, > > My opinion: > MySQL forums are open to every one to said about him MySQL > problems,experience... etc.From performance point of view(slow query), 90 % > from the people which was posted on this forums , has problem with Database > and Table design.Comparing RDBMS it's not quite fairly because every of them > have advantage and disadvantage.But you must BE ABLE TO CHOICE DEPENDS ON > WHAT YOU NEED(or customer). > Like project manager when you begin to develop a database software must > know: > -ALL ABOUT RDBMS WHICH WISH TO USE(Apropos...MySQL have a too complex set of > SQL commands). > -How big database will be in the future? > -From time to time what Upgrade must to do on the Hardware? > -If is a multi level application, in every level you must know what > programming language you must used (style or technic of programming) > ...and WHAT IS MOST IMPORTANT : > > HOW YOU IMPLEMENTED (...AND WHAT TEST YOU MUST MAKE TO) THE ENTIRE > SOFTWARE TO WORK WITH NO PROBLEM ? > > Why i mentioned about this thinks(and are not too many) ?...because the > table "admincoverage" it seem is not pass by NORMALIZATION RULE STEP 1. > > You have this fields : > > AccidentElim smallint(6) , > AccidentElimUnitID varchar(5) , > SicknessElim smallint(6) , > SicknessElimUnitID varchar(5) , > AccidentBenefit double , > AccidentBenefitUnitID varchar(5) , > SicknessBenefit double , > SicknessBenefitUnitID varchar(5) , > > in this example i iterate only 2 entity of data from one table.But i > think you have more than that.Usually in this situation you must create one > table for one entity of data and make relations between entity(tables) using > FOREIGN KEYS.MySQL support (and work very well) and SCHEMA OF INDEXES. > > What convince me is the next sequence of query : > > "INNER JOIN AdminCoverage ON > AdminHierarchy.CoverageID=AdminCoverage.CoverageID AND > AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc" & _ > > I suppose you don't need by a double confirmation. > > I'm sorry if something from what i said...was not what you expected.My > intention is to help...and finally it's only my opinion. > > Best Regards and Good luck (you need), > > Gelu > > > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access to MySQL
SELECT * INTO mysql_table IN "" [ODBC;DSN=mysql_dsn;SERVER=localhost;UID=root;PWD=] FROM access_table You need myODBC and execute this in Access. Respect the double empty quotes. - Original Message - From: "Oladejo, Tokunboh" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 1:48 PM Subject: Access to MySQL > Hi All, > > Does anyone knows of any easy way/short cut of exporting Microsoft access > tables into MySql database. > > Thanx > All > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
...in this case...is very "sad". You use this query to generate report or it's for usually work ? _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Gelu Gogancea" <[EMAIL PROTECTED]>; "Elizabeth Bogner" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 9:58 PM Subject: RE: MySQL vs. Oracle (not speed) > > > I don't have a choice as to what database I use I WISH I did > > and I didnt design the tables, I inherited them > BUT I can do SOME redesign, but only by creating new tables. > I have to leave all the old tables as is , for the old stuff. > > > see my hands are tied behind my back. > > > -Original Message- > From: Gelu Gogancea [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 1:48 PM > To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Hi Mary, > > My opinion: > MySQL forums are open to every one to said about him MySQL > problems,experience... etc.From performance point of view(slow query), 90 % > from the people which was posted on this forums , has problem with Database > and Table design.Comparing RDBMS it's not quite fairly because every of them > have advantage and disadvantage.But you must BE ABLE TO CHOICE DEPENDS ON > WHAT YOU NEED(or customer). > Like project manager when you begin to develop a database software must > know: > -ALL ABOUT RDBMS WHICH WISH TO USE(Apropos...MySQL have a too complex set of > SQL commands). > -How big database will be in the future? > -From time to time what Upgrade must to do on the Hardware? > -If is a multi level application, in every level you must know what > programming language you must used (style or technic of programming) > ...and WHAT IS MOST IMPORTANT : > > HOW YOU IMPLEMENTED (...AND WHAT TEST YOU MUST MAKE TO) THE ENTIRE > SOFTWARE TO WORK WITH NO PROBLEM ? > > Why i mentioned about this thinks(and are not too many) ?...because the > table "admincoverage" it seem is not pass by NORMALIZATION RULE STEP 1. > > You have this fields : > > AccidentElim smallint(6) , > AccidentElimUnitID varchar(5) , > SicknessElim smallint(6) , > SicknessElimUnitID varchar(5) , > AccidentBenefit double , > AccidentBenefitUnitID varchar(5) , > SicknessBenefit double , > SicknessBenefitUnitID varchar(5) , > > in this example i iterate only 2 entity of data from one table.But i > think you have more than that.Usually in this situation you must create one > table for one entity of data and make relations between entity(tables) using > FOREIGN KEYS.MySQL support (and work very well) and SCHEMA OF INDEXES. > > What convince me is the next sequence of query : > > "INNER JOIN AdminCoverage ON > AdminHierarchy.CoverageID=AdminCoverage.CoverageID AND > AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc" & _ > > I suppose you don't need by a double confirmation. > > I'm sorry if something from what i said...was not what you expected.My > intention is to help...and finally it's only my opinion. > > Best Regards and Good luck (you need), > > Gelu > > > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strange network error using mysql,mysql c api
Hi, Usually Network Adapter doesn't make this things(Hardware and Software CRC).I think you should check what happened with the "query" string.When you "hang" the error make a dump of memory and see where is "broke" your array of char. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Krata" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 8:19 PM Subject: Re: Strange network error using mysql,mysql c api > On Fri, 16 Aug 2002, Victoria Reznichenko wrote: > > > K> do someone know what should be the problem? > > K> Server is Win2K and has latest mysql server installed client is the one > > K> from RedHat 7.3 (3.23.49) (=`mysql_config --version`) > > > > > > Do you always get this error or you got it just once? > well i get this error sometimes. (if i try to insert about 50.000 rows (in > loop) then it occurs in one or two cases) > > > Does error occur if you execute query via mysql command line client > > tool? > well i am not crazy :) i am not keen of inserting thousands rows in > mysqlcommand line. > > (My last hope is that there is wrong Eth card. > Curios thing is that when errors occurs then T->P differs binary in one > bit.) > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
I don't have a choice as to what database I use I WISH I did and I didnt design the tables, I inherited them BUT I can do SOME redesign, but only by creating new tables. I have to leave all the old tables as is , for the old stuff. see my hands are tied behind my back. -Original Message- From: Gelu Gogancea [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 1:48 PM To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Hi Mary, My opinion: MySQL forums are open to every one to said about him MySQL problems,experience... etc.From performance point of view(slow query), 90 % from the people which was posted on this forums , has problem with Database and Table design.Comparing RDBMS it's not quite fairly because every of them have advantage and disadvantage.But you must BE ABLE TO CHOICE DEPENDS ON WHAT YOU NEED(or customer). Like project manager when you begin to develop a database software must know: -ALL ABOUT RDBMS WHICH WISH TO USE(Apropos...MySQL have a too complex set of SQL commands). -How big database will be in the future? -From time to time what Upgrade must to do on the Hardware? -If is a multi level application, in every level you must know what programming language you must used (style or technic of programming) ...and WHAT IS MOST IMPORTANT : HOW YOU IMPLEMENTED (...AND WHAT TEST YOU MUST MAKE TO) THE ENTIRE SOFTWARE TO WORK WITH NO PROBLEM ? Why i mentioned about this thinks(and are not too many) ?...because the table "admincoverage" it seem is not pass by NORMALIZATION RULE STEP 1. You have this fields : AccidentElim smallint(6) , AccidentElimUnitID varchar(5) , SicknessElim smallint(6) , SicknessElimUnitID varchar(5) , AccidentBenefit double , AccidentBenefitUnitID varchar(5) , SicknessBenefit double , SicknessBenefitUnitID varchar(5) , in this example i iterate only 2 entity of data from one table.But i think you have more than that.Usually in this situation you must create one table for one entity of data and make relations between entity(tables) using FOREIGN KEYS.MySQL support (and work very well) and SCHEMA OF INDEXES. What convince me is the next sequence of query : "INNER JOIN AdminCoverage ON AdminHierarchy.CoverageID=AdminCoverage.CoverageID AND AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc" & _ I suppose you don't need by a double confirmation. I'm sorry if something from what i said...was not what you expected.My intention is to help...and finally it's only my opinion. Best Regards and Good luck (you need), Gelu G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query slowing down, need advice
On Friday 16 August 2002 12:43 pm, Brian Moon wrote: > No, it should be and is scanning through the 500,000+ rows that meet the > key of (approved, datestamp). The table has 1M+ in it. My question is, how > can it be faster? Can it not be faster? Is MySQL on my size server just > not going to handle scanning 500K rows? That seems like a low number to > have problems with. The query is not just slow, it does not return. There's no RDBMS on earth that would be fast for this query. If the string 'myphorum' is a constant expression which is always getting searched for then I would simply flag every record that it occurs in. I would generally try to redesign my application so this sort of query is not used. Or else maybe there are other fields you can index on as well to cut down the numbers of candidate records. I mean consider this. I don't know your schema, but if you have record lengths of say 256 characters then 1 million records is going to be 256 megabytes. I suspect your data is more like a gig. Given that most disk subsystems in realistic use get about 5 meg/sec of sustained transfer you're talking about a 1 minute READ time for the entire table (and your index is likely not helping a bit, in fact I'm surprised MySQL bothers with it, at the hit rate of 50% practically every page of the database has to be loaded anyhow, so the index is practically worthless, in fact it may even be slowing your query, try dropping it). My other advice would be to get lots of RAM and increase the size of the database's buffers. If you can get the entire table buffered into RAM you might get some decent performance. > > Brian. > > - Original Message - > From: "Thomas Spahni" <[EMAIL PROTECTED]> > To: "Brian Moon" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 10:51 AM > Subject: Re: Query slowing down, need advice > > | On Fri, 16 Aug 2002, Brian Moon wrote: > | > I ran into a problem when the queries started to call for 500,000+ > | > rows. The query would not return. Here is the query and explain: > | > > | > select SQL_NO_CACHE id from single_table where approved='Y' AND > > datestamp >= > > | > '2001-08-16 00:00:00' AND ( ( author LIKE '%myphorum%' OR subject > > LIKE > > | > '%myphorum%' OR body LIKE '%myphorum%' ) ) order by datestamp desc; > | > | Brian, > | > | this query, having a joker at the beginning of each search string, scans > | through the whole table. It can't possibly be fast on large tables. > | > | Regards, > | Thomas > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> Trouble > unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
Mary, you could try downloading MySQL-Max-3.23.52 for Windows and converting your tables to the InnoDB format. Your tables maybe take some 500 MB of space in InnoDB. If you have a reasonably big Windows server with > 500 MB RAM, they will fit completely in the buffer pool if you configure it big enough. Windows has inefficient file cache management. When you get the data into the InnoDB buffer pool, you bypass that problem. Below you can check appropriate settings for your my.cnf or my.ini. http://www.innodb.com/ibman.html#InnoDB_start [mysqld] # You can write your other MySQL server options here # ... #Data file(s) must be able to #hold your data and indexes. #Make sure you have enough #free disk space. innodb_data_file_path = ibdata1:10M:autoextend #Set buffer pool size to #50 - 80 % of your computer's #memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M #Set the log file size to about #25 % of the buffer pool size set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M #Set ..flush_log_at_trx_commit #to 0 if you can afford losing #some last transactions innodb_flush_log_at_trx_commit=0 Regards, Heikki Innobase Oy .. it was a limitation of the DBTOOLS table manager... I am indexing right now using alter table... -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 1:08 PM To: Mary Stickney Cc: Jocelyn Fournier; Mark Matthews; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) In the last episode (Aug 16), Mary Stickney said: > looks to me like I can only make indexes on fields that are not > null... this one is not , not null Indexing of NULL columns went into Mysql 3.23.2 (Dec 16, 2000). With tables this large, you might also want to raise some of the cache parameters in my.cnf . http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
Regarding your testing... did you test many concurrent processes or concurrent multi-user access? If you need concurrent multi-user access for an OLTP app then you should test it. To this end I developed a Perl routine which launched concurrent sessions in the background. I could adjust the number of concurrent sessions via arguments passed to the program. Each session performed a series of SQL statements involving a real world mix of selects, inserts, updates and deletes with pseudo random values provided for key column ranges. As soon as I cranked up the volume to around 20 concurrent sessions MySQL barfed but Oracle scaled much higher without a hitch. MySQL is great for raw speed with individual queries or batch inserts but it doesn't seem to scale as well with OLTP apps having many concurrent sessions. I'm hoping this will change with InnoDB and future enhancements as MySQLAB strives for ANSI compatibility like the other guys (PostgreSQL and Interbase). -Original Message- From: Kenneth Hylton [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 10:47 AM To: [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) Our experience has been totally the opposite. We recently ported a Delphi application from MS-SQL to MySQL. The MySQL server was a less powerful box than MS-SQL was running on. I will not go into great detail because some of the software is proprietary and we make $$$ providing the service. But here are out times: Process #1 (heavy inserts, few updates) MS-SQL 9 hours MySQL 90 minutes Process #2 (heavy updates, few inserts) MS-SQL 17 hours MySQL 2.5 hours The databases were tuned to get maximum performance from MS-SQL (you can see why) and NO changes were made to optimize for MySQL, as we didn't need to. Ken Hylton Programmer Analyst IV LEC Systems & Programming Billing Concepts, Inc. 7411 John Smith Drive San Antonio, Texas 78229-4898 (210) 949-7261 -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 8:53 AM To: Francisco; Elizabeth Bogner; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) I have been doing speed tests the same query ran on MYSQL took 45 minutes on MS-SQL it took 11 minutes.. yes you do get what you pay for -Original Message- From: Francisco [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 8:47 AM To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) Hi, I am beging using MySQL for quite a while and it is a very good choice if you don't really need stored procedures. MySQL provides a pretty good implementation of a subset of MySQL-92, performance is great, it is cross-platform, provides transactions, and its price... well is free. Hope it helps. --- Mary Stickney <[EMAIL PROTECTED]> wrote: > > It doesn't suport alot of differnt things > it dosent have store procedures , dosent have a > complete SQL command set... > > I am using it becasue I am being forced to... > > > > -Original Message- > From: Elizabeth Bogner > [mailto:[EMAIL PROTECTED]] > Sent: Thursday, August 15, 2002 9:25 PM > To: [EMAIL PROTECTED] > Subject: MySQL vs. Oracle (not speed) > > > > A company I work with is in the process of upgrading > its databases from > some > motheaten system to something current. My impression > is that they > want to go with Oracle, and I'm not sure if this is > based on anything > other than being impressed with the size and > presumed quality support > of Oracle. I'd like to encourage them to at least > seriously consider > using > MySQL instead. > > I don't think that speed is a huge factor here; we > do a lot of XML > publishing > and content management, but at most we'd have > several gigabytes of > data and several dozen simultaneous users, so well > within the > capabilities > of MySQL. I've looked at various things I could > find, like the benchmarks > pages (probably not relevant) and the MySQL myths > page, which was > somewhat helpful, but I couldn't find anything more > along the lines of > "How to Convince my Management to go with MySQL." I > don't even know > what to expect from them, but I'm imagining they'll > say, "But MySQL > doesn't support sub-selects," to which I can reply, > "But you can write > most of those as joins anyway, so it won't matter > because the software > will all be written from scratch." Etc. > > Are there pointers anyone can give me? > > E. Bognewitz > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > > > - > Before posting, please check: >http
Re: MySQL vs. Oracle (not speed)
Hi Mary, My opinion: MySQL forums are open to every one to said about him MySQL problems,experience... etc.From performance point of view(slow query), 90 % from the people which was posted on this forums , has problem with Database and Table design.Comparing RDBMS it's not quite fairly because every of them have advantage and disadvantage.But you must BE ABLE TO CHOICE DEPENDS ON WHAT YOU NEED(or customer). Like project manager when you begin to develop a database software must know: -ALL ABOUT RDBMS WHICH WISH TO USE(Apropos...MySQL have a too complex set of SQL commands). -How big database will be in the future? -From time to time what Upgrade must to do on the Hardware? -If is a multi level application, in every level you must know what programming language you must used (style or technic of programming) ...and WHAT IS MOST IMPORTANT : HOW YOU IMPLEMENTED (...AND WHAT TEST YOU MUST MAKE TO) THE ENTIRE SOFTWARE TO WORK WITH NO PROBLEM ? Why i mentioned about this thinks(and are not too many) ?...because the table "admincoverage" it seem is not pass by NORMALIZATION RULE STEP 1. You have this fields : AccidentElim smallint(6) , AccidentElimUnitID varchar(5) , SicknessElim smallint(6) , SicknessElimUnitID varchar(5) , AccidentBenefit double , AccidentBenefitUnitID varchar(5) , SicknessBenefit double , SicknessBenefitUnitID varchar(5) , in this example i iterate only 2 entity of data from one table.But i think you have more than that.Usually in this situation you must create one table for one entity of data and make relations between entity(tables) using FOREIGN KEYS.MySQL support (and work very well) and SCHEMA OF INDEXES. What convince me is the next sequence of query : "INNER JOIN AdminCoverage ON AdminHierarchy.CoverageID=AdminCoverage.CoverageID AND AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc" & _ I suppose you don't need by a double confirmation. I'm sorry if something from what i said...was not what you expected.My intention is to help...and finally it's only my opinion. Best Regards and Good luck (you need), Gelu G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Persisten Connections
> There is at least one reason, which is that persistent connections cause > the server to hold open connection slots even the connection isn't actively > being used at the moment. But isn't this really just a failure of the connection pooling mechanism? If you have a connection pooling program on a separate server in a 3 tier architecture then it should be configurable to tune timeouts, connection reuse or disconnects. No need to blame the database if the connection multiplexer isn't doing a good job. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 14, 2002 11:26 AM To: Tod Harter; Thomas Seifert; [EMAIL PROTECTED] Subject: Re: Persisten Connections At 11:14 -0400 8/14/02, Tod Harter wrote: >On Wednesday 14 August 2002 09:54 am, Thomas Seifert wrote: > >I disagree entirely > >Persistent connections have little or nothing to do with increasing load! > >Given that you mention you are using PHP I'll assume you have mod_php running >in Apache. Each Apache child process in this configuration will maintain ONE >open database handle, so 100k pageviews per day I would expect you might max >at like 30k in one hour, or around 10/second, so you might top out at roughly >100 Apache child processes at any one time, thus 100 database connections. > >Each DB connection is not a huge overhead, but creating and destroying 10 >database handles PER SECOND is a large overhead!!! Remember, every time mysql >creates a connection it has to do internal queries on the grant tables. I >don't know exactly what the overhead of that is going to be, but ANYTHING >that creates 10 queries per second is putting some strain on your database >server! > >One of the main goals of using Apache modules for scripting was to allow >persistent database connections. There is really NO reason to give up that >advantage. Remember, MySQL is multi-threaded, with one thread per connection, >so the resources for a database connection are on the order of under 100k of >memory per connection. There is at least one reason, which is that persistent connections cause the server to hold open connection slots even the connection isn't actively being used at the moment. This can cause the MySQL server to run out of connection slots and result in connections being refused. Using non-persistent connections under such circumstances can be beneficial because on average, the server need hold fewer connections open. This has been found to solve connection-refused issues in a number of cases on this list. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
it was a limitation of the DBTOOLS table manager... I am indexing right now using alter table... -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 1:08 PM To: Mary Stickney Cc: Jocelyn Fournier; Mark Matthews; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) In the last episode (Aug 16), Mary Stickney said: > looks to me like I can only make indexes on fields that are not > null... this one is not , not null Indexing of NULL columns went into Mysql 3.23.2 (Dec 16, 2000). With tables this large, you might also want to raise some of the cache parameters in my.cnf . http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
no I am using myisam it was just a limitation of DBTOOLS I am getting it done using alter table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Van Sent: Friday, August 16, 2002 1:06 PM To: Mary Stickney Cc: [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Mary: You can index fields containing NULL values in MyISAM tables, but not ISAM tables. If you're using ISAM tables, this might explain your performance issues. There are also other more featured table types, but MyISAM is probably going to yield the most performance in your application. Van -- = Linux rocks!!! http://www.dedserius.com/ = Mary Stickney wrote: > > looks to me like I can only make indexes on fields that are not null... > this one is not , not null > > -Original Message- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 11:34 AM > To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > Hi, > > I see you do an order by on AdminHierarchy.WritingAgentSlot but this column > is not indexed. > Please tell me if an index on this column improve the speed of the query a > bit. > > Regards, > Jocelyn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Still trying to build libmysqld.a on Tru64
In the last episode (Aug 16), Randall Severy said: > > It is only libmysqld that you're having problems with? I have > > built mysql (the regular separate client and server) on three Tru64 > > systems with no problems. What version of Tru64? > > Yes, it's only libmysqld that I can't get to work. I've been > using the regular separate client and server on Tru64 successfully > for years (although for 3.x only, not 4.0). The version of Tru64 I > am currently trying to build on is 5.1. Make sure you've got the latest patchkit installed. The latest for 5.1 was released on May 8, and is available at http://ftp.support.compaq.com/patches/public/Readmes/unix/t64v51b19as0005-20020411.README I don't think any of the Pks are really required (Tru64 is pretty stable), but you never know. > >As for your stack trace, try using the native ladebug debugger instead > >of gdb. Gdb (even 5.2.1) has real problems with threads on Tru64. > > ladebug does show more detail, but the end result is the same: > #6 0x3ff800d5170 in __sigtramp(0x3ffc01842c8, 0xb, 0x1, 0x1, 0x14, 0x3ffc018400 0) >in /usr/shlib/libc.so > #7 0x3ff800d29ac in _OtsMove(0x1a1dd44ca, 0x1a1dd44c8, 0x1a1dc0481, 0x0, >0x61540478, 0x0) in /usr/shlib/libc.so > #8 0x120157978 in UnknownProcedure8FromFile25(0x1a1dd44ca, 0x1a1dd44c8, >0x1a1dc0481, 0x0, 0x61540478, 0x0) in wsd > #9 0x12015972c in mysql_read_query_result(0x1a1dd44ca, 0x1a1dd44c8, 0x1a1dc0481, >0x0, 0x61540478, 0x0) in wsd > > So why would mysql_read_query_result end up in > UnknownProcedure8FromFile25? I assume wsd is your binary? Not sure why it couldn't get the function name. Try building mysqld again with --with-debug, and build your application with debugging (-g) as well. That should make the stack dump even more readable. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Persisten Connections
> I profiled my PHP app with a debugger A single session and a few queries does not make a very good test. The reason for persistent connections is to scale web apps to handle intense multi-user activity like 1000 queries per second or 100's or even 1000's of concurrent database connections. Do a for..loop that launches 1000 *nix background processes each of which has a suite of queries with slightly different values in the where clause. You need to look at overall performance of the database server and its ability to handle the load of many concurrent users using different connection and/or programming techniques. The best implementation I've seen is where the connection pooling function was written in C in a 3 tier architecture having a web server, a db server, and an application server where the connection pooling was done... VERY scalable, just like the fish I like to catch. -Original Message- From: mos [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 10:36 AM To: John Wards Cc: [EMAIL PROTECTED] Subject: Re: Persisten Connections At 08:40 AM 8/14/2002, you wrote: >I am running a website which generates around 100,000 pageviews a day and I >am wondering if I stop using persistent conections to the MySQL database and >use >normal open and close conections this would reduce the load onto my server? > >Most conections are either made through my "main" file or the phorum message >board system. > >Thanks in advance >John Wards John, I profiled my PHP app with a debugger and discovered connecting to MySQL 4.x with persistent connections actually took 50% longer than non-persistent connections. (It added an extra 80ms to connect). So I don't see why persistent connections is useful at all with MySQL. Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication Problem
Hello, I have this replication problem. I am getting this error on the slave: 020816 16:30:49 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020816 16:30:49 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 4 020816 16:30:49 Error reading packet from server: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) (server_errno=1045) But I have verified through the command line that this user has the ability to contact the master, use the database, and that the password in /etc/my.cnf if correct. Any other reason that this is failing? -James sql query James Kelty Director of Operations Everbase Systems, LLC 624 A Street Ashland, OR 97520 [EMAIL PROTECTED] 541.488.0801 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
In the last episode (Aug 16), Mary Stickney said: > looks to me like I can only make indexes on fields that are not > null... this one is not , not null Indexing of NULL columns went into Mysql 3.23.2 (Dec 16, 2000). With tables this large, you might also want to raise some of the cache parameters in my.cnf . http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
Mary: You can index fields containing NULL values in MyISAM tables, but not ISAM tables. If you're using ISAM tables, this might explain your performance issues. There are also other more featured table types, but MyISAM is probably going to yield the most performance in your application. Van -- = Linux rocks!!! http://www.dedserius.com/ = Mary Stickney wrote: > > looks to me like I can only make indexes on fields that are not null... > this one is not , not null > > -Original Message- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 11:34 AM > To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > Hi, > > I see you do an order by on AdminHierarchy.WritingAgentSlot but this column > is not indexed. > Please tell me if an index on this column improve the speed of the query a > bit. > > Regards, > Jocelyn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Access to MySQL
Hi All, Does anyone knows of any easy way/short cut of exporting Microsoft access tables into MySql database. Thanx All - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
I also noticed the field where you make the join are not exactly of the same type : e.g : AdminHierarchy.CoverageID=AdminCoverage.CoverageID in adminHierarchy : CoverageID varchar(15) NOT NULL in admincoverage : CoverageID varchar(10) NOT NULL AdminCoverage.ProductID=AdminProduct.ProductID in adminCoverage : ProductId varchar(11) NOT NULL , in adminProduct : ProductID varchar(15) NOT NULL , AdminProducer.taxid = tempsap.taxid in adminProducer : TaxID varchar(9) NOT NULL , in tempsap : TaxID varchar(10) NOT NULL , If you want to maximize the performance you should use the same field type. - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Jocelyn Fournier" <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 7:51 PM Subject: RE: MySQL vs. Oracle (not speed) > > I hope this makes it faster , the program just got done , it ran from 7:00 > am yesterday until noon today. > and guess what , one of my report columns is wrong , I made a boo boo in the > persistency calculation > > at least it friday... > > > -Original Message- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 12:25 PM > To: Mary Stickney > Subject: Re: MySQL vs. Oracle (not speed) > > > Ok, so it's already a MyISAM table. You should be able to add index on > WritingAgentSlot with > > ALTER TABLE adminhierarchy ADD INDEX (WritingAgentSlot); > > - Original Message - > From: "Mary Stickney" <[EMAIL PROTECTED]> > To: "Jocelyn Fournier" <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 7:22 PM > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > ;Table;Create Table > > ;Table;Create Table > > ;AdminHierarchy;CREATE TABLE `adminhierarchy` ( > > `Source` char(2) NOT NULL default '', > > `WritingAgentID` varchar(15) NOT NULL default '', > > `CoverageID` varchar(15) NOT NULL default '', > > `AgentLevelID` varchar(15) NOT NULL default '', > > `ProducerID` varchar(15) NOT NULL default '', > > `ProducerTypeID` varchar(5) NOT NULL default '', > > `PercentOfCase` double default NULL, > > `RegionCode` varchar(5) default NULL, > > `CoverageIdSbc` varchar(10) NOT NULL default '', > > `WritingAgentSlot` int(11) default NULL, > > `RadDistributionCode` varchar(5) default NULL, > > KEY `WritingAgentIDIndex` (`WritingAgentID`), > > KEY `AgentLevelIDIndex` (`AgentLevelID`), > > KEY `CoverageIDIndex` (`CoverageID`), > > KEY `ProducerIDIndex` (`ProducerID`), > > KEY `ProducerTypeIDIndex` (`ProducerTypeID`), > > KEY `CoverageIdSbcIndex` (`CoverageIdSbc`), > > KEY `CovIdCovIdSbcIndex` (`CoverageID`,`CoverageIdSbc`) > > ) TYPE=MyISAM > > > > > > > > -Original Message- > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 12:16 PM > > To: Mary Stickney > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > SHOW CREATE TABLE AdminHierarchy; > > > > It must show what kind of table you are using. > > > > I don't think it change anything for the other tools. Just try to create a > > table in MyISAM format and see if it's correctly handled by Segate > Analysis, > > etc. > > If not, just go back to ISAM (ALTER TABLE AdminHierarchy type=ISAM, and so > > on). > > > > > > - Original Message - > > From: "Mary Stickney" <[EMAIL PROTECTED]> > > To: "Jocelyn Fournier" <[EMAIL PROTECTED]> > > Sent: Friday, August 16, 2002 7:14 PM > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > > > > ok I am on a quest to find out what type we are using. and if changeing > > them > > > will affect > > > other things. like the Segate Analysis that the Clients use for adhoc > > > reporting > > > > > > -Original Message- > > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > > Sent: Friday, August 16, 2002 12:07 PM > > > To: Mary Stickney > > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > > > > Ok, so you have to convert them to MyISAM (you should have a great > > > improvement in speed then). > > > > > > Just use > > > > > > ALTER TABLE AdminHierarchy type=MyISAM; > > > ALTER TABLE AdminCoverage type=MyISAM; > > > ALTER TABLE AdminProducer type=MyISAM; > > > ALTER TABLE AdminProduct type=MyISAM; > > > ALTER TABLE tempsap type=MyISAM; > > > > > > Then you should be able to add the index on the WritingAgentSlot column. > > > > > > - Original Message - > > > From: "Mary Stickney" <[EMAIL PROTECTED]> > > > To: "Jocelyn Fournier" <[EMAIL PROTECTED]> > > > Sent: Friday, August 16, 2002 7:03 PM > > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > > > > > > > > no I am not.. I just started using this system 3 weeks ago. > > > > > > > > > > > > -Original Message- > > > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > > > Sent: Friday, August 16, 2002 12:00 PM > > > > To: Mary Stickney > > > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > > > > > > > Are you sure your tables used the MyISAM handler, and not the ISAM > > handler > > > ? > > > > - O
Re: improving slow query (was: MySQL vs. Oracle (not speed))
Hello. On Fri 2002-08-16 at 10:49:00 -0500, [EMAIL PROTECTED] wrote: > > yes I do But they are not listed in the "Possible Keys" column, which they should. MySQL cannot use them for some reason. I had a look at the posting with the CREATE TABLEs and it seems the reason is that the columns are not declared identically in the two tables (taxid allows NULL in one and it NOT NULL in the other). Please go through all indexed fields and assure they are the same as their corresponding fields in all tables. Then post again the results of DESC and EXPLAIN (and also the SELECT statement itself, for reference). For EXPLAIN, please make use \G in the command line client, which will format the output column-wise instead of row-wise, which is better readable. Thanks. Regards, Benjamin. PS: Subject changed to reflect new topic [...] > Do you have an index on AdminProducer.taxid and on tempsap.taxid ? [...] > > explian returns this > > > > ;table;type;possible_keys;key;key_len;ref;rows;Extra > > ;table;type;possible_keys;key;key_len;ref;rows;Extra > > > > ;AdminCoverage;range;CoverageID,CoverageIdSbc,InitPremDateIndex,CovIdCovIdSb > > cIndex,CovIdCovIdSbcInitPremIndex;InitPremDateIndex;3;;315663;where used; > > Using temporary; Using filesort > > > > ;AdminHierarchy;ref;CoverageIDIndex,CoverageIdSbcIndex,CovIdCovIdSbcIndex;Co > > > > vIdCovIdSbcIndex;25;AdminCoverage.CoverageID,AdminCoverage.CoverageIdSbc;7;w > > here used > > > > ;AdminProducer;ref;ProducerIDIndex;ProducerIDIndex;15;AdminHierarchy.Writing > > AgentID;11; > > > > ;AdminProduct;ref;ProductIdIndex;ProductIdIndex;15;AdminCoverage.ProductId;1 > > 1; > > ;tempsap;ALL;10019; > > > [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
running mysqld
Hello! I have a problem when I trying to start mysqld (as a root, under RH 7.2). Allegedly I don't have a rights to run this program... What I have to do to run mysqld without rebooting system? Best regards: Krzychu --- Super promocyjna oferta dla mieszkancow Krakowa http://www.promocja.kki.pl/radio.html --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Count and group by problems
Me again I found a work around to my problem: select country, count(distinct client_name, client_address, etc..) clients from addresses group by country But I still don't understand why the below would not work. I downloaded the 3.# version of the OBDC but that didn't seem to help. Help. Thanks Jake -Original Message- From: Menard, Inc. Information Systems [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 14, 2002 5:26 PM To: David Kramer; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Count and group by problems For an example let's say that I have clients in multiple countries and I want a list of the number of clients per country. In order to do this I would think a statment like: select country, count(*) clients from addresses group by country would work just fine. I have have been working in VB on NT with odbc client 2.50.39.00. I have tried all of the cursor combinations that I could think of, but I still at a loss. Could there be a problem with the version of the client??? Thanks again Jake -Original Message- From: David Kramer [mailto:[EMAIL PROTECTED]] Sent: Friday, August 09, 2002 5:59 PM To: 'Menard, Inc. Information Systems'; [EMAIL PROTECTED] Subject: RE: Count and group by problems Im not sure I understand what it is that you are trying to do here??? Im assuming your using PERL/SomeScriptingLang on something on NT to connect to mysql using the ADO object... Within your Count(*) Group by are you trying to return a result set that contains a duplicate row count, i.e. Column=Booger, Count(*)=2??? If you provide a little more info I might be able to help here, if what I said above is true then maybe do something like this select foo, count(*) CNT from Table group by foo having CNT > 1; Not sure on the syntax though, but something along those lines... DK -Original Message- From: Menard, Inc. Information Systems [mailto:[EMAIL PROTECTED]] Sent: Friday, August 09, 2002 3:10 PM To: [EMAIL PROTECTED] Subject: Count and group by problems Evening I think I'm losing my mind. I have a very simple query that works fine in MySQLFront but will not work when I connect using ADO: select booger, count(*) from nose group by booger If I try the above query with one record It works fine. Once I try it with more records, nothing returns. If I try: select booger from nose group by booger It returns results with one or more records. Thanks for all of your help! Jake - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strange network error using mysql,mysql c api
On Fri, 16 Aug 2002, Victoria Reznichenko wrote: > K> do someone know what should be the problem? > K> Server is Win2K and has latest mysql server installed client is the one > K> from RedHat 7.3 (3.23.49) (=`mysql_config --version`) > > > Do you always get this error or you got it just once? well i get this error sometimes. (if i try to insert about 50.000 rows (in loop) then it occurs in one or two cases) > Does error occur if you execute query via mysql command line client > tool? well i am not crazy :) i am not keen of inserting thousands rows in mysqlcommand line. (My last hope is that there is wrong Eth card. Curios thing is that when errors occurs then T->P differs binary in one bit.) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 3.23.52 is released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 3.23.52, the world's most popular Open Source Database, has been released and is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. This is a bugfix release for the current stable tree. - From the changelog: o Fixed security bug when having an empty databasename in the user.db o Changed initialisation of RND() to make it less predictable. o Fixed problem with GROUP BY on result with expression that created a BLOB field. o Fixed problem with privilege tables when downgrading from 4.0.2 to 3.23.xx o Fixed thread bug in SLAVE START, SLAVE STOP and automatic repair of MyISAM tables that could cause table cache to be corrupted. o Fixed possible thread related key-cache-corruption problem with OPTIMIZE TABLE and REPAIR TABLE. o Added name of 'administrator command' logs. o Fixed bug with creating an auto-increment value on second part of a UNIQUE() key where first part could contain NULL values. o Don't write slave-timeout reconnects to the error log. o Fixed bug with slave net read timeouting. o Fixed a core-dump bug with MERGE tables and MAX() function. o Fixed bug in ALTER TABLE with BDB tables. o Fixed bug when logging LOAD DATA INFILE to binary log with no active database. o Fixed a bug in range optimiser (causing crashes). o Fixed possible problem in replication when doing DROP DATABASE on a database with InnoDB tables. o Fixed that mysql_info() returns 0 for 'Duplicates' when using INSERT DELAYED IGNORE. o Added -DHAVE_BROKEN_REALPATH to the Mac OS X (darwin) compile options in configure.in to fix a failure under high load. Some additional notes: Replication on Mac OS X can give you some errors when running the test suite - we are currently looking into these. However, we have already received many reports from users who are using replication on this platform without any problems. AIX and SCO binaries are currently missing due to some changes to the systems that we are using for our builds. We hope to be able to provide binaries for these platforms again with the next release. Have a nice weekend, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Lenz Grimmer <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany <___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE9XTDvSVDhKrJykfIRAs6/AJ9GV4xw+e9vOf/sfYnrgDMTEh1mDgCfdEmg vuNFtSX8Tj/mKwvxpXACaJs= =TDy+ -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
Mary Stickney wrote: > thanks Serge.. > > incidentally my very first post to the list contained my query , and is the > reason I joined this list in the first place. > This 3 year sales report has been running for over 24 hours now. this is > unacceptable, since they also wanbt a 5 year > one and I am sure that will take 5 years to run... at this rate. > > > -Original Message- > From: Serge Paquin [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 11:01 AM > To: Mark Matthews > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > I think this is a touch on the negative side. I'm sure many people get > soured on MySQL when they to a post here and get yelled at for not trying > hard enough. It sounds like Mary is having a valid problem. Her query > works fast in one database and slow in the other. Because she did not come > out and put her table schema and sql in the first email is no reason to > insult her. I think a helping hand is a better approach. Mabey a couple > general suggestions on MySQL tuning and a "We would need to see the schema > and SQL statement to help further". I agree that it seems very strange that > MS-SQL runs fater than MySQL on a query. Microsoft seems to always be > plaiged with performance problems. Insulting someone will not help convert > them to MySQL it will drive people away. > > Anyway enough of my rant... > > Serge. > > On Fri, 16 Aug 2002 09:46:44 -0500 > "Mark Matthews" <[EMAIL PROTECTED]> wrote: > > >>Mary Stickney wrote: >> >>>What exactly is Trolling >>> >>> >>>I find MYSQL to be slow , sorry if that doesn't met with your approval. >> >>But you don't give examples. >> >>I've found MS-SQL to be slow at times, especially when used from JDBC, >>but I don't make crack comments about it newsgroups without backing up >>my claims. >> >>Why don't you show us your queries? Do you really care to learn if MySQL >>could be faster and you're just using it wrong? >> I didn't intend it to be insulting. Maybe people are oversensitive. The problem is that it was very easy to take Mary's e-mail out of context, because there was no hint that she was having problems and was therfore upset over that...The volume in this e-mail list is tremendous, we don't see all messages. If someone's going to make a negative comment as a reply to some un-related e-mail, and then say "But I posted my problem earlier" but never refer to it in the email containing the negative comment that is un-related to their problem, then it is a bit of a leap to assume that people that respond will have read _every_ message in this list, and be able to connect the two. If anyone found me harsh, I apologize, but I didn't intend the message to come off that way. Just remember that if you expect answers here, you need to help us a little, we're not omniscient, and not all of us read every single e-mail on this listHeck, there's been 113 today and the day's not over! -Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed) - not part of the rant, but real information
I believe MySQL doesn't do a lot of the optimizations that MSSQL does. However, you can do it manually (I think) by playing around with both the order of the where clause and the order of the join clause The where clauses go in order, so you want to use the first part of the where clause to get rid of as many records as possible (ie. use the most restrictive where clause first and then go down from there). That way, you minimize the quantity of data getting through the first where clause and not the second or third, or nth. Also, MySQL has a really wimpy default configuration (I can't figure out why). Here is my /etc/my.cnf (I don't know what the equivalent is on windows) [mysqld] set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = table_cache=256 set-variable = key_buffer=256M set-variable = tmp_table_size=256M set-variable = interactive_timeout=7200 set-variable = wait_timeout=40 set-variable = max_connections=200 # innodb stuff added 05/16/02 innodb_data_file_path = ibdata:500M set-variable = innodb_buffer_pool_size=350M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_files_in_group=7 set-variable = innodb_log_file_size=50M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 set-variable = innodb_thread_concurrency=4 default-table-type=innodb -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 10:32 AM To: Francisco; Elizabeth Bogner; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) I am not for one or the other I just hate to wait I need speed... we already have a MS-SQL server , so no more money needs to me spent... I did a 4 table join , drwing 3 years of sales data table one 6.5 million records --- AdminHierarchy table two 1.5 million records AdminCoverage table three 10191 records --- AdminProcuder table four 19823 records --- AdminProduct SELECT AdminHierarchy.WritingAgentID, AdminHierarchy.WritingAgentSlot, AdminHierarchy.ProducerID, tempsap.taxid, (ModalPremium * BillModeID * (PercentOfCase / 100)) AS TotalPaidPremium, AdminCoverage.CoverageID, AdminCoverage.CoverageIDSbc, AdminHierarchy.RegionCode, AdminProduct.LobId, AdminCoverage.StatusID, AdminCoverage.StatusDate, AdminCoverage.InitialPremiumDate, AdminCoverage.PaidToDate, tempsap.GROUPID FROM AdminHierarchy INNER JOIN AdminCoverage ON AdminHierarchy.CoverageID=AdminCoverage.CoverageID AND AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc LEFT JOIN AdminProducer ON AdminProducer.ProducerID=AdminHierarchy.WritingAgentID LEFT JOIN AdminProduct ON AdminCoverage.ProductID=AdminProduct.ProductID Left join tempsap on AdminProducer.taxid = tempsap.taxid WHERE AdminCoverage.InitialPremiumDate >= '2101' AND AdminCoverage.InitialPremiumDate <= '20020430' ORDER BY AdminHierarchy.WritingAgentSlot,AdminCoverage.CoverageId, AdminCoverage.CoverageIdSbc, AdminHierarchy.ProducerID -Original Message- From: Francisco [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 9:21 AM To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) Hi Mary, I am not specially against or pro MySQL, Microsoft SQL Server, Oracle or any other database. Teams make their choices based on the project needs such as budget (is your team ready to spend thousands of dollars on Oracle and marry that corporation forever?), deployment (do you want your product with Oracle's price tag attached to it?), functionality: does the database server provide a viable solution technically speaking?, etc, etc. So it is not an issue of good or bad. But regardless of all that, what we should have, at least, is a great respect for people that has been working so hard to provide an affordable and viable alternative to the database server giants. I don't think that throwing those numbers without any other explanations about your test environments, SQL, tables, etc is a good practice. Do you think that you always get what you pay when you spend thousand of dollars in software and services without leaving you any other choices? I know that you did not put bad intentions behind your comment but should be more careful and precise. Whoever is interested on some benchmarks can go to: http://www.mysql.com/information/benchmarks.html There is an interesting article comparing Ms-SQL, Oracle, DB2 and MySQL in: http://www.pcmag.com/article2/0,4149,7279,00.asp Sincerely, Francisco --- Mary Stickney <[EMAIL PROTECTED]> wrote: > > I have been doing speed tests the same query > ran on MYSQL took 45 > minutes > on MS-SQL it took 11 minutes.. > > yes you do get what you pay for > > -Original Message- > From: Francisco [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 8:47 AM > To: Mary Stickney; Elizabeth Bogner; > [EMAIL PROTECTED] > Subject: RE: MySQL v
RE: MySQL vs. Oracle (not speed)
Our experience has been totally the opposite. We recently ported a Delphi application from MS-SQL to MySQL. The MySQL server was a less powerful box than MS-SQL was running on. I will not go into great detail because some of the software is proprietary and we make $$$ providing the service. But here are out times: Process #1 (heavy inserts, few updates) MS-SQL 9 hours MySQL 90 minutes Process #2 (heavy updates, few inserts) MS-SQL 17 hours MySQL 2.5 hours The databases were tuned to get maximum performance from MS-SQL (you can see why) and NO changes were made to optimize for MySQL, as we didn't need to. Ken Hylton Programmer Analyst IV LEC Systems & Programming Billing Concepts, Inc. 7411 John Smith Drive San Antonio, Texas 78229-4898 (210) 949-7261 -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 8:53 AM To: Francisco; Elizabeth Bogner; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) I have been doing speed tests the same query ran on MYSQL took 45 minutes on MS-SQL it took 11 minutes.. yes you do get what you pay for -Original Message- From: Francisco [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 8:47 AM To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) Hi, I am beging using MySQL for quite a while and it is a very good choice if you don't really need stored procedures. MySQL provides a pretty good implementation of a subset of MySQL-92, performance is great, it is cross-platform, provides transactions, and its price... well is free. Hope it helps. --- Mary Stickney <[EMAIL PROTECTED]> wrote: > > It doesn't suport alot of differnt things > it dosent have store procedures , dosent have a > complete SQL command set... > > I am using it becasue I am being forced to... > > > > -Original Message- > From: Elizabeth Bogner > [mailto:[EMAIL PROTECTED]] > Sent: Thursday, August 15, 2002 9:25 PM > To: [EMAIL PROTECTED] > Subject: MySQL vs. Oracle (not speed) > > > > A company I work with is in the process of upgrading > its databases from > some > motheaten system to something current. My impression > is that they > want to go with Oracle, and I'm not sure if this is > based on anything > other than being impressed with the size and > presumed quality support > of Oracle. I'd like to encourage them to at least > seriously consider > using > MySQL instead. > > I don't think that speed is a huge factor here; we > do a lot of XML > publishing > and content management, but at most we'd have > several gigabytes of > data and several dozen simultaneous users, so well > within the > capabilities > of MySQL. I've looked at various things I could > find, like the benchmarks > pages (probably not relevant) and the MySQL myths > page, which was > somewhat helpful, but I couldn't find anything more > along the lines of > "How to Convince my Management to go with MySQL." I > don't even know > what to expect from them, but I'm imagining they'll > say, "But MySQL > doesn't support sub-selects," to which I can reply, > "But you can write > most of those as joins anyway, so it won't matter > because the software > will all be written from scratch." Etc. > > Are there pointers anyone can give me? > > E. Bognewitz > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscrib
Re: Query slowing down, need advice
No, it should be and is scanning through the 500,000+ rows that meet the key of (approved, datestamp). The table has 1M+ in it. My question is, how can it be faster? Can it not be faster? Is MySQL on my size server just not going to handle scanning 500K rows? That seems like a low number to have problems with. The query is not just slow, it does not return. Brian. - Original Message - From: "Thomas Spahni" <[EMAIL PROTECTED]> To: "Brian Moon" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 10:51 AM Subject: Re: Query slowing down, need advice | On Fri, 16 Aug 2002, Brian Moon wrote: | | > I ran into a problem when the queries started to call for 500,000+ rows. | > The query would not return. Here is the query and explain: | > | > select SQL_NO_CACHE id from single_table where approved='Y' AND datestamp >= | > '2001-08-16 00:00:00' AND ( ( author LIKE '%myphorum%' OR subject LIKE | > '%myphorum%' OR body LIKE '%myphorum%' ) ) order by datestamp desc; | | Brian, | | this query, having a joker at the beginning of each search string, scans | through the whole table. It can't possibly be fast on large tables. | | Regards, | Thomas | | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
Hi! - Original Message - From: "Jocelyn Fournier" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 7:16 PM Subject: Re: MySQL vs. Oracle (not speed) > Hi, > > Does the MySQL-4.1 development tree publicly available (if so, on which port > ??) Sorry, not yet. It will be available as soon as the MySQL 4.0.3 beta is out and we have reviewed all the code changes in the 4.1 tree. > Regards, > Jocelyn Regards, Heikki > - Original Message - > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 3:42 PM > Subject: Re: MySQL vs. Oracle (not speed) > > > > Hi! > > > > > > I tested a couple of days ago the MySQL-4.1 development tree. A simple > > EXISTS subquery worked there. So there are already subqueries if you dare > to > > use the MySQL-4.1 development tree. > > > > Best regards, > > > > Heikki Tuuri > > Innobase Oy > > --- > > InnoDB - transactions, hot backup, and foreign key support for MySQL > > See http://www.innodb.com, download MySQL-Max from http://www.mysql.com > > > > > > > > > > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
You can if you have a MySQL version > 3.23.2, and are using the MyISAM handler. (http://www.mysql.com/doc/en/CREATE_INDEX.html) What version of MySQL do you use ?? - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Jocelyn Fournier" <[EMAIL PROTECTED]>; "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 6:38 PM Subject: RE: MySQL vs. Oracle (not speed) > > looks to me like I can only make indexes on fields that are not null... > this one is not , not null > > > -Original Message- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 11:34 AM > To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Hi, > > I see you do an order by on AdminHierarchy.WritingAgentSlot but this column > is not indexed. > Please tell me if an index on this column improve the speed of the query a > bit. > > Regards, > Jocelyn > - Original Message - > From: "Mary Stickney" <[EMAIL PROTECTED]> > To: "Mary Stickney" <[EMAIL PROTECTED]>; "Jocelyn Fournier" > <[EMAIL PROTECTED]>; "Mark Matthews" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 6:29 PM > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > incidentaly... Primary keys cant not be made on most of these tables due > to > > the fact that > > the farther back in time you got the more screwed up the data is... > > Fields have been added and they did not have the info to fill in the > > blanks... > > > > > > -Original Message- > > From: Mary Stickney [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 11:17 AM > > To: Jocelyn Fournier; Mark Matthews; [EMAIL PROTECTED] > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > > I am getting the taxid's I need from here so as not to try ane merge the > > entire table.. > > and there are 833... > > > > CREATE TABLE tempsap ( > > TempSapRecNum mediumint(9) NOT NULL auto_increment, > > StatusCode char(3) , > > Company varchar(10) , > > FirstYear varchar(4) , > > SecondYear varchar(4) , > > ThruDate varchar(12) , > > WritingAgentID varchar(10) , > > GroupID varchar(10) , > > TaxID varchar(10) NOT NULL , > > RegionCode varchar(10) , > > RegionName varchar(50) , > > AgentName varchar(50) , > > NewCasePercent double , > > FirstYearSales decimal(10,2) , > > SecondYearSales decimal(10,2) , > > AnnualPremium decimal(10,2) , > > PercentOfCases double , > > PremiumsInforce decimal(10,2) , > > ThirdYear varchar(4) , > > ThirdYearSales decimal(10,2) , > > Producerid varchar(20) , > > DistributionCode varchar(10) , > > TotalPaidPremium decimal(10,2), > > PRIMARY KEY (TempSapRecNum), > > KEY taxid (TaxID) > > ); > > > > > > -Original Message- > > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 11:01 AM > > To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] > > Subject: Re: MySQL vs. Oracle (not speed) > > > > > > Could you please send also tempsap ? > > > > Thanks and regards, > > Jocelyn > > > > - Original Message - > > From: "Mary Stickney" <[EMAIL PROTECTED]> > > To: "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Friday, August 16, 2002 4:55 PM > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > > this is a read only datawarehouse database we refresh monthly from > the > > > antiquated mainframe we have > > > > > > ok here are the tables > > > > > > Adminhierarchy 6.5 million records > > > CREATE TABLE adminhierarchy ( > > > Source char(2) NOT NULL , > > > WritingAgentID varchar(15) NOT NULL , > > > CoverageID varchar(15) NOT NULL , > > > AgentLevelID varchar(15) NOT NULL , > > > ProducerID varchar(15) NOT NULL , > > > ProducerTypeID varchar(5) NOT NULL , > > > PercentOfCase double , > > > RegionCode varchar(5) , > > > CoverageIdSbc varchar(10) NOT NULL , > > > WritingAgentSlot int(11) , > > > RadDistributionCode varchar(5), > > > KEY WritingAgentIDIndex (WritingAgentID), > > > KEY AgentLevelIDIndex (AgentLevelID), > > > KEY CoverageIDIndex (CoverageID), > > > KEY ProducerIDIndex (ProducerID), > > > KEY ProducerTypeIDIndex (ProducerTypeID), > > > KEY CoverageIdSbcIndex (CoverageIdSbc), > > > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc) > > > ); > > > > > > admin coverage 1.5 million > > > CREATE TABLE admincoverage ( > > > Source char(2) , > > > IsRider char(1) , > > > CoverageID varchar(10) NOT NULL , > > > CoverageIdSbc varchar(10) NOT NULL , > > > ParentCoverageID varchar(10) NOT NULL , > > > GroupID varchar(10) NOT NULL , > > > EmployeeID varchar(10) NOT NULL , > > > ProductId varchar(11) NOT NULL , > > > OriginalCertificateNumber varchar(20) , > > > StatusID varchar(10) NOT NULL , > > > ApplicationDate date , > > > effectivedate date NOT NULL , > > > PaidToDate date NOT NULL , > > > InitialPremiumDate date NOT NULL , > > > StatusDate date , > > > BenefitAmo
RE: MySQL vs. Oracle (not speed)
looks to me like I can only make indexes on fields that are not null... this one is not , not null -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 11:34 AM To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Hi, I see you do an order by on AdminHierarchy.WritingAgentSlot but this column is not indexed. Please tell me if an index on this column improve the speed of the query a bit. Regards, Jocelyn - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Mary Stickney" <[EMAIL PROTECTED]>; "Jocelyn Fournier" <[EMAIL PROTECTED]>; "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 6:29 PM Subject: RE: MySQL vs. Oracle (not speed) > > incidentaly... Primary keys cant not be made on most of these tables due to > the fact that > the farther back in time you got the more screwed up the data is... > Fields have been added and they did not have the info to fill in the > blanks... > > > -Original Message- > From: Mary Stickney [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 11:17 AM > To: Jocelyn Fournier; Mark Matthews; [EMAIL PROTECTED] > Subject: RE: MySQL vs. Oracle (not speed) > > > > I am getting the taxid's I need from here so as not to try ane merge the > entire table.. > and there are 833... > > CREATE TABLE tempsap ( > TempSapRecNum mediumint(9) NOT NULL auto_increment, > StatusCode char(3) , > Company varchar(10) , > FirstYear varchar(4) , > SecondYear varchar(4) , > ThruDate varchar(12) , > WritingAgentID varchar(10) , > GroupID varchar(10) , > TaxID varchar(10) NOT NULL , > RegionCode varchar(10) , > RegionName varchar(50) , > AgentName varchar(50) , > NewCasePercent double , > FirstYearSales decimal(10,2) , > SecondYearSales decimal(10,2) , > AnnualPremium decimal(10,2) , > PercentOfCases double , > PremiumsInforce decimal(10,2) , > ThirdYear varchar(4) , > ThirdYearSales decimal(10,2) , > Producerid varchar(20) , > DistributionCode varchar(10) , > TotalPaidPremium decimal(10,2), > PRIMARY KEY (TempSapRecNum), > KEY taxid (TaxID) > ); > > > -Original Message- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 11:01 AM > To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Could you please send also tempsap ? > > Thanks and regards, > Jocelyn > > - Original Message - > From: "Mary Stickney" <[EMAIL PROTECTED]> > To: "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 4:55 PM > Subject: RE: MySQL vs. Oracle (not speed) > > > > this is a read only datawarehouse database we refresh monthly from the > > antiquated mainframe we have > > > > ok here are the tables > > > > Adminhierarchy 6.5 million records > > CREATE TABLE adminhierarchy ( > > Source char(2) NOT NULL , > > WritingAgentID varchar(15) NOT NULL , > > CoverageID varchar(15) NOT NULL , > > AgentLevelID varchar(15) NOT NULL , > > ProducerID varchar(15) NOT NULL , > > ProducerTypeID varchar(5) NOT NULL , > > PercentOfCase double , > > RegionCode varchar(5) , > > CoverageIdSbc varchar(10) NOT NULL , > > WritingAgentSlot int(11) , > > RadDistributionCode varchar(5), > > KEY WritingAgentIDIndex (WritingAgentID), > > KEY AgentLevelIDIndex (AgentLevelID), > > KEY CoverageIDIndex (CoverageID), > > KEY ProducerIDIndex (ProducerID), > > KEY ProducerTypeIDIndex (ProducerTypeID), > > KEY CoverageIdSbcIndex (CoverageIdSbc), > > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc) > > ); > > > > admin coverage 1.5 million > > CREATE TABLE admincoverage ( > > Source char(2) , > > IsRider char(1) , > > CoverageID varchar(10) NOT NULL , > > CoverageIdSbc varchar(10) NOT NULL , > > ParentCoverageID varchar(10) NOT NULL , > > GroupID varchar(10) NOT NULL , > > EmployeeID varchar(10) NOT NULL , > > ProductId varchar(11) NOT NULL , > > OriginalCertificateNumber varchar(20) , > > StatusID varchar(10) NOT NULL , > > ApplicationDate date , > > effectivedate date NOT NULL , > > PaidToDate date NOT NULL , > > InitialPremiumDate date NOT NULL , > > StatusDate date , > > BenefitAmount double , > > ModalPremium double , > > AnnualPremium double , > > AccidentElim smallint(6) , > > AccidentElimUnitID varchar(5) , > > SicknessElim smallint(6) , > > SicknessElimUnitID varchar(5) , > > AccidentBenefit double , > > AccidentBenefitUnitID varchar(5) , > > SicknessBenefit double , > > SicknessBenefitUnitID varchar(5) , > > Waived char(1) , > > IssueAgePI smallint(6) , > > IssueStatePI char(2) , > > ResidentStatePI char(2) , > > ResidentZipPI varchar(9) , > > GenderPI char(1) , > > DateOfBirthPI date , > > TaxIDPI varchar(9) , > > RelationToEmp varchar(5) , > > BillTypeID varchar(10) NOT NULL , > > BillModeID varchar(10) NOT NULL , > > Da
Re: Query slowing down, need advice
On Fri, 16 Aug 2002, Brian Moon wrote: > I ran into a problem when the queries started to call for 500,000+ rows. > The query would not return. Here is the query and explain: > > select SQL_NO_CACHE id from single_table where approved='Y' AND datestamp >= > '2001-08-16 00:00:00' AND ( ( author LIKE '%myphorum%' OR subject LIKE > '%myphorum%' OR body LIKE '%myphorum%' ) ) order by datestamp desc; Brian, this query, having a joker at the beginning of each search string, scans through the whole table. It can't possibly be fast on large tables. Regards, Thomas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Persisten Connections
At 08:40 AM 8/14/2002, you wrote: >I am running a website which generates around 100,000 pageviews a day and I >am wondering if I stop using persistent conections to the MySQL database and >use >normal open and close conections this would reduce the load onto my server? > >Most conections are either made through my "main" file or the phorum message >board system. > >Thanks in advance >John Wards John, I profiled my PHP app with a debugger and discovered connecting to MySQL 4.x with persistent connections actually took 50% longer than non-persistent connections. (It added an extra 80ms to connect). So I don't see why persistent connections is useful at all with MySQL. Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
Hi, I see you do an order by on AdminHierarchy.WritingAgentSlot but this column is not indexed. Please tell me if an index on this column improve the speed of the query a bit. Regards, Jocelyn - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Mary Stickney" <[EMAIL PROTECTED]>; "Jocelyn Fournier" <[EMAIL PROTECTED]>; "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 6:29 PM Subject: RE: MySQL vs. Oracle (not speed) > > incidentaly... Primary keys cant not be made on most of these tables due to > the fact that > the farther back in time you got the more screwed up the data is... > Fields have been added and they did not have the info to fill in the > blanks... > > > -Original Message- > From: Mary Stickney [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 11:17 AM > To: Jocelyn Fournier; Mark Matthews; [EMAIL PROTECTED] > Subject: RE: MySQL vs. Oracle (not speed) > > > > I am getting the taxid's I need from here so as not to try ane merge the > entire table.. > and there are 833... > > CREATE TABLE tempsap ( > TempSapRecNum mediumint(9) NOT NULL auto_increment, > StatusCode char(3) , > Company varchar(10) , > FirstYear varchar(4) , > SecondYear varchar(4) , > ThruDate varchar(12) , > WritingAgentID varchar(10) , > GroupID varchar(10) , > TaxID varchar(10) NOT NULL , > RegionCode varchar(10) , > RegionName varchar(50) , > AgentName varchar(50) , > NewCasePercent double , > FirstYearSales decimal(10,2) , > SecondYearSales decimal(10,2) , > AnnualPremium decimal(10,2) , > PercentOfCases double , > PremiumsInforce decimal(10,2) , > ThirdYear varchar(4) , > ThirdYearSales decimal(10,2) , > Producerid varchar(20) , > DistributionCode varchar(10) , > TotalPaidPremium decimal(10,2), > PRIMARY KEY (TempSapRecNum), > KEY taxid (TaxID) > ); > > > -Original Message- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 11:01 AM > To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Could you please send also tempsap ? > > Thanks and regards, > Jocelyn > > - Original Message - > From: "Mary Stickney" <[EMAIL PROTECTED]> > To: "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 4:55 PM > Subject: RE: MySQL vs. Oracle (not speed) > > > > this is a read only datawarehouse database we refresh monthly from the > > antiquated mainframe we have > > > > ok here are the tables > > > > Adminhierarchy 6.5 million records > > CREATE TABLE adminhierarchy ( > > Source char(2) NOT NULL , > > WritingAgentID varchar(15) NOT NULL , > > CoverageID varchar(15) NOT NULL , > > AgentLevelID varchar(15) NOT NULL , > > ProducerID varchar(15) NOT NULL , > > ProducerTypeID varchar(5) NOT NULL , > > PercentOfCase double , > > RegionCode varchar(5) , > > CoverageIdSbc varchar(10) NOT NULL , > > WritingAgentSlot int(11) , > > RadDistributionCode varchar(5), > > KEY WritingAgentIDIndex (WritingAgentID), > > KEY AgentLevelIDIndex (AgentLevelID), > > KEY CoverageIDIndex (CoverageID), > > KEY ProducerIDIndex (ProducerID), > > KEY ProducerTypeIDIndex (ProducerTypeID), > > KEY CoverageIdSbcIndex (CoverageIdSbc), > > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc) > > ); > > > > admin coverage 1.5 million > > CREATE TABLE admincoverage ( > > Source char(2) , > > IsRider char(1) , > > CoverageID varchar(10) NOT NULL , > > CoverageIdSbc varchar(10) NOT NULL , > > ParentCoverageID varchar(10) NOT NULL , > > GroupID varchar(10) NOT NULL , > > EmployeeID varchar(10) NOT NULL , > > ProductId varchar(11) NOT NULL , > > OriginalCertificateNumber varchar(20) , > > StatusID varchar(10) NOT NULL , > > ApplicationDate date , > > effectivedate date NOT NULL , > > PaidToDate date NOT NULL , > > InitialPremiumDate date NOT NULL , > > StatusDate date , > > BenefitAmount double , > > ModalPremium double , > > AnnualPremium double , > > AccidentElim smallint(6) , > > AccidentElimUnitID varchar(5) , > > SicknessElim smallint(6) , > > SicknessElimUnitID varchar(5) , > > AccidentBenefit double , > > AccidentBenefitUnitID varchar(5) , > > SicknessBenefit double , > > SicknessBenefitUnitID varchar(5) , > > Waived char(1) , > > IssueAgePI smallint(6) , > > IssueStatePI char(2) , > > ResidentStatePI char(2) , > > ResidentZipPI varchar(9) , > > GenderPI char(1) , > > DateOfBirthPI date , > > TaxIDPI varchar(9) , > > RelationToEmp varchar(5) , > > BillTypeID varchar(10) NOT NULL , > > BillModeID varchar(10) NOT NULL , > > DateBilled date , > > Reinsured varchar(5) , > > InsuredLives char(2) , > > BenefitFrequency char(1) , > > BenefitPercent varchar(9) , > > SmokerPI char(1) , > > PaidUpDate date , > > SegId char(2) , > > RegionCode varchar(5) , > > TerminationDate date, > > KEY CoverageID (CoverageID), > > KEY
RE: MySQL vs. Oracle (not speed)
incidentaly... Primary keys cant not be made on most of these tables due to the fact that the farther back in time you got the more screwed up the data is... Fields have been added and they did not have the info to fill in the blanks... -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 11:17 AM To: Jocelyn Fournier; Mark Matthews; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) I am getting the taxid's I need from here so as not to try ane merge the entire table.. and there are 833... CREATE TABLE tempsap ( TempSapRecNum mediumint(9) NOT NULL auto_increment, StatusCode char(3) , Company varchar(10) , FirstYear varchar(4) , SecondYear varchar(4) , ThruDate varchar(12) , WritingAgentID varchar(10) , GroupID varchar(10) , TaxID varchar(10) NOT NULL , RegionCode varchar(10) , RegionName varchar(50) , AgentName varchar(50) , NewCasePercent double , FirstYearSales decimal(10,2) , SecondYearSales decimal(10,2) , AnnualPremium decimal(10,2) , PercentOfCases double , PremiumsInforce decimal(10,2) , ThirdYear varchar(4) , ThirdYearSales decimal(10,2) , Producerid varchar(20) , DistributionCode varchar(10) , TotalPaidPremium decimal(10,2), PRIMARY KEY (TempSapRecNum), KEY taxid (TaxID) ); -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 11:01 AM To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Could you please send also tempsap ? Thanks and regards, Jocelyn - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 4:55 PM Subject: RE: MySQL vs. Oracle (not speed) > this is a read only datawarehouse database we refresh monthly from the > antiquated mainframe we have > > ok here are the tables > > Adminhierarchy 6.5 million records > CREATE TABLE adminhierarchy ( > Source char(2) NOT NULL , > WritingAgentID varchar(15) NOT NULL , > CoverageID varchar(15) NOT NULL , > AgentLevelID varchar(15) NOT NULL , > ProducerID varchar(15) NOT NULL , > ProducerTypeID varchar(5) NOT NULL , > PercentOfCase double , > RegionCode varchar(5) , > CoverageIdSbc varchar(10) NOT NULL , > WritingAgentSlot int(11) , > RadDistributionCode varchar(5), > KEY WritingAgentIDIndex (WritingAgentID), > KEY AgentLevelIDIndex (AgentLevelID), > KEY CoverageIDIndex (CoverageID), > KEY ProducerIDIndex (ProducerID), > KEY ProducerTypeIDIndex (ProducerTypeID), > KEY CoverageIdSbcIndex (CoverageIdSbc), > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc) > ); > > admin coverage 1.5 million > CREATE TABLE admincoverage ( > Source char(2) , > IsRider char(1) , > CoverageID varchar(10) NOT NULL , > CoverageIdSbc varchar(10) NOT NULL , > ParentCoverageID varchar(10) NOT NULL , > GroupID varchar(10) NOT NULL , > EmployeeID varchar(10) NOT NULL , > ProductId varchar(11) NOT NULL , > OriginalCertificateNumber varchar(20) , > StatusID varchar(10) NOT NULL , > ApplicationDate date , > effectivedate date NOT NULL , > PaidToDate date NOT NULL , > InitialPremiumDate date NOT NULL , > StatusDate date , > BenefitAmount double , > ModalPremium double , > AnnualPremium double , > AccidentElim smallint(6) , > AccidentElimUnitID varchar(5) , > SicknessElim smallint(6) , > SicknessElimUnitID varchar(5) , > AccidentBenefit double , > AccidentBenefitUnitID varchar(5) , > SicknessBenefit double , > SicknessBenefitUnitID varchar(5) , > Waived char(1) , > IssueAgePI smallint(6) , > IssueStatePI char(2) , > ResidentStatePI char(2) , > ResidentZipPI varchar(9) , > GenderPI char(1) , > DateOfBirthPI date , > TaxIDPI varchar(9) , > RelationToEmp varchar(5) , > BillTypeID varchar(10) NOT NULL , > BillModeID varchar(10) NOT NULL , > DateBilled date , > Reinsured varchar(5) , > InsuredLives char(2) , > BenefitFrequency char(1) , > BenefitPercent varchar(9) , > SmokerPI char(1) , > PaidUpDate date , > SegId char(2) , > RegionCode varchar(5) , > TerminationDate date, > KEY CoverageID (CoverageID), > KEY CoverageIdSbc (CoverageIdSbc), > KEY GroupId (GroupID), > KEY ProductID (ProductId), > KEY StatusID (StatusID), > KEY ParentCoverageIdIndex (ParentCoverageID), > KEY EffectiveDateIndex (effectivedate), > KEY PaidToDateIndex (PaidToDate), > KEY InitPremDateIndex (InitialPremiumDate), > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc), > KEY CovIdCovIdSbcInitPremIndex (CoverageID, CoverageIdSbc, > InitialPremiumDate) > ); > > CREATE TABLE adminproducer ( > Source char(2) NOT NULL , > ProducerID varchar(15) NOT NULL , > ProducerNbr varchar(15) NOT NULL , >
RE: MySQL vs. Oracle (not speed)
thanks Serge.. incidentally my very first post to the list contained my query , and is the reason I joined this list in the first place. This 3 year sales report has been running for over 24 hours now. this is unacceptable, since they also wanbt a 5 year one and I am sure that will take 5 years to run... at this rate. -Original Message- From: Serge Paquin [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 11:01 AM To: Mark Matthews Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) I think this is a touch on the negative side. I'm sure many people get soured on MySQL when they to a post here and get yelled at for not trying hard enough. It sounds like Mary is having a valid problem. Her query works fast in one database and slow in the other. Because she did not come out and put her table schema and sql in the first email is no reason to insult her. I think a helping hand is a better approach. Mabey a couple general suggestions on MySQL tuning and a "We would need to see the schema and SQL statement to help further". I agree that it seems very strange that MS-SQL runs fater than MySQL on a query. Microsoft seems to always be plaiged with performance problems. Insulting someone will not help convert them to MySQL it will drive people away. Anyway enough of my rant... Serge. On Fri, 16 Aug 2002 09:46:44 -0500 "Mark Matthews" <[EMAIL PROTECTED]> wrote: > Mary Stickney wrote: > > What exactly is Trolling > > > > > > I find MYSQL to be slow , sorry if that doesn't met with your approval. > > But you don't give examples. > > I've found MS-SQL to be slow at times, especially when used from JDBC, > but I don't make crack comments about it newsgroups without backing up > my claims. > > Why don't you show us your queries? Do you really care to learn if MySQL > could be faster and you're just using it wrong? > > -Mark > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
I did the first day I joined the list and again today -Original Message- From: John Griffin [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 10:09 AM To: [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) Hi Mary, It's not a question of approval. If you are having problems with MySQL there is an entire list of people who are willing to offer support for free. Post your query and some table information and let's see if we can clear up your performance issues. John Griffin -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 10:28 AM To: Mark Matthews; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) What exactly is Trolling I find MYSQL to be slow , sorry if that doesn't met with your approval. -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 9:15 AM To: Mary Stickney; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Mary Stickney wrote: > I have been doing speed tests the same query ran on MYSQL took 45 > minutes > on MS-SQL it took 11 minutes.. > > yes you do get what you pay for Why not post the queries and the schemas here? My guess is you don't have something indexed correctly, or are using a query that gets optimized well by MS-SQL Server, but not MySQL. We all know that SQL is not absolutely portable, and that when you move queries from database to database, that there is some work to re-optimize them. There are some queries that just work better on databases other than MySQL, but they are very few and far-between. Without any way to backup your claim, it is hard for anyone here to believe that you have done everything possible to make a fair comparison. Given your previous comments in this forum, it appears that you must be trolling. -Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
Hi, Does the MySQL-4.1 development tree publicly available (if so, on which port ??) Regards, Jocelyn - Original Message - From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 3:42 PM Subject: Re: MySQL vs. Oracle (not speed) > Hi! > > > I tested a couple of days ago the MySQL-4.1 development tree. A simple > EXISTS subquery worked there. So there are already subqueries if you dare to > use the MySQL-4.1 development tree. > > Best regards, > > Heikki Tuuri > Innobase Oy > --- > InnoDB - transactions, hot backup, and foreign key support for MySQL > See http://www.innodb.com, download MySQL-Max from http://www.mysql.com > > > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
I am getting the taxid's I need from here so as not to try ane merge the entire table.. and there are 833... CREATE TABLE tempsap ( TempSapRecNum mediumint(9) NOT NULL auto_increment, StatusCode char(3) , Company varchar(10) , FirstYear varchar(4) , SecondYear varchar(4) , ThruDate varchar(12) , WritingAgentID varchar(10) , GroupID varchar(10) , TaxID varchar(10) NOT NULL , RegionCode varchar(10) , RegionName varchar(50) , AgentName varchar(50) , NewCasePercent double , FirstYearSales decimal(10,2) , SecondYearSales decimal(10,2) , AnnualPremium decimal(10,2) , PercentOfCases double , PremiumsInforce decimal(10,2) , ThirdYear varchar(4) , ThirdYearSales decimal(10,2) , Producerid varchar(20) , DistributionCode varchar(10) , TotalPaidPremium decimal(10,2), PRIMARY KEY (TempSapRecNum), KEY taxid (TaxID) ); -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 11:01 AM To: Mary Stickney; Mark Matthews; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Could you please send also tempsap ? Thanks and regards, Jocelyn - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 4:55 PM Subject: RE: MySQL vs. Oracle (not speed) > this is a read only datawarehouse database we refresh monthly from the > antiquated mainframe we have > > ok here are the tables > > Adminhierarchy 6.5 million records > CREATE TABLE adminhierarchy ( > Source char(2) NOT NULL , > WritingAgentID varchar(15) NOT NULL , > CoverageID varchar(15) NOT NULL , > AgentLevelID varchar(15) NOT NULL , > ProducerID varchar(15) NOT NULL , > ProducerTypeID varchar(5) NOT NULL , > PercentOfCase double , > RegionCode varchar(5) , > CoverageIdSbc varchar(10) NOT NULL , > WritingAgentSlot int(11) , > RadDistributionCode varchar(5), > KEY WritingAgentIDIndex (WritingAgentID), > KEY AgentLevelIDIndex (AgentLevelID), > KEY CoverageIDIndex (CoverageID), > KEY ProducerIDIndex (ProducerID), > KEY ProducerTypeIDIndex (ProducerTypeID), > KEY CoverageIdSbcIndex (CoverageIdSbc), > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc) > ); > > admin coverage 1.5 million > CREATE TABLE admincoverage ( > Source char(2) , > IsRider char(1) , > CoverageID varchar(10) NOT NULL , > CoverageIdSbc varchar(10) NOT NULL , > ParentCoverageID varchar(10) NOT NULL , > GroupID varchar(10) NOT NULL , > EmployeeID varchar(10) NOT NULL , > ProductId varchar(11) NOT NULL , > OriginalCertificateNumber varchar(20) , > StatusID varchar(10) NOT NULL , > ApplicationDate date , > effectivedate date NOT NULL , > PaidToDate date NOT NULL , > InitialPremiumDate date NOT NULL , > StatusDate date , > BenefitAmount double , > ModalPremium double , > AnnualPremium double , > AccidentElim smallint(6) , > AccidentElimUnitID varchar(5) , > SicknessElim smallint(6) , > SicknessElimUnitID varchar(5) , > AccidentBenefit double , > AccidentBenefitUnitID varchar(5) , > SicknessBenefit double , > SicknessBenefitUnitID varchar(5) , > Waived char(1) , > IssueAgePI smallint(6) , > IssueStatePI char(2) , > ResidentStatePI char(2) , > ResidentZipPI varchar(9) , > GenderPI char(1) , > DateOfBirthPI date , > TaxIDPI varchar(9) , > RelationToEmp varchar(5) , > BillTypeID varchar(10) NOT NULL , > BillModeID varchar(10) NOT NULL , > DateBilled date , > Reinsured varchar(5) , > InsuredLives char(2) , > BenefitFrequency char(1) , > BenefitPercent varchar(9) , > SmokerPI char(1) , > PaidUpDate date , > SegId char(2) , > RegionCode varchar(5) , > TerminationDate date, > KEY CoverageID (CoverageID), > KEY CoverageIdSbc (CoverageIdSbc), > KEY GroupId (GroupID), > KEY ProductID (ProductId), > KEY StatusID (StatusID), > KEY ParentCoverageIdIndex (ParentCoverageID), > KEY EffectiveDateIndex (effectivedate), > KEY PaidToDateIndex (PaidToDate), > KEY InitPremDateIndex (InitialPremiumDate), > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc), > KEY CovIdCovIdSbcInitPremIndex (CoverageID, CoverageIdSbc, > InitialPremiumDate) > ); > > CREATE TABLE adminproducer ( > Source char(2) NOT NULL , > ProducerID varchar(15) NOT NULL , > ProducerNbr varchar(15) NOT NULL , > IsAgency tinyint(4) , > ProducerName varchar(50) , > StatusID char(1) , > EffectiveDate date , > TerminationDate date , > TaxID varchar(9) NOT NULL , > Address1 varchar(50) , > Address2 varchar(50) , > City varchar(30) , > State char(2) , > Zip varchar(9) , > MailingAddress1 varchar(50) , > MailingAddress2 varchar(50) , > MailingCity varchar(30) , > MailingState char(2) , > MailingZip varchar(9) , > AdvanceBalance do
Re: MySQL vs. Oracle (not speed)
I think this is a touch on the negative side. I'm sure many people get soured on MySQL when they to a post here and get yelled at for not trying hard enough. It sounds like Mary is having a valid problem. Her query works fast in one database and slow in the other. Because she did not come out and put her table schema and sql in the first email is no reason to insult her. I think a helping hand is a better approach. Mabey a couple general suggestions on MySQL tuning and a "We would need to see the schema and SQL statement to help further". I agree that it seems very strange that MS-SQL runs fater than MySQL on a query. Microsoft seems to always be plaiged with performance problems. Insulting someone will not help convert them to MySQL it will drive people away. Anyway enough of my rant... Serge. On Fri, 16 Aug 2002 09:46:44 -0500 "Mark Matthews" <[EMAIL PROTECTED]> wrote: > Mary Stickney wrote: > > What exactly is Trolling > > > > > > I find MYSQL to be slow , sorry if that doesn't met with your approval. > > But you don't give examples. > > I've found MS-SQL to be slow at times, especially when used from JDBC, > but I don't make crack comments about it newsgroups without backing up > my claims. > > Why don't you show us your queries? Do you really care to learn if MySQL > could be faster and you're just using it wrong? > > -Mark > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
Could you please send also tempsap ? Thanks and regards, Jocelyn - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Mark Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 4:55 PM Subject: RE: MySQL vs. Oracle (not speed) > this is a read only datawarehouse database we refresh monthly from the > antiquated mainframe we have > > ok here are the tables > > Adminhierarchy 6.5 million records > CREATE TABLE adminhierarchy ( > Source char(2) NOT NULL , > WritingAgentID varchar(15) NOT NULL , > CoverageID varchar(15) NOT NULL , > AgentLevelID varchar(15) NOT NULL , > ProducerID varchar(15) NOT NULL , > ProducerTypeID varchar(5) NOT NULL , > PercentOfCase double , > RegionCode varchar(5) , > CoverageIdSbc varchar(10) NOT NULL , > WritingAgentSlot int(11) , > RadDistributionCode varchar(5), > KEY WritingAgentIDIndex (WritingAgentID), > KEY AgentLevelIDIndex (AgentLevelID), > KEY CoverageIDIndex (CoverageID), > KEY ProducerIDIndex (ProducerID), > KEY ProducerTypeIDIndex (ProducerTypeID), > KEY CoverageIdSbcIndex (CoverageIdSbc), > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc) > ); > > admin coverage 1.5 million > CREATE TABLE admincoverage ( > Source char(2) , > IsRider char(1) , > CoverageID varchar(10) NOT NULL , > CoverageIdSbc varchar(10) NOT NULL , > ParentCoverageID varchar(10) NOT NULL , > GroupID varchar(10) NOT NULL , > EmployeeID varchar(10) NOT NULL , > ProductId varchar(11) NOT NULL , > OriginalCertificateNumber varchar(20) , > StatusID varchar(10) NOT NULL , > ApplicationDate date , > effectivedate date NOT NULL , > PaidToDate date NOT NULL , > InitialPremiumDate date NOT NULL , > StatusDate date , > BenefitAmount double , > ModalPremium double , > AnnualPremium double , > AccidentElim smallint(6) , > AccidentElimUnitID varchar(5) , > SicknessElim smallint(6) , > SicknessElimUnitID varchar(5) , > AccidentBenefit double , > AccidentBenefitUnitID varchar(5) , > SicknessBenefit double , > SicknessBenefitUnitID varchar(5) , > Waived char(1) , > IssueAgePI smallint(6) , > IssueStatePI char(2) , > ResidentStatePI char(2) , > ResidentZipPI varchar(9) , > GenderPI char(1) , > DateOfBirthPI date , > TaxIDPI varchar(9) , > RelationToEmp varchar(5) , > BillTypeID varchar(10) NOT NULL , > BillModeID varchar(10) NOT NULL , > DateBilled date , > Reinsured varchar(5) , > InsuredLives char(2) , > BenefitFrequency char(1) , > BenefitPercent varchar(9) , > SmokerPI char(1) , > PaidUpDate date , > SegId char(2) , > RegionCode varchar(5) , > TerminationDate date, > KEY CoverageID (CoverageID), > KEY CoverageIdSbc (CoverageIdSbc), > KEY GroupId (GroupID), > KEY ProductID (ProductId), > KEY StatusID (StatusID), > KEY ParentCoverageIdIndex (ParentCoverageID), > KEY EffectiveDateIndex (effectivedate), > KEY PaidToDateIndex (PaidToDate), > KEY InitPremDateIndex (InitialPremiumDate), > KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc), > KEY CovIdCovIdSbcInitPremIndex (CoverageID, CoverageIdSbc, > InitialPremiumDate) > ); > > CREATE TABLE adminproducer ( > Source char(2) NOT NULL , > ProducerID varchar(15) NOT NULL , > ProducerNbr varchar(15) NOT NULL , > IsAgency tinyint(4) , > ProducerName varchar(50) , > StatusID char(1) , > EffectiveDate date , > TerminationDate date , > TaxID varchar(9) NOT NULL , > Address1 varchar(50) , > Address2 varchar(50) , > City varchar(30) , > State char(2) , > Zip varchar(9) , > MailingAddress1 varchar(50) , > MailingAddress2 varchar(50) , > MailingCity varchar(30) , > MailingState char(2) , > MailingZip varchar(9) , > AdvanceBalance double , > DistributionCode varchar(5), > KEY ProducerIDIndex (ProducerID), > KEY ProducerNbrIndex (ProducerNbr), > KEY TaxIDIndex (TaxID) > ); > > CREATE TABLE adminproduct ( > Source char(2) , > IsRider char(1) , > ProductID varchar(15) NOT NULL , > ProductNbr varchar(15) , > ProductDescription varchar(50) , > UnderwriterID varchar(15) , > LOBID varchar(15) NOT NULL , > BusinessType varchar(5) , > StatutoryLinesCode varchar(5) , > WaiverAvailable tinyint(4) , > AccidentElim mediumint(9) , > AccidentBen1 double , > AccidentBen2 double , > SickElim mediumint(9) , > SickBen1 double , > SickBen2 double, > KEY ProductIdIndex (ProductID), > KEY LobIdIndex (LOBID) > ); > > > -Original Message- > From: Mark Matthews [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 9:15 AM > To: Mary Stickney; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Mary Stickney wrote: > > I have been doing speed tests the same query ran on MYSQL took 45 > > minutes > > on MS-SQL it took 11 minutes.. > > > > yes you do get what you pay for > > Why not post the queries and the schemas here? My guess is you don't > have something indexed correctly, or are using a query that gets > optimized well by MS-SQL
Re: Is this a known issue?
Song, Friday, August 16, 2002, 12:57:58 AM, you wrote: S> Well, I am not sure if anyone has experienced the same problem, or if this S> problem has been documented, but this is what I have noticed: S> After one of my data table was full, I started to delete old records from S> the table to allow new data to be inserted. Since then, the LIMIT function S> stop working. I used to be able to do: S> SELECT * FROM mytable LIMIT 0, 50 S> or S> DELETE FROM mytable LIMIT 0, 50 S> Now it doesn't work any more. I have to add a WHERE clause in order to make S> the LIMIT working. For instance: S> SELECT * FROM mytable WHERE id > 0 LIMIT 0, 50 S> or S> DELETE FROM mytable WHERE id > 0 LIMIT 0, 50 S> However, this is not a good query considering 15 million records in the S> table. I have repeated the same problem on two systems with similar setting. Run CHECK TABLE table_name to know if the table is not crushed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: libmysql
Daniel, Friday, August 16, 2002, 3:19:40 PM, you wrote: DK> I'm interested in what are the differences between different versions of DK> libmysql.dll's and libmysql.so's? Exported functions are different. DK> And are their versions the same under linux and windows? DK> Where can I get their header files in C or Object Pascal? Check /include dir, you can find header files there. As to Object Pascal, seems there are some interfaces for libmysql.dll at: http://www.mysql.com/doc/en/Contrib.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strange network error using mysql,mysql c api
Krata,
Friday, August 16, 2002, 5:10:20 PM, you wrote:
K> Hi,
K> i am using mysql c api to send some queries to mysql server which is on
K> another computer (100MBit almost next to the client)
K> in my code i am doing this
K> if (mysql_real_query(mysql,query,strlen(query)))
K> {
K> fprintf(stderr,"In query : \"%s\"\n",query);
K> fprintf(stderr,"Error occured : %s\n",mysql_error(mysql));
K> }
K> and this sometimes (e.g. 1 from 1.000.000 sends) do this :
K> In query : "insert into TR_ITEMS_TEMP
(PLU_ID,SOL_PRICE,VOIDED,TR_ID,DPH,DELETE_INDEX) values (-70,500,0,8052,2,8301)
K> "
K> Error occured : Unknown column 'QR_ID' in 'field list'
K> In query : "insert into TR_ITEMS_TEMP
(PLU_ID,SOL_PRICE,VOIDED,TR_ID,DPH,DELETE_INDEX) values (-70,500,0,8052,2,8301)
K> "
K> do someone know what should be the problem?
K> Server is Win2K and has latest mysql server installed client is the one
K> from RedHat 7.3 (3.23.49) (=`mysql_config --version`)
8-[ ]
I tested your example and it works nice...
Do you always get this error or you got it just once?
Does error occur if you execute query via mysql command line client
tool?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: Setting root password in mysql for windows
Art, Thursday, August 15, 2002, 4:53:26 PM, you wrote: AF> Still could not get it to work so I uninstealled and reinstalled. Will use AF> the anonymous login etc util I figure out this security system. Is there a AF> document that explains the password system in laymans terms? You can find info about privilege system in the MySQL manual. Check the following chapters: 4.2 General Security Issues and the MySQL Access Privilege System http://www.mysql.com/doc/en/Privilege_system.html and 4.3 MySQL User Account Management http://www.mysql.com/doc/en/User_Account_Management.html And in the Paul DuBois's book 'MySQL' -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I cannot get mysql to run.
John, Thursday, August 15, 2002, 1:03:58 PM, you wrote: JS> >Description: JS> <[root@localhost mysql]# ./bin/mysqladmin version JS> ./bin/mysqladmin: connect to server at 'localhost' failed JS> error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' JS> (111)' JS> Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! JS> [root@localhost mysql]# JS> I have checked this and the socks in this directory says 0 bytes. Check if MySQL server is running. If MySQL server isn't running, look into error log file to find what is the cause of failed start. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
yes I do -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 10:37 AM To: Mary Stickney; Francisco; Elizabeth Bogner; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Do you have an index on AdminProducer.taxid and on tempsap.taxid ? - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Jocelyn Fournier" <[EMAIL PROTECTED]>; "Francisco" <[EMAIL PROTECTED]>; "Elizabeth Bogner" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 5:31 PM Subject: RE: MySQL vs. Oracle (not speed) > > explian returns this > > ;table;type;possible_keys;key;key_len;ref;rows;Extra > ;table;type;possible_keys;key;key_len;ref;rows;Extra > ;AdminCoverage;range;CoverageID,CoverageIdSbc,InitPremDateIndex,CovIdCovIdSb > cIndex,CovIdCovIdSbcInitPremIndex;InitPremDateIndex;3;;315663;where used; > Using temporary; Using filesort > ;AdminHierarchy;ref;CoverageIDIndex,CoverageIdSbcIndex,CovIdCovIdSbcIndex;Co > vIdCovIdSbcIndex;25;AdminCoverage.CoverageID,AdminCoverage.CoverageIdSbc;7;w > here used > ;AdminProducer;ref;ProducerIDIndex;ProducerIDIndex;15;AdminHierarchy.Writing > AgentID;11; > ;AdminProduct;ref;ProductIdIndex;ProductIdIndex;15;AdminCoverage.ProductId;1 > 1; > ;tempsap;ALL;10019; > > -Original Message- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 10:21 AM > To: Mary Stickney; Francisco; Elizabeth Bogner; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Hi, > > Which columns are indexed in your table ? > What does EXPLAIN into MySQL return ? > > Regards, > Jocelyn > - Original Message - > From: "Mary Stickney" <[EMAIL PROTECTED]> > To: "Francisco" <[EMAIL PROTECTED]>; "Elizabeth Bogner" > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 4:31 PM > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > I am not for one or the other I just hate to wait > > I need speed... > > > > we already have a MS-SQL server , so no more money needs to me spent... > > > > I did a 4 table join , drwing 3 years of sales data > > > > table one 6.5 million records --- AdminHierarchy > > table two 1.5 million records AdminCoverage > > table three 10191 records --- AdminProcuder > > table four 19823 records --- AdminProduct > > > > SELECT AdminHierarchy.WritingAgentID, AdminHierarchy.WritingAgentSlot, > > AdminHierarchy.ProducerID, > > tempsap.taxid, (ModalPremium * BillModeID * (PercentOfCase / 100)) AS > > TotalPaidPremium, > > AdminCoverage.CoverageID, AdminCoverage.CoverageIDSbc, > > AdminHierarchy.RegionCode, > > AdminProduct.LobId, AdminCoverage.StatusID, AdminCoverage.StatusDate, > > AdminCoverage.InitialPremiumDate, AdminCoverage.PaidToDate, > tempsap.GROUPID > > FROM AdminHierarchy > > INNER JOIN AdminCoverage ON > > AdminHierarchy.CoverageID=AdminCoverage.CoverageID > > AND AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc > > LEFT JOIN AdminProducer ON > > AdminProducer.ProducerID=AdminHierarchy.WritingAgentID > > LEFT JOIN AdminProduct ON AdminCoverage.ProductID=AdminProduct.ProductID > > Left join tempsap on AdminProducer.taxid = tempsap.taxid > > WHERE AdminCoverage.InitialPremiumDate >= '2101' AND > > AdminCoverage.InitialPremiumDate <= '20020430' > > ORDER BY AdminHierarchy.WritingAgentSlot,AdminCoverage.CoverageId, > > AdminCoverage.CoverageIdSbc, > > AdminHierarchy.ProducerID > > > > > > > > -Original Message- > > From: Francisco [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 9:21 AM > > To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > Hi Mary, > > > > I am not specially against or pro MySQL, Microsoft SQL > > Server, Oracle or any other database. Teams make their > > choices based on the project needs such as budget (is > > your team ready to spend thousands of dollars on > > Oracle and marry that corporation forever?), > > deployment (do you want your product with Oracle's > > price tag attached to it?), functionality: does the > > database server provide a viable solution technically > > speaking?, etc, etc. So it is not an issue of good or > > bad. > > > > But regardless of all that, what we should have, at > > least, is a great respect for people that has been > > working so hard to provide an affordable and viable > > alternative to the database server giants. I don't > > think that throwing those numbers without any other > > explanations about your test environments, SQL, > > tables, etc is a good practice. Do you think that you > > always get what you pay when you spend thousand of > > dollars in software and services without leaving you > > any other choices? > > > > I know that you did not put bad intentions behind your > > comment but should be more careful and precise. > > > > Whoever is interested on some benchmarks can go to: > > http://www.mysql.com/information/benchmarks.html > >
Re: MySQL vs. Oracle (not speed)
Mary Stickney wrote: > no need to get defensive ... > > We did do a timed test. > > I am trying to sped up a program that currently take over 12 hours to run... > I started running it yesterday morning and it is still going. and going and > going... Would it be possible for you to send the list the output of "EXPLAIN [your query]". That will give us a lot of insight into what's going on internally in MySQL. -Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
Do you have an index on AdminProducer.taxid and on tempsap.taxid ? - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Jocelyn Fournier" <[EMAIL PROTECTED]>; "Francisco" <[EMAIL PROTECTED]>; "Elizabeth Bogner" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 5:31 PM Subject: RE: MySQL vs. Oracle (not speed) > > explian returns this > > ;table;type;possible_keys;key;key_len;ref;rows;Extra > ;table;type;possible_keys;key;key_len;ref;rows;Extra > ;AdminCoverage;range;CoverageID,CoverageIdSbc,InitPremDateIndex,CovIdCovIdSb > cIndex,CovIdCovIdSbcInitPremIndex;InitPremDateIndex;3;;315663;where used; > Using temporary; Using filesort > ;AdminHierarchy;ref;CoverageIDIndex,CoverageIdSbcIndex,CovIdCovIdSbcIndex;Co > vIdCovIdSbcIndex;25;AdminCoverage.CoverageID,AdminCoverage.CoverageIdSbc;7;w > here used > ;AdminProducer;ref;ProducerIDIndex;ProducerIDIndex;15;AdminHierarchy.Writing > AgentID;11; > ;AdminProduct;ref;ProductIdIndex;ProductIdIndex;15;AdminCoverage.ProductId;1 > 1; > ;tempsap;ALL;10019; > > -Original Message- > From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 10:21 AM > To: Mary Stickney; Francisco; Elizabeth Bogner; [EMAIL PROTECTED] > Subject: Re: MySQL vs. Oracle (not speed) > > > Hi, > > Which columns are indexed in your table ? > What does EXPLAIN into MySQL return ? > > Regards, > Jocelyn > - Original Message - > From: "Mary Stickney" <[EMAIL PROTECTED]> > To: "Francisco" <[EMAIL PROTECTED]>; "Elizabeth Bogner" > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, August 16, 2002 4:31 PM > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > I am not for one or the other I just hate to wait > > I need speed... > > > > we already have a MS-SQL server , so no more money needs to me spent... > > > > I did a 4 table join , drwing 3 years of sales data > > > > table one 6.5 million records --- AdminHierarchy > > table two 1.5 million records AdminCoverage > > table three 10191 records --- AdminProcuder > > table four 19823 records --- AdminProduct > > > > SELECT AdminHierarchy.WritingAgentID, AdminHierarchy.WritingAgentSlot, > > AdminHierarchy.ProducerID, > > tempsap.taxid, (ModalPremium * BillModeID * (PercentOfCase / 100)) AS > > TotalPaidPremium, > > AdminCoverage.CoverageID, AdminCoverage.CoverageIDSbc, > > AdminHierarchy.RegionCode, > > AdminProduct.LobId, AdminCoverage.StatusID, AdminCoverage.StatusDate, > > AdminCoverage.InitialPremiumDate, AdminCoverage.PaidToDate, > tempsap.GROUPID > > FROM AdminHierarchy > > INNER JOIN AdminCoverage ON > > AdminHierarchy.CoverageID=AdminCoverage.CoverageID > > AND AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc > > LEFT JOIN AdminProducer ON > > AdminProducer.ProducerID=AdminHierarchy.WritingAgentID > > LEFT JOIN AdminProduct ON AdminCoverage.ProductID=AdminProduct.ProductID > > Left join tempsap on AdminProducer.taxid = tempsap.taxid > > WHERE AdminCoverage.InitialPremiumDate >= '2101' AND > > AdminCoverage.InitialPremiumDate <= '20020430' > > ORDER BY AdminHierarchy.WritingAgentSlot,AdminCoverage.CoverageId, > > AdminCoverage.CoverageIdSbc, > > AdminHierarchy.ProducerID > > > > > > > > -Original Message- > > From: Francisco [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 9:21 AM > > To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > Hi Mary, > > > > I am not specially against or pro MySQL, Microsoft SQL > > Server, Oracle or any other database. Teams make their > > choices based on the project needs such as budget (is > > your team ready to spend thousands of dollars on > > Oracle and marry that corporation forever?), > > deployment (do you want your product with Oracle's > > price tag attached to it?), functionality: does the > > database server provide a viable solution technically > > speaking?, etc, etc. So it is not an issue of good or > > bad. > > > > But regardless of all that, what we should have, at > > least, is a great respect for people that has been > > working so hard to provide an affordable and viable > > alternative to the database server giants. I don't > > think that throwing those numbers without any other > > explanations about your test environments, SQL, > > tables, etc is a good practice. Do you think that you > > always get what you pay when you spend thousand of > > dollars in software and services without leaving you > > any other choices? > > > > I know that you did not put bad intentions behind your > > comment but should be more careful and precise. > > > > Whoever is interested on some benchmarks can go to: > > http://www.mysql.com/information/benchmarks.html > > > > There is an interesting article comparing Ms-SQL, > > Oracle, DB2 and MySQL in: > > http://www.pcmag.com/article2/0,4149,7279,00.asp > > > > Sincerely, > > > > Francisco > > > > --- Mary Stickney <[EMAIL PROTECTED]> wrote: > > > > > > I
RE: MySQL vs. Oracle (not speed)
explian returns this ;table;type;possible_keys;key;key_len;ref;rows;Extra ;table;type;possible_keys;key;key_len;ref;rows;Extra ;AdminCoverage;range;CoverageID,CoverageIdSbc,InitPremDateIndex,CovIdCovIdSb cIndex,CovIdCovIdSbcInitPremIndex;InitPremDateIndex;3;;315663;where used; Using temporary; Using filesort ;AdminHierarchy;ref;CoverageIDIndex,CoverageIdSbcIndex,CovIdCovIdSbcIndex;Co vIdCovIdSbcIndex;25;AdminCoverage.CoverageID,AdminCoverage.CoverageIdSbc;7;w here used ;AdminProducer;ref;ProducerIDIndex;ProducerIDIndex;15;AdminHierarchy.Writing AgentID;11; ;AdminProduct;ref;ProductIdIndex;ProductIdIndex;15;AdminCoverage.ProductId;1 1; ;tempsap;ALL;10019; -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 10:21 AM To: Mary Stickney; Francisco; Elizabeth Bogner; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Hi, Which columns are indexed in your table ? What does EXPLAIN into MySQL return ? Regards, Jocelyn - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Francisco" <[EMAIL PROTECTED]>; "Elizabeth Bogner" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 4:31 PM Subject: RE: MySQL vs. Oracle (not speed) > > I am not for one or the other I just hate to wait > I need speed... > > we already have a MS-SQL server , so no more money needs to me spent... > > I did a 4 table join , drwing 3 years of sales data > > table one 6.5 million records --- AdminHierarchy > table two 1.5 million records AdminCoverage > table three 10191 records --- AdminProcuder > table four 19823 records --- AdminProduct > > SELECT AdminHierarchy.WritingAgentID, AdminHierarchy.WritingAgentSlot, > AdminHierarchy.ProducerID, > tempsap.taxid, (ModalPremium * BillModeID * (PercentOfCase / 100)) AS > TotalPaidPremium, > AdminCoverage.CoverageID, AdminCoverage.CoverageIDSbc, > AdminHierarchy.RegionCode, > AdminProduct.LobId, AdminCoverage.StatusID, AdminCoverage.StatusDate, > AdminCoverage.InitialPremiumDate, AdminCoverage.PaidToDate, tempsap.GROUPID > FROM AdminHierarchy > INNER JOIN AdminCoverage ON > AdminHierarchy.CoverageID=AdminCoverage.CoverageID > AND AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc > LEFT JOIN AdminProducer ON > AdminProducer.ProducerID=AdminHierarchy.WritingAgentID > LEFT JOIN AdminProduct ON AdminCoverage.ProductID=AdminProduct.ProductID > Left join tempsap on AdminProducer.taxid = tempsap.taxid > WHERE AdminCoverage.InitialPremiumDate >= '2101' AND > AdminCoverage.InitialPremiumDate <= '20020430' > ORDER BY AdminHierarchy.WritingAgentSlot,AdminCoverage.CoverageId, > AdminCoverage.CoverageIdSbc, > AdminHierarchy.ProducerID > > > > -Original Message- > From: Francisco [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 9:21 AM > To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] > Subject: RE: MySQL vs. Oracle (not speed) > > > Hi Mary, > > I am not specially against or pro MySQL, Microsoft SQL > Server, Oracle or any other database. Teams make their > choices based on the project needs such as budget (is > your team ready to spend thousands of dollars on > Oracle and marry that corporation forever?), > deployment (do you want your product with Oracle's > price tag attached to it?), functionality: does the > database server provide a viable solution technically > speaking?, etc, etc. So it is not an issue of good or > bad. > > But regardless of all that, what we should have, at > least, is a great respect for people that has been > working so hard to provide an affordable and viable > alternative to the database server giants. I don't > think that throwing those numbers without any other > explanations about your test environments, SQL, > tables, etc is a good practice. Do you think that you > always get what you pay when you spend thousand of > dollars in software and services without leaving you > any other choices? > > I know that you did not put bad intentions behind your > comment but should be more careful and precise. > > Whoever is interested on some benchmarks can go to: > http://www.mysql.com/information/benchmarks.html > > There is an interesting article comparing Ms-SQL, > Oracle, DB2 and MySQL in: > http://www.pcmag.com/article2/0,4149,7279,00.asp > > Sincerely, > > Francisco > > --- Mary Stickney <[EMAIL PROTECTED]> wrote: > > > > I have been doing speed tests the same query > > ran on MYSQL took 45 > > minutes > > on MS-SQL it took 11 minutes.. > > > > yes you do get what you pay for > > > > -Original Message- > > From: Francisco [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 8:47 AM > > To: Mary Stickney; Elizabeth Bogner; > > [EMAIL PROTECTED] > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > Hi, > > > > I am beging using MySQL for quite a while and it is > > a > > very good choice if you don't really need stored > > procedures. MySQL provides a pretty good > > implementatio
Re: MySQL vs. Oracle (not speed)
Hi, Which columns are indexed in your table ? What does EXPLAIN into MySQL return ? Regards, Jocelyn - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Francisco" <[EMAIL PROTECTED]>; "Elizabeth Bogner" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 4:31 PM Subject: RE: MySQL vs. Oracle (not speed) > > I am not for one or the other I just hate to wait > I need speed... > > we already have a MS-SQL server , so no more money needs to me spent... > > I did a 4 table join , drwing 3 years of sales data > > table one 6.5 million records --- AdminHierarchy > table two 1.5 million records AdminCoverage > table three 10191 records --- AdminProcuder > table four 19823 records --- AdminProduct > > SELECT AdminHierarchy.WritingAgentID, AdminHierarchy.WritingAgentSlot, > AdminHierarchy.ProducerID, > tempsap.taxid, (ModalPremium * BillModeID * (PercentOfCase / 100)) AS > TotalPaidPremium, > AdminCoverage.CoverageID, AdminCoverage.CoverageIDSbc, > AdminHierarchy.RegionCode, > AdminProduct.LobId, AdminCoverage.StatusID, AdminCoverage.StatusDate, > AdminCoverage.InitialPremiumDate, AdminCoverage.PaidToDate, tempsap.GROUPID > FROM AdminHierarchy > INNER JOIN AdminCoverage ON > AdminHierarchy.CoverageID=AdminCoverage.CoverageID > AND AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc > LEFT JOIN AdminProducer ON > AdminProducer.ProducerID=AdminHierarchy.WritingAgentID > LEFT JOIN AdminProduct ON AdminCoverage.ProductID=AdminProduct.ProductID > Left join tempsap on AdminProducer.taxid = tempsap.taxid > WHERE AdminCoverage.InitialPremiumDate >= '2101' AND > AdminCoverage.InitialPremiumDate <= '20020430' > ORDER BY AdminHierarchy.WritingAgentSlot,AdminCoverage.CoverageId, > AdminCoverage.CoverageIdSbc, > AdminHierarchy.ProducerID > > > > -Original Message- > From: Francisco [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 9:21 AM > To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] > Subject: RE: MySQL vs. Oracle (not speed) > > > Hi Mary, > > I am not specially against or pro MySQL, Microsoft SQL > Server, Oracle or any other database. Teams make their > choices based on the project needs such as budget (is > your team ready to spend thousands of dollars on > Oracle and marry that corporation forever?), > deployment (do you want your product with Oracle's > price tag attached to it?), functionality: does the > database server provide a viable solution technically > speaking?, etc, etc. So it is not an issue of good or > bad. > > But regardless of all that, what we should have, at > least, is a great respect for people that has been > working so hard to provide an affordable and viable > alternative to the database server giants. I don't > think that throwing those numbers without any other > explanations about your test environments, SQL, > tables, etc is a good practice. Do you think that you > always get what you pay when you spend thousand of > dollars in software and services without leaving you > any other choices? > > I know that you did not put bad intentions behind your > comment but should be more careful and precise. > > Whoever is interested on some benchmarks can go to: > http://www.mysql.com/information/benchmarks.html > > There is an interesting article comparing Ms-SQL, > Oracle, DB2 and MySQL in: > http://www.pcmag.com/article2/0,4149,7279,00.asp > > Sincerely, > > Francisco > > --- Mary Stickney <[EMAIL PROTECTED]> wrote: > > > > I have been doing speed tests the same query > > ran on MYSQL took 45 > > minutes > > on MS-SQL it took 11 minutes.. > > > > yes you do get what you pay for > > > > -Original Message- > > From: Francisco [mailto:[EMAIL PROTECTED]] > > Sent: Friday, August 16, 2002 8:47 AM > > To: Mary Stickney; Elizabeth Bogner; > > [EMAIL PROTECTED] > > Subject: RE: MySQL vs. Oracle (not speed) > > > > > > Hi, > > > > I am beging using MySQL for quite a while and it is > > a > > very good choice if you don't really need stored > > procedures. MySQL provides a pretty good > > implementation of a subset of MySQL-92, performance > > is > > great, it is cross-platform, provides transactions, > > and its price... well is free. > > > > Hope it helps. > > --- Mary Stickney <[EMAIL PROTECTED]> wrote: > > > > > > It doesn't suport alot of differnt things > > > it dosent have store procedures , dosent have a > > > complete SQL command set... > > > > > > I am using it becasue I am being forced to... > > > > > > > > > > > > -Original Message- > > > From: Elizabeth Bogner > > > [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, August 15, 2002 9:25 PM > > > To: [EMAIL PROTECTED] > > > Subject: MySQL vs. Oracle (not speed) > > > > > > > > > > > > A company I work with is in the process of > > upgrading > > > its databases from > > > some > > > motheaten system to something current. My > > impression > > > is that they > > > want to go with Oracle, and I'm not sure if thi
Re: MySQL ERROR 1133 MEANING?
Hi, Victoria: The version installed is mysql-3.23.51. I don't know why my previous operation did not succeed. However, I now grant the permissions through "insert into db values (...) and succeed. THank you very much. Duo >From: Victoria Reznichenko <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Subject: Re: MySQL ERROR 1133 MEANING? >Date: Fri, 16 Aug 2002 17:02:01 +0300 > >duo, >Friday, August 16, 2002, 2:23:52 AM, you wrote: > >df> Hi friends: >df> I try to grant privileges to a user named 'glen' but not succeed. Here >is >df> the script: > >[skip] > >df> mysql> grant all privileges on test.* to 'glen'@'localhost' identified >by >df> 'tidbs1'; >df> ERROR 1133: Can't find any matching row in the user table >df> mysql> >df> -### > >df> Could you please tell me what the real meaning of the ERROR? The user >an >df> doassword just remains in the user table? How come to say "Can't find >any >df> matching row in the user table"? > >What version of MySQL do you use? > > > > >-- >For technical support contracts, goto https://order.mysql.com/?ref=ensita >This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Victoria Reznichenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] >/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
Hi Mary, It's not a question of approval. If you are having problems with MySQL there is an entire list of people who are willing to offer support for free. Post your query and some table information and let's see if we can clear up your performance issues. John Griffin -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 10:28 AM To: Mark Matthews; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) What exactly is Trolling I find MYSQL to be slow , sorry if that doesn't met with your approval. -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 9:15 AM To: Mary Stickney; [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Mary Stickney wrote: > I have been doing speed tests the same query ran on MYSQL took 45 > minutes > on MS-SQL it took 11 minutes.. > > yes you do get what you pay for Why not post the queries and the schemas here? My guess is you don't have something indexed correctly, or are using a query that gets optimized well by MS-SQL Server, but not MySQL. We all know that SQL is not absolutely portable, and that when you move queries from database to database, that there is some work to re-optimize them. There are some queries that just work better on databases other than MySQL, but they are very few and far-between. Without any way to backup your claim, it is hard for anyone here to believe that you have done everything possible to make a fair comparison. Given your previous comments in this forum, it appears that you must be trolling. -Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
speed is of the utmost importance , I can't have a client run a report and wait 2 days for it to finish. -Original Message- From: Aron Pilhofer [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 9:46 AM To: Mary Stickney; [EMAIL PROTECTED]; Francisco; Elizabeth Bogner; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) Well, I meant this to be off-list (thus the next message) because I don't want to start a round of M$ bashing, blah, blah... But since I blew it already, I don't mean to sound harsh or defensive, because, frankly, I don't have a vested interest one way or the other which db you use. And I'm not doubting your results. I'm simply saying you should probably look at your settings/configuration because MySQL should not be lagging that far behind SQL Server. That is unless there is something particularly unique about your setup or tables or data that is causing queries to run so slowly on MySQL. If speed is important to you (and it sounds like it is) then it might be worth tweaking some to figure out what is causing MySQL to lag so badly. Ok, 'nuff said. aron > -Original Message- > From: Mary Stickney [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 10:36 AM > To: [EMAIL PROTECTED]; Francisco; Elizabeth Bogner; > [EMAIL PROTECTED] > Subject: RE: MySQL vs. Oracle (not speed) > > > > no need to get defensive ... > > We did do a timed test. > > I am trying to sped up a program that currently take over 12 > hours to run... > I started running it yesterday morning and it is still going. and > going and > going... > > > > > > -Original Message- > From: Aron Pilhofer [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 9:25 AM > To: Mary Stickney; Francisco; Elizabeth Bogner; [EMAIL PROTECTED] > Subject: RE: MySQL vs. Oracle (not speed) > > > Not to open a can of worms here, Mary, but I'd love to know > exactly how you > got those results, since they basically contradict every known benchmark I > have seen (at least those not funded by M$... this being one: > http://www.eweek.com/article2/0,3959,293,00.asp) as well as every > benchmarking test I have run myself. > > My results, frankly, have not been close -- not even in the same ballpark. > So I suspect if you are getting different results it has something to do > with your configuration. > > Just for the record: I'm not particularly anti-MS. I like Windows XP, I > think they make some decent software (Access and Excel are what I teach on > most often). And you do get a lot of nice features with SQL > Server -- speed > just doesn't happen to be one of them. > > Regards, > > aron > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
it has been sent to the list but here it is again I did a 4 table join , drwing 3 years of sales data table one 6.5 million records --- AdminHierarchy table two 1.5 million records AdminCoverage table three 10191 records --- AdminProcuder table four 19823 records --- AdminProduct SELECT AdminHierarchy.WritingAgentID, AdminHierarchy.WritingAgentSlot, AdminHierarchy.ProducerID, tempsap.taxid, (ModalPremium * BillModeID * (PercentOfCase / 100)) AS TotalPaidPremium, AdminCoverage.CoverageID, AdminCoverage.CoverageIDSbc, AdminHierarchy.RegionCode, AdminProduct.LobId, AdminCoverage.StatusID, AdminCoverage.StatusDate, AdminCoverage.InitialPremiumDate, AdminCoverage.PaidToDate, tempsap.GROUPID FROM AdminHierarchy INNER JOIN AdminCoverage ON AdminHierarchy.CoverageID=AdminCoverage.CoverageID AND AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc LEFT JOIN AdminProducer ON AdminProducer.ProducerID=AdminHierarchy.WritingAgentID LEFT JOIN AdminProduct ON AdminCoverage.ProductID=AdminProduct.ProductID Left join tempsap on AdminProducer.taxid = tempsap.taxid WHERE AdminCoverage.InitialPremiumDate >= '2101' AND AdminCoverage.InitialPremiumDate <= '20020430' ORDER BY AdminHierarchy.WritingAgentSlot,AdminCoverage.CoverageId, AdminCoverage.CoverageIdSbc, AdminHierarchy.ProducerID -Original Message- From: Tom Gao [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 9:22 AM To: [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) what query was it may I ask ? Tom - Original Message - From: "Mary Stickney" <[EMAIL PROTECTED]> To: "Francisco" <[EMAIL PROTECTED]>; "Elizabeth Bogner" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 16, 2002 11:52 PM Subject: RE: MySQL vs. Oracle (not speed) > > I have been doing speed tests the same query ran on MYSQL took 45 > minutes > on MS-SQL it took 11 minutes.. > > yes you do get what you pay for > > -Original Message- > From: Francisco [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 8:47 AM > To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] > Subject: RE: MySQL vs. Oracle (not speed) > > > Hi, > > I am beging using MySQL for quite a while and it is a > very good choice if you don't really need stored > procedures. MySQL provides a pretty good > implementation of a subset of MySQL-92, performance is > great, it is cross-platform, provides transactions, > and its price... well is free. > > Hope it helps. > --- Mary Stickney <[EMAIL PROTECTED]> wrote: > > > > It doesn't suport alot of differnt things > > it dosent have store procedures , dosent have a > > complete SQL command set... > > > > I am using it becasue I am being forced to... > > > > > > > > -Original Message- > > From: Elizabeth Bogner > > [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, August 15, 2002 9:25 PM > > To: [EMAIL PROTECTED] > > Subject: MySQL vs. Oracle (not speed) > > > > > > > > A company I work with is in the process of upgrading > > its databases from > > some > > motheaten system to something current. My impression > > is that they > > want to go with Oracle, and I'm not sure if this is > > based on anything > > other than being impressed with the size and > > presumed quality support > > of Oracle. I'd like to encourage them to at least > > seriously consider > > using > > MySQL instead. > > > > I don't think that speed is a huge factor here; we > > do a lot of XML > > publishing > > and content management, but at most we'd have > > several gigabytes of > > data and several dozen simultaneous users, so well > > within the > > capabilities > > of MySQL. I've looked at various things I could > > find, like the benchmarks > > pages (probably not relevant) and the MySQL myths > > page, which was > > somewhat helpful, but I couldn't find anything more > > along the lines of > > "How to Convince my Management to go with MySQL." I > > don't even know > > what to expect from them, but I'm imagining they'll > > say, "But MySQL > > doesn't support sub-selects," to which I can reply, > > "But you can write > > most of those as joins anyway, so it won't matter > > because the software > > will all be written from scratch." Etc. > > > > Are there pointers anyone can give me? > > > > E. Bognewitz > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list > > archive) > > > > To request this thread, e-mail > > <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > > > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: > > http://lists.mysql.com/php/unsubscribe.php > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list > > archive) > > > > To request this thread, e-mail > > <[EM
RE: MySQL vs. Oracle (not speed)
I did send in my query the day I joined this list and the table structures. I just did again... -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 9:47 AM To: Mary Stickney Cc: [EMAIL PROTECTED] Subject: Re: MySQL vs. Oracle (not speed) Mary Stickney wrote: > What exactly is Trolling > > > I find MYSQL to be slow , sorry if that doesn't met with your approval. But you don't give examples. I've found MS-SQL to be slow at times, especially when used from JDBC, but I don't make crack comments about it newsgroups without backing up my claims. Why don't you show us your queries? Do you really care to learn if MySQL could be faster and you're just using it wrong? -Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
this is a read only datawarehouse database we refresh monthly from the antiquated mainframe we have ok here are the tables Adminhierarchy 6.5 million records CREATE TABLE adminhierarchy ( Source char(2) NOT NULL , WritingAgentID varchar(15) NOT NULL , CoverageID varchar(15) NOT NULL , AgentLevelID varchar(15) NOT NULL , ProducerID varchar(15) NOT NULL , ProducerTypeID varchar(5) NOT NULL , PercentOfCase double , RegionCode varchar(5) , CoverageIdSbc varchar(10) NOT NULL , WritingAgentSlot int(11) , RadDistributionCode varchar(5), KEY WritingAgentIDIndex (WritingAgentID), KEY AgentLevelIDIndex (AgentLevelID), KEY CoverageIDIndex (CoverageID), KEY ProducerIDIndex (ProducerID), KEY ProducerTypeIDIndex (ProducerTypeID), KEY CoverageIdSbcIndex (CoverageIdSbc), KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc) ); admin coverage 1.5 million CREATE TABLE admincoverage ( Source char(2) , IsRider char(1) , CoverageID varchar(10) NOT NULL , CoverageIdSbc varchar(10) NOT NULL , ParentCoverageID varchar(10) NOT NULL , GroupID varchar(10) NOT NULL , EmployeeID varchar(10) NOT NULL , ProductId varchar(11) NOT NULL , OriginalCertificateNumber varchar(20) , StatusID varchar(10) NOT NULL , ApplicationDate date , effectivedate date NOT NULL , PaidToDate date NOT NULL , InitialPremiumDate date NOT NULL , StatusDate date , BenefitAmount double , ModalPremium double , AnnualPremium double , AccidentElim smallint(6) , AccidentElimUnitID varchar(5) , SicknessElim smallint(6) , SicknessElimUnitID varchar(5) , AccidentBenefit double , AccidentBenefitUnitID varchar(5) , SicknessBenefit double , SicknessBenefitUnitID varchar(5) , Waived char(1) , IssueAgePI smallint(6) , IssueStatePI char(2) , ResidentStatePI char(2) , ResidentZipPI varchar(9) , GenderPI char(1) , DateOfBirthPI date , TaxIDPI varchar(9) , RelationToEmp varchar(5) , BillTypeID varchar(10) NOT NULL , BillModeID varchar(10) NOT NULL , DateBilled date , Reinsured varchar(5) , InsuredLives char(2) , BenefitFrequency char(1) , BenefitPercent varchar(9) , SmokerPI char(1) , PaidUpDate date , SegId char(2) , RegionCode varchar(5) , TerminationDate date, KEY CoverageID (CoverageID), KEY CoverageIdSbc (CoverageIdSbc), KEY GroupId (GroupID), KEY ProductID (ProductId), KEY StatusID (StatusID), KEY ParentCoverageIdIndex (ParentCoverageID), KEY EffectiveDateIndex (effectivedate), KEY PaidToDateIndex (PaidToDate), KEY InitPremDateIndex (InitialPremiumDate), KEY CovIdCovIdSbcIndex (CoverageID, CoverageIdSbc), KEY CovIdCovIdSbcInitPremIndex (CoverageID, CoverageIdSbc, InitialPremiumDate) ); CREATE TABLE adminproducer ( Source char(2) NOT NULL , ProducerID varchar(15) NOT NULL , ProducerNbr varchar(15) NOT NULL , IsAgency tinyint(4) , ProducerName varchar(50) , StatusID char(1) , EffectiveDate date , TerminationDate date , TaxID varchar(9) NOT NULL , Address1 varchar(50) , Address2 varchar(50) , City varchar(30) , State char(2) , Zip varchar(9) , MailingAddress1 varchar(50) , MailingAddress2 varchar(50) , MailingCity varchar(30) , MailingState char(2) , MailingZip varchar(9) , AdvanceBalance double , DistributionCode varchar(5), KEY ProducerIDIndex (ProducerID), KEY ProducerNbrIndex (ProducerNbr), KEY TaxIDIndex (TaxID) ); CREATE TABLE adminproduct ( Source char(2) , IsRider char(1) , ProductID varchar(15) NOT NULL , ProductNbr varchar(15) , ProductDescription varchar(50) , UnderwriterID varchar(15) , LOBID varchar(15) NOT NULL , BusinessType varchar(5) , StatutoryLinesCode varchar(5) , WaiverAvailable tinyint(4) , AccidentElim mediumint(9) , AccidentBen1 double , AccidentBen2 double , SickElim mediumint(9) , SickBen1 double , SickBen2 double, KEY ProductIdIndex (ProductID), KEY LobIdIndex (LOBID) ); -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 9:15 AM To: Mary Stickney; [EMAIL PROTECTED] Subject: Re: MySQL vs. Ora
RE: Migrating Oracle To MySQL
Try the script oracledump.pl at this link. I used it to export my Oracle data to Mysql and it worked great. Some variables to modify at the top of the script, but that's it. http://mirrors.sunsite.dk/mysql/Downloads/Contrib/oracledump Stanley G. Martin Sprint - ISS Enterprise App/Decision Support Services 913.315.4576 913.226.3090 PCS [EMAIL PROTECTED] -Original Message- From: francois.joubert [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 8:46 AM To: mysql Subject: Migrating Oracle To MySQL Hi I am new to the world of databases. We will be installing an Oracle9i database shortly as the backend for our current financial package. We hope to replace it with MySQL in the future. The data held in the database will be predominantly of text-type. However, some data fields will be binary. Is it a scary job transferring data from Oracle to MySQL? What tools are available to assist in such a process. Thanks. Francois -- Imagination 25 Store Street South Crescent London WC1E 7BL England | Tel +44 20 7323 3300Fax +44 20 7323 5801 | ___| - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. Oracle (not speed)
Mary Stickney wrote: > What exactly is Trolling > > > I find MYSQL to be slow , sorry if that doesn't met with your approval. But you don't give examples. I've found MS-SQL to be slow at times, especially when used from JDBC, but I don't make crack comments about it newsgroups without backing up my claims. Why don't you show us your queries? Do you really care to learn if MySQL could be faster and you're just using it wrong? -Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
I did crash the MYSQL server the other day... I am currently looping thru and getting the sales agent by agent. I tried to do the query for all 804 agents at one time and after an hour and a 1/2 and still not being done decided against doing it that way. next I decided I should have a table already made of all these tables joined for reporting purposes, it would speed my reports. well I tried to join them all and insert them into another table ... crashed big time on that one... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
Well, I meant this to be off-list (thus the next message) because I don't want to start a round of M$ bashing, blah, blah... But since I blew it already, I don't mean to sound harsh or defensive, because, frankly, I don't have a vested interest one way or the other which db you use. And I'm not doubting your results. I'm simply saying you should probably look at your settings/configuration because MySQL should not be lagging that far behind SQL Server. That is unless there is something particularly unique about your setup or tables or data that is causing queries to run so slowly on MySQL. If speed is important to you (and it sounds like it is) then it might be worth tweaking some to figure out what is causing MySQL to lag so badly. Ok, 'nuff said. aron > -Original Message- > From: Mary Stickney [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 10:36 AM > To: [EMAIL PROTECTED]; Francisco; Elizabeth Bogner; > [EMAIL PROTECTED] > Subject: RE: MySQL vs. Oracle (not speed) > > > > no need to get defensive ... > > We did do a timed test. > > I am trying to sped up a program that currently take over 12 > hours to run... > I started running it yesterday morning and it is still going. and > going and > going... > > > > > > -Original Message- > From: Aron Pilhofer [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 9:25 AM > To: Mary Stickney; Francisco; Elizabeth Bogner; [EMAIL PROTECTED] > Subject: RE: MySQL vs. Oracle (not speed) > > > Not to open a can of worms here, Mary, but I'd love to know > exactly how you > got those results, since they basically contradict every known benchmark I > have seen (at least those not funded by M$... this being one: > http://www.eweek.com/article2/0,3959,293,00.asp) as well as every > benchmarking test I have run myself. > > My results, frankly, have not been close -- not even in the same ballpark. > So I suspect if you are getting different results it has something to do > with your configuration. > > Just for the record: I'm not particularly anti-MS. I like Windows XP, I > think they make some decent software (Access and Excel are what I teach on > most often). And you do get a lot of nice features with SQL > Server -- speed > just doesn't happen to be one of them. > > Regards, > > aron > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
no need to get defensive ... We did do a timed test. I am trying to sped up a program that currently take over 12 hours to run... I started running it yesterday morning and it is still going. and going and going... -Original Message- From: Aron Pilhofer [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 9:25 AM To: Mary Stickney; Francisco; Elizabeth Bogner; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) Not to open a can of worms here, Mary, but I'd love to know exactly how you got those results, since they basically contradict every known benchmark I have seen (at least those not funded by M$... this being one: http://www.eweek.com/article2/0,3959,293,00.asp) as well as every benchmarking test I have run myself. My results, frankly, have not been close -- not even in the same ballpark. So I suspect if you are getting different results it has something to do with your configuration. Just for the record: I'm not particularly anti-MS. I like Windows XP, I think they make some decent software (Access and Excel are what I teach on most often). And you do get a lot of nice features with SQL Server -- speed just doesn't happen to be one of them. Regards, aron - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select from two different tables query
Hello, I have two different tables with similar fields (id, name, profession, hobbies) Some people might be listed in both tables. I need a write query to select count(*) from the tables group by name... i'd like it get something like this: Joe Smith. total 5 records . 3 records from table 1 2 records from table 2 Any help will be greatly appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed)
The "Open Source" argument went down well with my management. Of course, both Oracle and MySQL have full support teams, and should fix any bugs in their databases promptly - and, so far as I know, they do. However, we have had problems with (other) large companies in the past when we find bugs in what they regard as "not mainstream code" - features used by very few customers but essential to us. They promise to fix it "some day" - but we are too much of a minnow to influence their plans. If such a problem were to occur with Oracle, you have no recourse. If it occurs with MySQL - if all else fails, you can dive in and fix it yourself. Of course you hope never to resort to such an expedient (and a small company such as MySQL is probably more amenable to altering their plans, at a cost, than a giant) but it is a nice fallback line to have for the worst case. Alec Cawley - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
