Re: working around lack of subselects
On Thu, Feb 06, 2003 at 09:52:11AM -0500, Brent Baisley wrote: As long as the foreign keys are index it should be pretty quick. There really is no way to get around processing every record in table data. As that table grows, your query will get slower. But a 157K rows isn't really a whole lot. Thanks Brent. I know 157K rows isn't a lot, but you probably don't realise just how budget this hardware is. :) Anyway I'm pleased to say that I was pleasantly surprised at how fast the query completed even on this less than ideal server, it takes around a minute. As I only need to do it once a day and even once a week would be acceptable, there's plenty of room for growth there. Thanks for reassuring me there. :) - 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: IS NULL problem with dates; confirmed on 3.23.x 4.09
On Wednesday 05 February 2003 02:07, aah at volunteermatch dot org wrote: Description: Multiple IS NULL tests in a WHERE clause appear to be broken when testing NOT NULL date fields. Queries work fine against varchar and int, probably(?) others. How-To-Repeat: create temporary table test (a date not null, b date not null); insert into test values (now(), now()); insert into test values (now(), now()); select count(*) from test where a is null or b is null; The SELECT query should return 0, instead it returns every row in the table (2 in this case). For what it's worth, the query returns the right number for int and varchar fields, and returns the right number if the columns allow null values. Thanks for bug report! It's fixed. -- 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: converting MS SQL to MySQL
Hi Hawk [EMAIL PROTECTED], I'm not sure if you have checked the manual yet, but following links seem to be somewhat related to your query: * http://www.mysql.com/doc/en/Using_mSQL_tools.html * http://www.mysql.com/doc/en/Using_InnoDB_tables.html This was an automated response to your email 'converting MS SQL to MySQL'. Final search keyword used to query the manual was 'converting MS SQL'. Comming soon... * Support for querying mysql archives for related discussions. Feedbacks, suggestions and complaints should be directed to [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: backslash before newline
On 6 Feb 2003, at 8:58, Michael Boudreau wrote: mysql select review into outfile '/tmp/MS55038rev1.txt' from MsReviews where msid=55038 and msreviewid=1; Query OK, 1 row affected (0.00 sec) mysql quit Bye The newline seems to have had a \ (backslash, octal 134) inserted before it: mss (mrb)% more /tmp/MS55038rev1.txt 123\ 456 That's normal. If you don't specify any output options, the end of each record is marked with a newline, and backslash is used as the escape character to mark any newlines that are actually part of the data. It's explained in the documentation, mainly in the LOAD DATA section: http://www.mysql.com/doc/en/SELECT.html http://www.mysql.com/doc/en/LOAD_DATA.html Without the backslashes, how will you know where the record ends? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: Is it my English or does this sound bad?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Guy -- ...and then Defryn, Guy said... % % I was just reading through the Mysql manual section 3.32 % And came across a sentence that started like this % % Animal sex can be represented in a variety of ways % % My mother tongue is not English but doesn't it sound rude ? :-) I get a giggle out of that whenever I read it, too :-) I've heard various folks talk about how that has been changed in newer versions of the manual. When you get down to it, though, it still talks about animal sex. I'm surprised they don't either leave it like it is because it doesn't really matter (and geeks such as I can make anything funny) or change from 'sex' to 'gender' to make the subject unambiguously a noun. HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+Q97dGb7uCXufRwARAptPAJ0YnLRhP6zhM/mRfKi+37fsa1Uy6ACeJhPf yNo1PEQtPSw/J3cicEFs+eg= =TsqR -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: backslash before newline
In the last episode (Feb 06), Michael Boudreau said: Here's something weird: text strings containing newlines seem to have a backslash character added when the data is exported from the database. (MySQL 3.23.37 on Solaris--yes we're upgrading to .54) However, when I read the data out to a file... mysql select review into outfile '/tmp/MS55038rev1.txt' from MsReviews where msid=55038 and msreviewid=1; Query OK, 1 row affected (0.00 sec) mysql quit Bye The newline seems to have had a \ (backslash, octal 134) inserted before it: mss (mrb)% more /tmp/MS55038rev1.txt 123\ 456 This is documented behaviour. INTO OUTFILE is the inverse of LOAD DATA INFILE, and escapes fields in exactly the same way. From the docs: `SELECT ... INTO OUTFILE' is the complement of `LOAD DATA INFILE'; the syntax for the `export_options' part of the statement consists of the same `FIELDS' and `LINES' clauses that are used with the `LOAD DATA INFILE' statement. *Note `LOAD DATA': LOAD DATA. * If you use `INTO DUMPFILE' instead of `INTO OUTFILE', MySQL will only write one row into the file, without any column or line terminations and without any escaping. This is useful if you want to store a blob in a file. -- 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 IMAP Server
Ben Clewett wrote: I was rather trying to find an IMAP server using MySQL. Ie, somewhere to store my millions of email messages, from many execlent mailing lists, and get at them fast... What you want is the Courier-IMAP mail server which uses the Maildir format to store its messages, running on ReiserFS. Maildir is a one file per message storage format for more reliability and faster message finding because of ReiserFS which uses very fast B+-type tree searching of filenames. See http://www.inter7.com for more information. (This is what I use, and I have hundreds of thousands of E-mails in IMAP format, some of my clients, using the same servers have gigabytes of E-mails stored this way and find it to be faster than local storage). -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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: converting MS SQL to MySQL
check the docs at www.mysql.com for the CORRECT command to create indexes. =C= * Cal Evans * Stay plugged into your audience. * http://www.christianperformer.com -Original Message- From: Hawk [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 06, 2003 9:03 AM To: [EMAIL PROTECTED] Subject: converting MS SQL to MySQL Hi, I am new to SQL. I am attempting to create a database that was created in MS SQL to MySQL. The below SQL statements works with MS SQL. I am executing the following: CREATE TABLE 'Accounting'( 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Addr' VARCHAR(128) NULL, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Start' DATETIME NULL, 'Stop' DATETIME NULL, 'SessionTime' INTEGER DEFAULT 0, 'ExtraTime' INTEGER DEFAULT 0, 'TimeLeft' INTEGER DEFAULT 0, 'KBytesIn' INTEGER DEFAULT 0, 'KBytesOut' INTEGER DEFAULT 0, 'SessionKB' INTEGER DEFAULT 0, 'ExtraKB' INTEGER DEFAULT 0, 'KBytesLeft' INTEGER DEFAULT 0, ) GO CREATE INDEX IX_Username ON Accounting(Username) MySQL will not take this command GO CREATE TABLE 'ActiveUsers'( 'UserID' VARCHAR(254) PRIMARY KEY, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Address' VARCHAR(128) NULL, 'LoginTime' DATETIME NULL, ) GO CREATE TABLE 'TAC_GRP'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_GRP(TAC_ID, TAC_Attr) MySQL will not take this command GO CREATE TABLE 'TAC_USR'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_USR(TAC_ID, TAC_Attr) MySQL will not take this command GO Thanks in advance. --- [This E-mail scanned for viruses by Friend.ly.net.] - 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: replication
Hi [EMAIL PROTECTED], I'm not sure if you have checked the manual yet, but following links seem to be somewhat related to your query: * http://www.mysql.com/doc/en/Replication.html * http://www.mysql.com/doc/en/Replication_FAQ.html * http://www.mysql.com/doc/en/Replication_HOWTO.html * http://www.mysql.com/doc/en/Replication_SQL.html * http://www.mysql.com/doc/en/Replication_Problems.html * http://www.mysql.com/doc/en/Replication_Options.html * http://www.mysql.com/doc/en/Replication_Features.html * http://www.mysql.com/doc/en/Replication_Implementation.html This was an automated response to your email 'replication'. Final search keyword used to query the manual was 'replication'. Comming soon... * Support for querying mysql archives for related discussions. Feedbacks, suggestions and complaints should be directed to [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: converting MS SQL to MySQL
At 04:13 PM 2/7/2003 +, you wrote: We sell a data converter for this purpose check out www.bpenet.net I'm not concern about transferring the data. I was having problems creating the indexes. thanks for the reply... Michael Johnson Managing Director Humphrey Consulting Limited BPEnet Tel/Fax: 0870 922 0247 email: [EMAIL PROTECTED] websites: www.humphreyconsulting.co.uk www.bpenet.net -Original Message- From: Hawk [mailto:[EMAIL PROTECTED]] Sent: 06 February 2003 3:03 PM To: [EMAIL PROTECTED] Subject: converting MS SQL to MySQL Hi, I am new to SQL. I am attempting to create a database that was created in MS SQL to MySQL. The below SQL statements works with MS SQL. I am executing the following: CREATE TABLE 'Accounting'( 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Addr' VARCHAR(128) NULL, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Start' DATETIME NULL, 'Stop' DATETIME NULL, 'SessionTime' INTEGER DEFAULT 0, 'ExtraTime' INTEGER DEFAULT 0, 'TimeLeft' INTEGER DEFAULT 0, 'KBytesIn' INTEGER DEFAULT 0, 'KBytesOut' INTEGER DEFAULT 0, 'SessionKB' INTEGER DEFAULT 0, 'ExtraKB' INTEGER DEFAULT 0, 'KBytesLeft' INTEGER DEFAULT 0, ) GO CREATE INDEX IX_Username ON Accounting(Username) MySQL will not take this command GO CREATE TABLE 'ActiveUsers'( 'UserID' VARCHAR(254) PRIMARY KEY, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Address' VARCHAR(128) NULL, 'LoginTime' DATETIME NULL, ) GO CREATE TABLE 'TAC_GRP'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_GRP(TAC_ID, TAC_Attr) MySQL will not take this command GO CREATE TABLE 'TAC_USR'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_USR(TAC_ID, TAC_Attr) MySQL will not take this command GO Thanks in advance. --- [This E-mail scanned for viruses by Friend.ly.net.] - 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] com Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --- [This E-mail scanned for viruses by friend.ly.net.] --- [This E-mail scanned for viruses by Friend.ly.net.] - 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: Batch mode / stored prodecure question?
Hmm, I thought this question I posted last weekend was a pretty good one, however, no replies have been posted :-( I am new to MySQL and this mailing list, is the the proper place and format for questions of this type? Regards, Bill Hi all: I am building a database containing usage information for a wireless ISP application. I am using a RADIUS server (Steel Belted Radius on Win 2K) to handle RADIUS events from the network hardware and then write to a MySQL database. Everything works fine but I have a problem: the RADIUS server can only execute a single SQL statement. This is fine for creating a usage record, but I need to do more processing and execute several SQL statements in order to do all the usage tracking I need. The RADIUS server can call a stored procedure and pass parameters to the procedure. This is what I need. I see that MySQL has a batch mode, but I do not see any mention of how I could pass parameters to a MySQL batch file. Can I do this, if so, how? I see that stored procedures are being added in a future release of MySQL, do I need to wait for this? Thanks in advance, Bill Brownrigg Project Manager DSL Holdings, Inc. - 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
Help with default NULL
Hi, I have a table Log described as such: +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | rec | int(1)| YES | | NULL| | | time | int(10) | | | 0 | | | ct | char(100) | YES | | NULL| | +--+---+--+-+-+---+ Notice that default for ct is NULL. Now I have a ^ separated line in my file which I am loading via LOAD FILE. This line MAY or MAY NOT have the corresponding ct value. So, it could either be ^www.yahoo.com^(ct value) or .^^.(no ct value). I would like that when there is no ct value present in the line, the ct value in Log table should stay as the default NULLbut it is not. The value becomes '' i.e. a zero length string. Any ideas on how can I keep it as NULL? Any help is much appreciated. Thanks in advance, Bhavin. sql, 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
request for recommendations on a machine
I've gotten some weird responses to this type of question before, but I'd like some input anyway. We are getting a new dedicated database server. Currently our load average is hovering above 2 and although things work fine, that snappiness is fading as the load becomes higher. We may be able to squeeze some more speed out of the current machine, but we are interested in making a redundant setup anyway, so a new machine it is. Currently, we have a dual 1.3 Ghz PIII processor machine with raid 1 scsi160 drives on Linux 2.4.7-10smp with 1GB RAM running 3.23.49a-Max on an ext3 partition. I feel that there are three problems. High load in general, not the best filesystem for the job, and the fact that there is only one raid1 diskspace. This is my solution: dual Xeon 2.8 GHz, 2GB RAM, 1 RAID 1 partition (2 disks) for the OS, 1 RAID5 partition (3disks) for the db. Then, what I would like to get some feedback on is whether to use a raw partition or what filesystem. Although ext3 is slow, I have actually had somebody unplug the database machine, plug it back in, and when it rebooted, everything worked fine and there was no corruption. I think I have to thank ext3 for that. If the raw partition cannot handle that, then I am not interested in using it. Any comments are greatly appreciated. Does anybody have 300 queries per second or more? What kind of hardware do you have? This is top now: 11:56am up 22 days, 2:07, 1 user, load average: 4.22, 3.64, 3.25 100 processes: 96 sleeping, 4 running, 0 zombie, 0 stopped CPU0 states: 93.4% user, 6.2% system, 0.0% nice, 0.0% idle CPU1 states: 90.2% user, 9.4% system, 0.0% nice, 0.0% idle Mem: 1028432K av, 1020672K used,7760K free, 0K shrd, 53612K buff Swap: 522104K av, 33328K used, 488776K free 364720K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 4434 mysql 17 0 441M 409M 2744 R35.3 40.7 901:02 mysqld-max 4470 mysql 9 0 441M 409M 2744 S21.0 40.7 61:07 mysqld-max 4462 mysql 17 0 441M 409M 2744 S20.2 40.7 63:07 mysqld-max 4463 mysql 9 0 441M 409M 2744 S20.2 40.7 65:34 mysqld-max 4449 mysql 15 0 441M 409M 2744 R19.3 40.7 66:47 mysqld-max 18483 mysql 10 0 441M 409M 2744 S19.1 40.7 103:58 mysqld-max 4437 mysql 14 0 441M 409M 2744 S14.8 40.7 64:52 mysqld-max mysql 9 0 441M 409M 2744 S11.0 40.7 65:39 mysqld-max 4450 mysql 16 0 441M 409M 2744 S10.1 40.7 65:14 mysqld-max 8776 adam 15 0 988 988 764 R 7.2 0.0 0:03 top 4001 mysql 9 0 441M 409M 2744 S 6.6 40.7 79:12 mysqld-max 18580 mysql 9 0 441M 409M 2744 S 4.9 40.7 101:26 mysqld-max 4467 mysql 9 0 441M 409M 2744 S 4.4 40.7 64:10 mysqld-max 18577 mysql 13 0 441M 409M 2744 R 1.5 40.7 99:48 mysqld-max 4432 mysql 9 0 441M 409M 2744 S 0.3 40.7 66:15 mysqld-max 4458 mysql 9 0 441M 409M 2744 S 0.3 40.7 62:31 mysqld-max And mysqladmin status: Uptime: 1908444 Threads: 10 Questions: 268078040 Slow queries: 21540 Opens: 1098 Flush tables: 1 Open tables: 730 Queries per second avg: 140.469 And vmstat: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 2 0 0 33328 5104 53652 366912 0 0 3 66 2 4 1 4 - 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
Best Way to optimize this
Hi, I'm trying to learn a little about query optimization.. I have a query that is pretty slow, and was wondering if there is an easy way to speed it up. mysql EXPLAIN - SELECT - mailfrom, - sum((msgsize)/100) as mb, - count(id) as count, - avg(hits) as hits - FROM email - GROUP BY mailfrom - ORDER BY count DESC - LIMIT 10; +---+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | | +---+--+---+--+-+--++-+ | email | ALL | NULL | NULL |NULL | NULL | 343030 | Using temporary; |Using filesort | +---+--+---+--+-+--++-+ 1 row in set (0.00 sec) mysql describe email; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | | +-+-+--+-+-++ | id | int(12) unsigned| | PRI | NULL| |auto_increment | | datetime| datetime| | MUL | 2003-01-01 00:00:00 | | | | spam| tinyint(1) unsigned | YES | MUL | 0 | | | | virus | tinyint(1) unsigned | YES | MUL | 0 | | | | hits| decimal(5,2)| YES | | 0.00| | | | msgsize | int(12) | YES | | 0 | | | | mailfrom| varchar(128)| YES | MUL | NULL| | | | rcptto | varchar(128)| YES | MUL | NULL| | | | subject | varchar(128)| YES | MUL | NULL| | | +-+-+--+-+-++ runing the query gives takes... mysql 10 rows in set (46.45 sec) I have indexed everything except hits and msgsize, should I index those also, or will it even matter? TIA! Dallas - 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: converting MS SQL to MySQL
The best way is to import data from MS SQL Server to MySQL. SQLyog at http://www.webyog.com/sqlyog/download.html has a DTS like ODBC Wizard. Try it out and all you data will be imported. Insane - Original Message - From: Hawk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 06, 2003 8:33 PM Subject: converting MS SQL to MySQL Hi, I am new to SQL. I am attempting to create a database that was created in MS SQL to MySQL. The below SQL statements works with MS SQL. I am executing the following: CREATE TABLE 'Accounting'( 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Addr' VARCHAR(128) NULL, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Start' DATETIME NULL, 'Stop' DATETIME NULL, 'SessionTime' INTEGER DEFAULT 0, 'ExtraTime' INTEGER DEFAULT 0, 'TimeLeft' INTEGER DEFAULT 0, 'KBytesIn' INTEGER DEFAULT 0, 'KBytesOut' INTEGER DEFAULT 0, 'SessionKB' INTEGER DEFAULT 0, 'ExtraKB' INTEGER DEFAULT 0, 'KBytesLeft' INTEGER DEFAULT 0, ) GO CREATE INDEX IX_Username ON Accounting(Username) MySQL will not take this command GO CREATE TABLE 'ActiveUsers'( 'UserID' VARCHAR(254) PRIMARY KEY, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Address' VARCHAR(128) NULL, 'LoginTime' DATETIME NULL, ) GO CREATE TABLE 'TAC_GRP'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_GRP(TAC_ID, TAC_Attr) MySQL will not take this command GO CREATE TABLE 'TAC_USR'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_USR(TAC_ID, TAC_Attr) MySQL will not take this command GO Thanks in advance. --- [This E-mail scanned for viruses by Friend.ly.net.] - 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
Select question
Hi, I have a database with 3 names in it. In each of these names is 5 categories that have numbers in them. I want to be able to do a select and get the top number from each category for each name and display them from most to least. I have checked all the docs on the select statement, but this is escaping me at the moment. TIA i.e This is what is in the database. Bill 3620504670 John2630324846 Fred8740196242 This is what I want to be able to show: Fred 87 Bill 70 John 48 SPAM bypass: sql, query, queries, smallint - 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: Select question
Hi again, After some struggling, I have managed to get the problem below 99% working, the problem now is that I can't get them in descending order. Here is my select statement. $query = select manager.name, position, MAX(goals) from roster join reference join manager where manager.idn=reference.idn and reference.idp=roster.idp and position like 'F' GROUP BY manager.name order by goals desc; Using the example below, this is what I get: Bill 70 John 48 Fred 87 This is what I want: Fred 87 Bill 70 John 48 TIA - Original Message - From: C. Reeve [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Friday, February 07, 2003 1:57 PM Subject: Select question Hi, I have a database with 3 names in it. In each of these names is 5 categories that have numbers in them. I want to be able to do a select and get the top number from each category for each name and display them from most to least. I have checked all the docs on the select statement, but this is escaping me at the moment. TIA i.e This is what is in the database. Bill 3620504670 John2630324846 Fred8740196242 This is what I want to be able to show: Fred 87 Bill 70 John 48 SPAM bypass: sql, query, queries, smallint - 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: Replication is out of sync? (auto)
Hi goth [EMAIL PROTECTED], I'm not sure if you have checked the manual yet, but following links seem to be somewhat related to your query: * http://www.mysql.com/doc/en/Commands_out_of_sync.html * http://www.mysql.com/doc/en/Replication.html * http://www.mysql.com/doc/en/Replication_HOWTO.html * http://www.mysql.com/doc/en/Replication_FAQ.html * http://www.mysql.com/doc/en/Replication_Problems.html This was an automated response to your email 'Replication is out of sync?'. Final search keyword used to query the manual was 'Replication out of sync'. Comming soon... * Support for mysql archives Feedbacks, suggestions and complaints should be directed to [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
query inserts from a table join
I don't know whetehr this is going to make any sense!
I have a query pulling records from MySQL tables that are joined. The join is
for a color_id to an item...
///
function showColor($item_id)
{
$query=select ref_colours.* from item_color ,ref_colours where
ref_colours.ColorID=item_color.color_id and item_color.item_id=$item_id;
$rs=mysql_query($query);
if(!$rs)
{
echo Error while exeuting the query;
}
else
{
while($row=mysql_fetch_array($rs))
{
$color_id=$row['ColorID'];
$cName=$row['ColorName'];
echo option value=\$color_id\$cName/option;
}
}
}
I guess without beating about the bush I just need to ask. What should the
value be set to for me to insert the color into a table along with the rest of
the chosen items details? this is the form:
echo SELECT name=\cName\ value=\$cName\;
showSize($II);
echo /SELECT ;
this is the add.php insert
mysql($DBName,INSERT INTO CartItems VALUES
('$UID','$ItemID','$ItemQuantity','$Date','$CartItemsID','$cName'));
but this is just inserting a value of 1 everytime :(
Thank you all for reading my problem :)
Andrew
-
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
various errors
Description: 1. executing scripts/mysql_install_db gave a ton of warnings like the following: 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN10field_real3st dEP6Stringm 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN10field_real8de cimalsEv 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN10field_real12c ollect_enumEv 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN14field_longlon g11get_min_argEP6String 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN14field_longlon g11get_max_argEP6String 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN14field_longlon g3avgEP6Stringm 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN14field_longlon g3stdEP6Stringm 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN14field_longlon g12collect_enumEv 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN15field_ulonglo ng11get_min_argEP6String 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN15field_ulonglo ng11get_max_argEP6String 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN15field_ulonglo ng3avgEP6Stringm 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN15field_ulonglo ng3stdEP6Stringm 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN15field_ulonglo ng12collect_enumEv 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN7analyseD1Ev 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN7analyseD0Ev 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN7analyse3addEv 19760:/usr/local/mysql/libexec/mysqld: /sbin/loader: Warning: Can't find strong symbol for _ZN7analyse9end_gr oupEv 030207 14:37:01 /usr/local/mysql/libexec/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: /usr/local/mysql/bin/mysqladmin -u root password 'new-password' /usr/local/mysql/bin/mysqladmin -u root -h newton password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd /usr/local/mysql ; /usr/local/mysql/bin/safe_mysqld You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; run-all-tests Please report any problems with the /usr/local/mysql/bin/mysqlbug script! 2. /usr/local/mysql/bin/mysqladmin -u root -h newton password 'new-password' gave the following: /usr/local/mysql/bin/mysqladmin: connect to server at 'newton' failed error: 'Host 'newton.physics.Arizona.EDU' is not allowed to connect to this MySQL server' The first command, /usr/local/mysql/bin/mysqladmin -u root password 'new-password', appeared to work - nothing was returned. 3. cd sql-bench ; run-all-tests returns the following failure: newton# ./run-all-tests Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib/perl5/5.6.1/alpha-dec_osf /usr/local/lib/perl5/5.6.1 /usr/local/lib/perl5/site_perl/5.6.1/alpha-dec_osf /usr/local/lib/perl5/site_perl/5.6.1 /usr/local/lib/perl5/site_perl/5.005/alpha-dec_osf /usr/local/lib/perl5/site_perl/5.005 /usr/local/lib/perl5/site_perl .) at ./run-all-tests line 36. BEGIN failed--compilation aborted at ./run-all-tests line 36. newton# How-To-Repeat: Fix: Submitter-Id: [EMAIL PROTECTED] Originator:Mr. Newton Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: need a lot of help Severity: Priority: Category: mysql Class: Release: mysql-3.23.55 (Source distribution) Environment: System: OSF1 newton V4.0 564 alpha Machine: alpha Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc /bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/alphaev5-dec-osf4.0b/3.0.3/specs Configured with: ../configure --enable-version-specific-runtime-libs --enable-languages=c,c++,f77,java --enable-libgcj --with-stabs Thread model: single gcc version 3.0.3 Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root system17 Jul 2 1997 /lib/libc.a - ../ccs/lib/libc.a lrwxrwxrwx 1 root system17 Jul 2 1997 /usr/lib/libc.a - ../ccs/lib/libc.a Configure command: ./configure '--prefix=/usr/local/mysql' Any help will be greatly appreciated, Regards, Philip Philip Goisman
Re: natural left join (auto)
Hi Varshavchick Alexander [EMAIL PROTECTED], I'm not sure if you have checked the manual yet, but following links seem to be somewhat related to your query: * http://www.mysql.com/doc/en/JOIN.html * http://www.mysql.com/doc/en/LEFT_JOIN_optimisation.html * http://www.mysql.com/doc/en/String_functions.html * http://www.mysql.com/doc/en/Bit_functions.html * http://www.mysql.com/doc/en/libmysqld_TODO.html This was an automated response to your email 'natural left join'. Final search keyword used to query the manual was 'natural left join'. Comming soon... * Support for mysql archives Feedbacks, suggestions and complaints should be directed to [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: CREATE TABLE, NOT NULL fields, and empty strings (auto)
Hi Doug Beyer [EMAIL PROTECTED], I'm not sure if you have checked the manual yet, but following links seem to be somewhat related to your query: * http://www.mysql.com/doc/en/Problems_with_NULL.html * http://www.mysql.com/doc/en/CREATE_TABLE.html * http://www.mysql.com/doc/en/SHOW.html * http://www.mysql.com/doc/en/SHOW_CREATE_TABLE.html * http://www.mysql.com/doc/en/ODBC_administrator.html This was an automated response to your email 'CREATE TABLE, NOT NULL fields, and empty strings'. Final search keyword used to query the manual was 'CREATE TABLE NOT NULL fields empty strings'. Comming soon... * Support for mysql archives Feedbacks, suggestions and complaints should be directed to [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: CREATE TABLE, NOT NULL fields, and empty strings
Doug Beyer wrote: create table t1 ( id varchar(5) not null, name varchar(5) not null ); insert into t1 ( id ) values ( 1234 ); select count(*) from t1 where name is null; -- Result = 0 select count(*) from t1 where name = ; -- Result = 1 Questions: 1) Why did the insert succeed since the name field is not null and I didn't provide a value? MySQL uses the default value of this column if you don't provide a value and the column is defined not null. 2) Why does MySql think it's correct to substitute an empty string for a non-provided value? http://www.mysql.com/doc/en/CREATE_TABLE.html : If no DEFAULT value is specified for a column, MySQL automatically assigns one. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type: * For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value (if you haven't explicitly specified another default value with the DEFAULT directive). Good night, Philipp - 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
DISTINCT LEFT JOIN
I¹m having problems with a left join. I¹m not even sure if I should be using a left join. I have two tables; sites and site_service. Site_service contains service dates for the sites. I¹m trying to write a query that will select each site and the last service date for that site. A site may have many service dates or none at all. Here¹s what I¹ve tried so far but it pulls down all of the service dates for a site if it has more then one and I only want the last date. SELECT DISTINCT site.id, site.site_id, site.name, site_service.service_date, site.service_order FROM site LEFT JOIN site_service ON site.id = site_service.site_id WHERE site.company = 'company' ORDER BY site_service.service_date DESC When I run the above I retrieve every service date for a given site if it has more then one. If I remove the site_service.service_date from the SELECT portion it retrieves only one site but I need at least the latest date so that doesn¹t help. Any help appreciated. Tom
Re: MySQL IMAP Server
Pretty sure dbmail can use mysql. www.dbmail.org -Erik --- Ben Clewett [EMAIL PROTECTED] wrote: Paul DuBois wrote: I believe the Horde IMP server uses MySQL: http://www.horde.org Thanks. Looking at this, I belive it's an IMAP clinet. Unless the web site is in error. I was rather trying to find an IMAP server using MySQL. Ie, somewhere to store my millions of email messages, from many execlent mailing lists, and get at them fast... EG, Mozilla Mail with message folders more than 10,000 email's, can (though a bug as well as bad structure) take more than a second per email to parse. Eg, marking as read. Where as an MySQL IMAP server would be thousands of times faster... If you hear of one, can you let me know? Cheers, Ben. Regards, Ben - 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!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.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: Is it my English or does this sound bad?
This thread is funny, When I first read the phrase in question it made perfect sense in the context in which it was written. Is the next change to the manual going to remove the example in chapter 6.4.5 UPDATE Syntax which refers to age; mysql UPDATE persondata SET age=age*2, age=age+1; Removed because it's ageist and therefore branded as non politically correct and subsequently binned? I hope not :) Andrew -Original Message- From: Richard E. Perlotto II [mailto:[EMAIL PROTECTED]] Sent: 06 February 2003 17:12 To: 'Paul DuBois'; 'Defryn, Guy'; [EMAIL PROTECTED] Subject: RE: Is it my English or does this sound bad? Why not just use the word gender. Richard -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 10:18 AM To: Defryn, Guy; [EMAIL PROTECTED] Subject: Re: Is it my English or does this sound bad? At 15:58 +1300 2/5/03, Defryn, Guy wrote: I was just reading through the Mysql manual section 3.32 And came across a sentence that started like this Animal sex can be represented in a variety of ways My mother tongue is not English but doesn't it sound rude ? :-) Yes, it does, and current versions of the manual are worded differently: Several types of values can be chosen to represent sex in animal records ... mysql CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), - species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); VARCHAR is a good choice for the name, owner, and species columns because the column values will vary in length. The lengths of those columns need not all be the same, and need not be 20. You can pick any length from 1 to 255, whatever seems most reasonable to you. (If you make a poor choice and it turns out later that you need a longer field, MySQL provides an ALTER TABLE statement.) Animal sex can be represented in a variety of ways, for example, m and f, or perhaps male and female. It's simplest to use the single characters m and f. Query/mysql - 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
NEWBIE to mysql
Can anyone tell me why I keep getting an ERROR 1064 with this command ? I just would like to create a table with two fields, name and messages for a Simple guestbook but I want to store the data in a mysql database. mysql CREATE TABLE info - ( - name varchar(50) - message varchar(255) - ) - ; thanks, -wiley - 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
NEWBIE to mysql
Can anyone tell me why I keep getting an ERROR 1064 with this command ? I just would like to create a table with two fields, name and messages for a Simple guestbook but I want to store the data in a mysql database. mysql CREATE TABLE info - ( - name varchar(50) - message varchar(255) - ) - ; thanks, -wiley - 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
follow up to file per field concept
context ... I had proposed writing a new backend for mysql tailored to read only very large and very wide (lots of columns) tables (14G + ) in which queries never involved more than a small percentage of the available columns (i.e. 5%) and often lacked WHERE clauses. A couple years ago I argued against the file per field disk layout on the belief that when the # and size of these files were too large that head movement back and forth would become the dominant time consumer and efficiency would drop. In practice though we were used to seeing the file per field theory perform roughly 10 times faster than an equivalent SQL database. Recently I became aware that on our largest table, detailed natality, query times had gone up radically half a year ago and the individual in charge had failed to determine what had gone wrong. I now believe that the performance was wrecked when the hard drive was defragmented. Originally the file per field files were written out en masse by a java program, but that would have left the resultant files fragmented, but fragmented in such a way that the head movement was minimized during a query. When the hard drive was defragged suddenly jumping back and forth between the different column files forced excessive head movement and performance went south. I still believe this technology has a lot to offer, but now am proposing that the columns be written as a single file and with a pseudo internal fragmentation. For col a, b and c, .. a1, a2, a3... aN, b1, b2, b3 ... bN, c1, c2, c3, ... CN aN+1, ..., bN+1, ... CN+1 ... and then we have the factor of 10 improvement over the usual sql table layout for the very particular query pattern that this is designed to solve. (note ... N needs to be determined after studying OS/controller/harddrive functions to minimize head seek) ANYWAY, wanted to make sure this hit the list before someone tried to experiment with a file per field approach. -- Heitzso [EMAIL PROTECTED] MetaMedia, Inc. - 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: SQLyog 3.02 Released (auto)
Hi Insanely Great [EMAIL PROTECTED], I'm not sure if you have checked the manual yet, but following links seem to be somewhat related to your query: * http://www.mysql.com/doc/en/Many_versions.html This was an automated response to your email 'SQLyog 3.02 Released'. Final search keyword used to query the manual was 'SQLyog 3.02 Released'. Comming soon... * Support for mysql archives Feedbacks, suggestions and complaints should be directed to [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: converting MS SQL to MySQL
On Thu, Feb 06, 2003 at 10:03:18AM -0500, Hawk wrote: Hi, I am new to SQL. I am attempting to create a database that was created in MS SQL to MySQL. The below SQL statements works with MS SQL. MS SQL could refer to either Jet SQL or SQL Server SQL, but the SQL below is obviously SQL Server. So the database was created with SQL Server. The GO statements are not SQL. They are used by SQL Server utilities to mark the end of a batch. I don't think they're used by anything other than MS SQL Server utilities and the equivalent Sybase utilites. But regardless, GO isn't used by any MySQL utilities. Every DBMS uses its own dialect of SQL. When you switch from one DBMS (SQL Server) to another (MySQL), you will have to look up the syntax of the statements that don't run and learn how to rewrite them in the new SQL dialect. The best place to start looking for info is the on-line manual at www.mysql.com. Good luck, and have fun. Bob Hall I am executing the following: CREATE TABLE 'Accounting'( 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Addr' VARCHAR(128) NULL, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Start' DATETIME NULL, 'Stop' DATETIME NULL, 'SessionTime' INTEGER DEFAULT 0, 'ExtraTime' INTEGER DEFAULT 0, 'TimeLeft' INTEGER DEFAULT 0, 'KBytesIn' INTEGER DEFAULT 0, 'KBytesOut' INTEGER DEFAULT 0, 'SessionKB' INTEGER DEFAULT 0, 'ExtraKB' INTEGER DEFAULT 0, 'KBytesLeft' INTEGER DEFAULT 0, ) GO CREATE INDEX IX_Username ON Accounting(Username) MySQL will not take this command GO CREATE TABLE 'ActiveUsers'( 'UserID' VARCHAR(254) PRIMARY KEY, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Address' VARCHAR(128) NULL, 'LoginTime' DATETIME NULL, ) GO CREATE TABLE 'TAC_GRP'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_GRP(TAC_ID, TAC_Attr) MySQL will not take this command GO CREATE TABLE 'TAC_USR'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_USR(TAC_ID, TAC_Attr) MySQL will not take this command GO Thanks in advance. --- [This E-mail scanned for viruses by Friend.ly.net.] - 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
Help. Not sure my other email got through!
Not sure my other email got through, so here it is again, with an updated description of the problem. Hi, I have a database with 3 names in it. In each of these names is 5 categories that have numbers in them. I want to be able to do a select and total these numbers for each name, and then sort them in descending order. I have tried for over 6 hours tonight and no matter what I do, there is one minor glitch. I am not supplying any code as I would like to know from you people first what the best way to go about this would be - as what I have done is obviously not working. TIA i.e This is what is in the database. Table 1 ID1 NAME 1 Bill 2 John 3 Fred Table 2 ID2 Col1 Col2 Col3 Col4 1 1456 2 3286 3 4922 Database 3 is a reference file that ties them together - so I would have to use a join in the select statement. ID1 ID2 11 22 33 This is what I want to be able to show: John 21 Fred 20 Bill 16 sql, query, queries, smallint - 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: Replication error (auto)
Hi Oliver Schlag [EMAIL PROTECTED], I'm not sure if you have checked the manual yet, but following links seem to be somewhat related to your query: * http://www.mysql.com/doc/en/Replication_FAQ.html * http://www.mysql.com/doc/en/Replication.html * http://www.mysql.com/doc/en/Replication_HOWTO.html * http://www.mysql.com/doc/en/Replication_SQL.html * http://www.mysql.com/doc/en/Replication_Options.html This was an automated response to your email 'Replication error'. Final search keyword used to query the manual was 'Replication error'. Comming soon... * Support for mysql archives Feedbacks, suggestions and complaints should be directed to [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: support question (win98_se)
Dino, to double click or run mysql.exe from the MS dos prompt. I get the following message. C:\mysql\binmysql ERROR 2003: Can't connect to Mysql server on 'localhost' (10061) Most probably your MySQL server isn't running. Using Windows Explorer or a DOS window, cd to c:\mysql\bin and start winmysqladmin.exe. This should start the server, and you will see a traffic light symbol in the system tray. The light should be green. If this fails, cd to c:\mysql\bin in a DOS window and enter: mysqld --console --standalone This will show you any startup errors that encounter, so you can track down the problem. When the MySQL server is up, try mysql.exe again. This should give you an SQL prompt. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - 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: CREATE TABLE, NOT NULL fields, and empty strings
Doug, create table t1 ( id varchar(5) not null, name varchar(5) not null ); insert into t1 ( id ) values ( 1234 ); select count(*) from t1 where name = ;-- Result = 1 1) Why did the insert succeed since the name field is not null and I didn't provide a value? 2) Why does MySql think it's correct to substitute an empty string for a non-provided value? It's in the documentation, although I found no better explanation than this one: NULLs must be indicated as NULL values, not left blank. (User comment) MySQL always has a default value for all fields. This is something that is imposed on MySQL to be able to work with both transactional and not transactional tables. (Manual text) http://www.mysql.com/doc/en/INSERT.html Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - 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: Problem with LEFT JOIN
Lisi, First, limiting the clicks: You didn't have any condition on click.date in your suggested queries. You need to have one. Here's my suggestion, somewhat modified. Note that the limit on click.date goes in the ON clause, while the limit on display.date goes in the WHERE clause. Looks wierd until you think about the meaning of left join. The left join gives you all displays, together with clicks for the day you want and nulls for displays with no clicks. The where clause then throws away displays for the wrong days. SELECT ... FROM display LEFT JOIN click ON display.name=click.name AND ... AND DAYOFMONTH(click.date) = '19' WHERE ... AND DAYOFMONTH(display.date) = '19'; Now, about the counts. The above will give you a (display, click) pair for every display and click with the same name, plus a (display, null) for the displays without clicks. Summing counts of the (display, click) pairs is going to give you results that are wrong, as you found out. Can you do it in one query? Probably not, until MySQL gets subqueries. Why not use a temporary table? CREATE TEMPORARY TABLE dcounts SELECT display.name, SUM(display.count) as dc WHERE ... AND DAYOFMONTH(display.date) = '19' GROUP BY display.name; this gives you a table of displays and counts. Then use a LEFT JOIN to combine it with click: SELECT dcounts.name, dc, SUM(clicks.date IS NOT NULL) FROM dcounts LEFT JOIN click ON dcounts.name=click.name AND ... AND DAYOFMONTH(click.date) = '19' Note: If you are replicating, you may want to create the temporary tables in a second database and use binlog-ignore-db to avoid cluttering up the logs and to avoid having the slave get confused by having the same table created in multiple threads on the server. Subqueries will sure make life simpler. About my comment on indenting. Yes, you cut and pasted from a generated query. What I asked is that, before sending the message, you insert some new lines and some indentation. You're asking for free help, and you're more likely to get it if people don't have to spend time figuring out what the question is. - Original Message - From: Lisi [EMAIL PROTECTED] To: Bill Easton [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, February 03, 2003 3:37 AM Subject: Re: Problem with LEFT JOIN OK, I'm coming back to this problem after not touching it for a while. Sorry about the indentation, it's a dynamically generated query and I just cut and pasted into the email. I tried your suggestion, and it is showing the correct number of displays for most ads, but it is not limiting the clicks - it's displaying all clicks even if there were none for that day. Plus, for one ad, it is showing the two clicks (on a different day) it got plus it doubled the number of displays it had for that day - i.e. it is multiplying the 13 displays by the 2 clicks! Huh? Is this just too complicated to do with one query? Should I perhaps use different queries, and store the info somehow in an array by name, and display it that way? I'm really stumped here. Thanks, -Lisi At 07:31 AM 1/20/03 -0500, Bill Easton wrote: Shalom, Lisi, (Sure would have been nice if you had indented and simplified your SQL statements so they could be read by a mere human instead of just by a computer ;-}) You have SELECT ... FROM display LEFT JOIN click ON display.name=click.name AND ... AND DAYOFMONTH(display.date) = '19'; The result of this select consists of the following: (1) The result of the following inner join: SELECT ... FROM display. click WHERE display.name=click.name AND ... AND DAYOFMONTH(display.date) = '19'; (2) For each row of display that did not get used in (1), that row together with nulls for all columns of click. In short, you get at least one row for each row of display--the ON clause only affects which ones that have data from click. That's how LEFT JOIN works. So, in particular, you get data for the whole month. You probably want something like: SELECT ... FROM display LEFT JOIN click ON display.name=click.name WHERE ... AND DAYOFMONTH(display.date) = '19'; The left join will give you rows with data from both tables and rows from display that don't have data in click; the where clause will then narrow the selection to the day you want. Hope this helps Date: Sun, 19 Jan 2003 19:02:25 +0200 To: [EMAIL PROTECTED] From: Lisi [EMAIL PROTECTED] Subject: Problem with LEFT JOIN I have a page with many ads that stores both the number of times an ad is displayed and how many times it gets clicked. These are stored in two different tables (since different information is stored for each) but both have identical name columns. I am trying to display both # times displayed and # times clicked in the same table in an admin
Re: converting MS SQL to MySQL
Hawk [EMAIL PROTECTED], Do you think the following links may help? * http://www.mysql.com/doc/en/Using_mSQL_tools.html * http://www.mysql.com/doc/en/Using_InnoDB_tables.html This was an automated response to your email 'converting MS SQL to MySQL'. Final search keyword used to query the manual was 'converting MS SQL to'. Feedbacks, suggestions and complaints about me should be directed to [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: converting MS SQL to MySQL
Take look at http://www.mysql.com/doc/en/CREATE_TABLE.html [EMAIL PROTECTED] wrote: Hi, I am new to SQL. I am attempting to create a database that was created in MS SQL to MySQL. The below SQL statements works with MS SQL. I am executing the following: CREATE TABLE 'Accounting'( 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Addr' VARCHAR(128) NULL, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Start' DATETIME NULL, 'Stop' DATETIME NULL, 'SessionTime' INTEGER DEFAULT 0, 'ExtraTime' INTEGER DEFAULT 0, 'TimeLeft' INTEGER DEFAULT 0, 'KBytesIn' INTEGER DEFAULT 0, 'KBytesOut' INTEGER DEFAULT 0, 'SessionKB' INTEGER DEFAULT 0, 'ExtraKB' INTEGER DEFAULT 0, 'KBytesLeft' INTEGER DEFAULT 0, ) GO CREATE INDEX IX_Username ON Accounting(Username) MySQL will not take this command GO CREATE TABLE 'ActiveUsers'( 'UserID' VARCHAR(254) PRIMARY KEY, 'NAS' VARCHAR(128) NULL, 'Port' VARCHAR(128) NULL, 'Username' VARCHAR(254) NULL, 'CallerID' VARCHAR(128) NULL, 'Address' VARCHAR(128) NULL, 'LoginTime' DATETIME NULL, ) GO CREATE TABLE 'TAC_GRP'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_GRP(TAC_ID, TAC_Attr) MySQL will not take this command GO CREATE TABLE 'TAC_USR'( 'TAC_ID' VARCHAR(254) NOT NULL, 'TAC_Attr' VARCHAR(64) NOT NULL, 'TAC_Val' TEXT NULL ) GO CREATE UNIQUE INDEX IX_TAC_ID ON TAC_USR(TAC_ID, TAC_Attr) MySQL will not take this command GO Thanks in advance. --- [This E-mail scanned for viruses by Friend.ly.net.] - 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 -- Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with Shop@Netscape! http://shopnow.netscape.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: Sorting with null values
On Thursday 06 February 2003 17:05, Octavian Rasnita wrote: Please tell me how can I sort a column and force placing the null values to the end of list? Something like: SELECT ... ORDER BY IF(column_name IS NULL, 1, 0), column_name; -- 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
sort problem
Hi, How can I fix an order by using numbers and letters ? Id Town 56 Paris 1 60 Paris 10 7 Paris 11 262 Paris 12 8 Paris 13 16 Paris 14 22 Paris 15 6 Paris 3 57 Paris 4 51 Paris 6 5 Paris 7 61 Paris 8 59 Paris 9 I'd like to get : 56 Paris 1 6 Paris 3 57 Paris 4 51 Paris 6 5 Paris 7 61 Paris 8 59 Paris 9 60 Paris 10 7 Paris 11 262 Paris 12 8 Paris 13 16 Paris 14 22 Paris 15 A this time I'm doing this sql syntax : SELECT * FROM town ORDER BY town ASC How can I do it ? - 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 foreign keys bug
Scott Wong [EMAIL PROTECTED], Do you think the following links may help? * http://www.mysql.com/doc/en/example-Foreign_keys.html * http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html * http://www.mysql.com/doc/en/ALTER_TABLE.html * http://www.mysql.com/doc/en/Using_InnoDB_tables.html * http://www.mysql.com/doc/en/Indexes.html This was an automated response to your email 'InnoDB foreign keys bug'. Final search keyword used to query the manual was 'InnoDB foreign keys bug'. Feedbacks, suggestions and complaints about me should be directed to [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
