RE: [PHP] Performance [Slightly OT]
Hi Markus, I've only seen the last couple messages from this thread, but to my knowledge PHP doesn't close the MySQL connection at the end of each block. It will close the connection automatically once the entire PHP script has finished processing, though, if you haven't. I prefer not to intermingle my HTML code with PHP code, but have not found any performance problems if you do. But in any event, here are a couple of options if you choose not to switch from PHP code to HTML and then back again via the ? ? syntax: 1. Use the here syntax: print EOS HTML TITLE$PHP_SELF/TITLE /HTML EOS; 2. Just create your HTML string in PHP and then output it: $html_string = HTML TITLE$PHP_SELF/TITLE /HTML; print $html_string; If I'm missing the point or have oversimplied it, my apologies. Best regards, -Joe -Original Message- From: markus|lervik [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 13, 2001 1:30 PM To: [EMAIL PROTECTED] Subject: RE: [PHP] Performance On Thu, 2001-12-13 at 22:16, Ron Jamison wrote: I'm fairly sure there's no difference in performance when using PHP tags mixed with HTML blocks as you are doing. Think of it as PHP knowing that it should send everything not in ? ? tags directly to the browser. Like an echo or a print, but without needing to specify calls to those functions. HTML ? echo $PHP_SELF ? /HTML HTML ?=$PHP_SELF? /HTML I'm not -that- worried about performance loss for switching between, php and html, what annoys me (and would probably slow down performance quite a bit), is that every time I have to do a database query, I have to use mysql_connect again, because as Prottoss pointed out, PHP drops the connection every time a PHP-block ends. Not that it is that a big issue for my particular application, but knowing a workaround to this problem would be quite useful in the future. Cheers, Markus -- Markus Lervik Linux administrator with a kungfoo grip Vasa City Library - Public Library - 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: Question
Hi, http://www.mysql.com/doc/m/y/mysqladmin.html and http://www.mysql.com/doc/C/o/Connecting.html are a good start. If SSHing into a Unix box, you can also use man mysqladmin and man mysql, and man mysqldump for more info. -Joe -Original Message- From: Scott Johnson [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 09, 2001 4:06 PM To: [EMAIL PROTECTED] Subject: Question Hello, Does anyone know where I should look on the http://www.mysql.com web site or anywhere else I can find documentation for being able to SSH to the box and setup additional databases as necessary using the MySQL command line tools? Scott 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 - 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: running MySQL on WinXP
Sounds like you need to add permission to connect from localhost. You might want to check out: http://www.mysql.com/doc/S/e/Secure_GRANT.html or you might need to use the -u root option to mysql -Original Message- From: Curt Kliewer [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 09, 2001 3:49 PM To: G r e g L a w r i e Cc: MySQL List Subject: Re: running MySQL on WinXP I have installed the necessary MySQL files onto the hard drive (under the folders inetpub -- wwwroot -- mysql. Next, I set up a cnf file and called it my.cnf with the path C:\Inetpub\wwwroot\mysql in it. Then, using the command prompt, I keyed in the path to the mysql directory and keyed in mysql.exe. The error I get is: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I've tried this with my firewall (giving permission to MySQL and without it. Both have resulted in the same error message. Any ideas? -- Best regards, Curt Kliewer ** ** Sol Interactive Development Applications Programmer/Analyst Internet Solutions/Server-Side Programmer ** ** Contact Information: ICQ:1835597 MSN Messenger: [EMAIL PROTECTED] Email:[EMAIL PROTECTED] Website: http://www.sol-interactive.com ** ** - Original Message - From: G r e g L a w r i e To: Curt Kliewer ; MySQL List Sent: Sunday, December 09, 2001 1:04 PM Subject: RE: running MySQL on WinXP Haven't tried it myself, but can't see why it shouldn't. How about you try telling us the process you have been through so far and any messages / errors that you may be getting. -Original Message- From: Curt Kliewer [mailto:[EMAIL PROTECTED]] Sent: Monday, 10 December 2001 5:16 To: MySQL List Subject: running MySQL on WinXP Hi. I am new to PHP/MySQL and this list. Can I run MySQL on WinXP? I haven't been able to get it to work so far. Any help would be appreciated. -- Best regards, Curt Kliewer ** ** Sol Interactive Development Applications Programmer/Analyst Internet Solutions/Server-Side Programmer ** ** Contact Information: ICQ:1835597 MSN Messenger: [EMAIL PROTECTED] Email:[EMAIL PROTECTED] Website: http://www.sol-interactive.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: Hacked Servers
Hi there, I feel for you. Been there myself before I installed a firewall router. (Very useful, BTW, to restrict access to only your customers IPs instead of the whole world.) There isn't really enough info provided here to know for sure, but it might be that when you were hacked, your inetd.conf file was hacked as well closing services to the world. Maybe even your MySQL user/hosts tables, so check permissions there as well. Some rootkits will restrict access after they have hacked a machine to make sure that their hack doesn't get overwritten by some other hacker. This can also lock you out of the machine except from the direct console. So you might want to check your inetd configuration and hit http://www.sans.org, http://www.incidents.org, and http://www.dshield.org for more info. Things to search for might be t0rn rootkit, ramen worm, l0in, etc. Here's a link about the ramen worm to get you started. http://www.sans.org/y2k/ramen.htm It also contains a link to a script to detect the worm on your server. Of course, after you get things a little more under control, you'll need to reinstall OS, change passwords, etc. You might also want to post the exploit via http://www.incidents.org. Best of luck! -Joe -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 10:26 AM To: [EMAIL PROTECTED] Subject: Hacked Servers Hi We have 2 Redhat 6.1 servers and MySQL 3.22.32 and both boxes appear to have been hacked on Friday last and MYSQL client just hangs when connecting to the localhost MYSQL server. MySQL is running on both boxes and suffer the same problems. We also have to use kill -9 pid number to kill the server(s). No MySQL client can connect remotely to either of these machines however the local MySQL client on the hacked server(s) can connect to other remote MySQL servers. We have re-installed MySQL server on this hacked server and still the client just hangs and no errors in the logs appear. We have Intrusion software but its very long winded trying to find how to fix it - and ultimately we will re-install. (but first I have 600 clients per server to please!) Please HELP we and all our tech guys are stumped. Any more info please ask. Kind regards Tony - 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: problems loading a datafile into a database table
Hi Roger, You might just need to set File_priv='Y' in the user table in the mysql database for the user that you logged into MySQL as. Don't forget to do a flush privileges; after you make the change so that it takes affect. -Joe -Original Message- From: Greenlaw, Roger [mailto:[EMAIL PROTECTED]] Sent: Friday, November 16, 2001 2:49 PM To: '[EMAIL PROTECTED]' Subject: problems loading a datafile into a database table I have a database defined, and a data file that needs to be imported into the table. I'm using Red Hat Linux 7.0 as my operating system, and mysql server is loaded and running on linux. When I type LOAD DATA INFILE '/home/usrename/datafile.ext' ; I receive the following error: Error 13: Can't get stat of '/home/usrename/datafile.ext' (Errcode: 13) perror 13 reports Error code 13: Permission denied I tried changing my file using chmod 777 datafile.ext and confirmed that I am the file's owner. Can anyone help me? Thanks, Roger Greenlaw, Systems Analyst Sebasticook Valley Hospital 99 Grove Street Pittsfield ME 04967 (207) 487-5141 Ext. 213 - 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: Load data infile
Hi, your test.csv file has some flaws in records 5 and 7 because the OPTIONALLY ENCLOSED BY char= isn't matched for each column. Changing those offending CSV lines to be instead something like: Record 5,\some\data\,Fails Record 7,some data\,Fails and then modifying your LOAD to be something like: LOAD DATA INFILE '/home/sql/test.csv' INTO TABLE quotetest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '\\' LINES TERMINATED BY '\n'; should do it. The reason you use the ENCLOSED BY clause is to take into account data like: id,Name,phone 12,Last,First,555-555- Since the 2nd column actually contains a comma you need to use OPTIONALLY ENCLOSED BY to contain it like: id,Name,phone 12,Last,First,555-555- If the s aren't matched LOAD gets confused as to what delemits a column. -Joe -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 12:48 PM To: [EMAIL PROTECTED] Subject: Re: Load data infile Hi I didn't see any difference between using optionally or not. Any solutions to make the two that fails work? (see test case below) Using 3.23.41. Thanks test.csv: Record 1,some data,Works Record 2,some data,Works Record 3,some 'data',Works Record 4,some data,Works Record 5,somedata,Fails Record 6,somedata,Works Record 7,some data,Fails Record 8,'some'data,Works Record 9,somedata,Works CREATE TABLE quotetest ( name text, content text, description text ); mysql LOAD DATA INFILE '/home/sql/test.csv' INTO TABLE quotetest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' LINES TERMINATED BY '\n'; Query OK, 9 rows affected (0.00 sec) Records: 9 Deleted: 0 Skipped: 0 Warnings: 2 mysql select * from quotetest; +--++-+ | name | content| description | +--++-+ | Record 1 | some data | Works | | Record 2 | some data| Works | | Record 3 | some 'data'| Works | | Record 4 | some data | Works | | Record 5 | somedata,Fails | NULL| | Record 6 | somedata | Works | | Record 7 | some data,Fails | NULL| | Record 8 | 'some'data | Works | | Record 9 | somedata | Works | +--++-+ 9 rows in set (0.00 sec) - 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: Help On Load Data File
Hi Calvin, Check out http://www.mysql.com/doc/L/O/LOAD_DATA.html and search for Warnings. A paragraph probably of particular interest to you is: Warnings occur under the same circumstances as when values are inserted via the INSERT statement (see section 6.4.3 INSERT Syntax), except that LOAD DATA INFILE also generates warnings when there are too few or too many fields in the input row. The warnings are not stored anywhere; the number of warnings can only be used as an indication if everything went well. If you get warnings and want to know exactly why you got them, one way to do this is to use SELECT ... INTO OUTFILE into another file and compare this to your original input file. -Original Message- From: Calvin Chin [mailto:[EMAIL PROTECTED]] Sent: Sunday, October 28, 2001 9:08 PM To: [EMAIL PROTECTED] Subject: Help On Load Data File Importance: High Hi list member, I have a slight problem here. I am testing on the data convertion from text file into MySQL table. I am able to use the 'load data infile' command and insert the data into the table, however, with 1000 warnings. I don't know where I can see the warning messages ? Can you people help me to identify where MySQL store the warning / error messages generated? Thank you. Regards, Calvin Chin [EMAIL PROTECTED] Ipmuda Berhad - Building materials for a better world ! - 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 config problems
Hey Tim, I've run into this before. The docs say that if you don't have the grant tables set up you'll get that error: http://www.mysql.com/doc/P/o/Post-installation.html (search for host.frm in page) But I've also seen it when I tried copying the support_files/mysql.server to the /etc/rc.d/init.d directory and then issuing the command: /etc/rc.d/init.d/mysql.server start even when user accounts were all fine. If nothing else, you might try running the command, safe_mysqld as root from your mysql/bin directory. If that works for you, you can add that command to your /etc/rc.d/rc.local file to automatically start MySQL at boot time. Good luck! -Joe -Original Message- From: Tim Veazey [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 17, 2001 3:02 PM To: [EMAIL PROTECTED] Subject: mysql config problems Hello, I'm new to mysql on a redhat linux 7.1, AMD Athlon system. I'm having some configuration problems with the mysql that redhat supplies out of the box. The latest error states... 011017 09:25:53 mysqld started 011017 9:25:53 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 011017 09:25:53 mysqld ended Is anyone familiar with this? Thanks in advance. -Tim - 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: Can one SELECT records depending on multiple entries in a many to many table?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Benjamin Hi. On Mon, Oct 01, 2001 at 07:54:37PM -0700, [EMAIL PROTECTED] wrote: Hi there, I have a many-to-many relationship: ind table - id = 4, name = Joe id = 5, name = Jane bus_ctr table - id = 1, name = downtown mall id = 2, name = sunshine mall id = 3, name = express shop mm_ind_bus_ctr table (many to many association table) - id = 6, ind_id = 4, bus_ctr_id = 1 id = 7, ind_id = 4, bus_ctr_id = 2 id = 8, ind_id = 4, bus_ctr_id = 3 id = 9, ind_id = 5, bus_ctr_id = 1 Is there a way to create a SELECT that returns only the people in the ind table that are associated with bus_ctr.id=1, bus_ctr.id=2 and bus_ctr.id=3? In other words a SELECT that only returns the ind.id = 4 (Joe) record? Do you want an exact match or an at least match, i.e. what if id = 10, ind_id = 4, bus_ctr_id = 5 would be given. Does Joe still match? I'm looking for an exact match, so if that record also existed in mm_ind_bus_ctr, Joe should not match. I've only been able to create a SELECT that would return both Jane and Joe so far. If you have something, please post it, even if it isn't correct. It will give other a better idea of what's all about (if you also post the result and what you don't like about it). I was going at it from the wrong direction as this provides the at least match. SELECT DISTINCT i.id, i.name FROM ind i, mm_ind_bus_ctr mm WHERE mm.bus_ctr_id IN (1,2,3) AND i.id=mm.ind_id; The select will get a bit large, because you need several joins. In fact, you want to check for three relations and therefore needs as much tables to join with: SELECTi.id, i.name FROM ind i, mm_ind_bus_ctr bi1, mm_ind_bus_ctr bi2, mm_ind_bus_ctr bi3 WHERE bi1.bus_ctr_id = 1 AND bi2.bus_ctr_id = 2 AND bi3.bus_ctr_id = 3 AND bi1.ind_id = i.id AND bi2.ind_id = i.id AND bi3.ind_id = i.id Untested, but should work. It does work and is exactly what I was hoping for. I was missing the multiple alias for a single table technique. Bye, Benjamin. Thank you, Benjamin. I really appreciate your time and help! -Joe - 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: sql syntax INSERT
You probably want a unique index for those cols. Check out: http://www.mysql.com/doc/C/R/CREATE_TABLE.html (search for unique in page) and http://www.mysql.com/doc/C/R/CREATE_INDEX.html -Joe -Original Message- From: Robert Martin [mailto:[EMAIL PROTECTED]] Sent: Monday, October 01, 2001 12:50 PM To: [EMAIL PROTECTED] Subject: sql syntax INSERT Hi, Im still learning sql so I hope this doesnt sound to basic. I would like to find out if there is a way to insert a record only when (X and Y) do not exist. I have the value to check against stored in a variable. My table looks like the following: ID | X | Y - 1 | 10 | 20 2 | 11 | 21 ID is an auto-increment field, X and Y are INT(4). The syntax Im looking for is sort of like this: Success: INSERT INTO table_name (X, Y) VALUES (10,23) WHERE (X myX) AND (Y myY) Fail: INSERT INTO table_name (X, Y) VALUES (10,20) WHERE (X myX) AND (Y myY) I know this wont work, but Im trying to avoid the overhead of running a query and checking the result before each INSERT. Would a compound primary key help? (To be honest I only have a slight idea of what one is.) Can one be created in MySQL? Thanks in advance, Robert Martin - 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
Can one SELECT records depending on multiple entries in a many to many table?
Hi there, I have a many-to-many relationship: ind table - id = 4, name = Joe id = 5, name = Jane bus_ctr table - id = 1, name = downtown mall id = 2, name = sunshine mall id = 3, name = express shop mm_ind_bus_ctr table (many to many association table) - id = 6, ind_id = 4, bus_ctr_id = 1 id = 7, ind_id = 4, bus_ctr_id = 2 id = 8, ind_id = 4, bus_ctr_id = 3 id = 9, ind_id = 5, bus_ctr_id = 1 Is there a way to create a SELECT that returns only the people in the ind table that are associated with bus_ctr.id=1, bus_ctr.id=2 and bus_ctr.id=3? In other words a SELECT that only returns the ind.id = 4 (Joe) record? I've only been able to create a SELECT that would return both Jane and Joe so far. Thanks, Joe - 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
why would LOWER
Hi there, Is it expected that using the LOWER function should greatly increase query time? Or is there a MySQL setting that can help speed it up? Below are the results of a query against a table that has an index on cols (email,cust). Using LOWER increases the query time by 10.5 seconds. (eek!) I've fixed the scripts that input data into the ind table so that emails are now first converted to lower case before being inserted, but was curious if doing some data mining to fix the existing emails is the only (probably best) solution. Thanks, Joe SELECT t.id, t.Name, t.city FROM ind t WHERE t.cust=1 AND t.email='[EMAIL PROTECTED]'; 1 row in set (0.01 sec) SELECT t.id, t.Name, t.city FROM ind t WHERE t.cust=1 AND LOWER(t.email)='[EMAIL PROTECTED]'; 1 row in set (10.52 sec) - 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: why would LOWER
The table has less than 200,000 records, but the table has quite a few columns and there is an index on ind for the columns (cust,email). MySQL 3.23.36 is running on a Sparc with 1 gig RAM. The ind table type is MyISAM and the cust field is an INT and the email field is a VARCHAR. I was wondering if something like that may be happening. Thanks for the comments! -Joe -Original Message- From: Will French [mailto:[EMAIL PROTECTED]] Sent: Sunday, September 30, 2001 1:25 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: why would LOWER You didn't mention how large your table is or if the email and cust fields are part of an index. In many situations, the results you are seeing make perfect sense to me. If email is a part of an index then the LOWER function may need to be performed on each value of email in the index before the comparison can be performed. Additionally you may be negating the effect of a hashing algorithm. I can not say with certainty how MySQL does indexed lookups, but many db engines start by narrowing down a search using a hash of the searched for value and looking that up in a hash table for the index. If the character set you are using is case sensitive (and I assume it is or you wouldn't need the LOWER), then the hashing algorithm would also be case sensitive. Will French -Original Message- From: Joe Kaiping [mailto:[EMAIL PROTECTED]] Sent: Sunday, September 30, 2001 4:14 PM To: [EMAIL PROTECTED] Subject: why would LOWER Hi there, Is it expected that using the LOWER function should greatly increase query time? Or is there a MySQL setting that can help speed it up? Below are the results of a query against a table that has an index on cols (email,cust). Using LOWER increases the query time by 10.5 seconds. (eek!) I've fixed the scripts that input data into the ind table so that emails are now first converted to lower case before being inserted, but was curious if doing some data mining to fix the existing emails is the only (probably best) solution. Thanks, Joe SELECT t.id, t.Name, t.city FROM ind t WHERE t.cust=1 AND t.email='[EMAIL PROTECTED]'; 1 row in set (0.01 sec) SELECT t.id, t.Name, t.city FROM ind t WHERE t.cust=1 AND LOWER(t.email)='[EMAIL PROTECTED]'; 1 row in set (10.52 sec) - 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: why would LOWER
Thanks to all for your responses. I had missed this important piece of information in the documentation that Benjamin pointed to and: http://www.mysql.com/doc/C/H/CHAR.html Values in CHAR and VARCHAR columns are sorted and compared in case-insensitive fashion, unless the BINARY attribute was specified when the table was created. The BINARY attribute means that column values are sorted and compared in case-sensitive fashion according to the ASCII order of the machine where the MySQL server is running. BINARY doesn't affect how the column is stored or retrieved. -Joe -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Benjamin Pflugmann Sent: Sunday, September 30, 2001 1:55 PM To: Joe Kaiping Cc: [EMAIL PROTECTED] Subject: Re: why would LOWER Hi. The problem is that LOWER(email) is an expression and expressions on the left hand side of an comparison cannot use an index with MySQL (see also http://www.mysql.com/doc/M/y/MySQL_indexes.html). If you usually want to compare emails ignoring case, an easier way is to assure that email is not of type BINARY, i.e. VARCHAR(x) instead of VARCHAR(x) BINARY. Without BINARY, the comparisons are case insensitive anyhow, so no need for LOWER(). Bye, Benjamin. On Sun, Sep 30, 2001 at 01:36:50PM -0700, [EMAIL PROTECTED] wrote: [...] Is it expected that using the LOWER function should greatly increase query time? Or is there a MySQL setting that can help speed it up? Below are the results of a query against a table that has an index on cols (email,cust). Using LOWER increases the query time by 10.5 seconds. (eek!) I've fixed the scripts that input data into the ind table so that emails are now first converted to lower case before being inserted, but was curious if doing some data mining to fix the existing emails is the only (probably best) solution. [...] SELECT t.id, t.Name, t.city FROM ind t WHERE t.cust=1 AND LOWER(t.email)='[EMAIL PROTECTED]'; 1 row in set (10.52 sec) [...] -- [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 - 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
ERD modeler and schema generator/modifier
Hi list, Can anyone recommend an ERD modeler for MySQL? I've tested the evaluation version of Dezign for databases and while I like it and was able to generate an initial schema with it, I couldn't get it to create SQL to alter the schema after I had modified the initial ERD. (I was only able to generate SQL that created a fresh new schema.) I sent an email to Datanamic 2-3 weeks ago asking about it, but have received no response. This makes me a little nervous about buying the product even if it is able to maintain a MySQL schema. Anyone have any experiences, good or bad, in using it? Or can someone recommend another modeler that might be out there that you have used and are content with? I'd be happy using one that ran on either Linux or Windows. Anyone tried using Silverrun? Can it work with MySQL? Thanks for any comments, -Joe - 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
Can't get MySQL to use available memory (performance very slow)
Hi there, I'm having trouble configuring MySQL 3.23.36 running on a Sparc/SunOS 5.8 to use the available 1 gig of memory and was wondering if anyone might be able to help find a solution. /usr/ucb/ps -aux gives the stats: USER PID %CPU %MEM SZ RSS TT SSTART TIME COMMAND root 2120 36.6 7.044881669800 ?O Sep 11 16:46 /u01/opt/MySQL/lib when a single user is running a simple query like: SELECT count(id) FROM ind WHERE cust=1 AND email''; This query takes 8.29 seconds to run and there are less than 200,000 records in the ind table. Different queries don't change the percentage of memory being used, only the CPU usage seems to change. I tried adding an index to the ind table for the cust and email fields, but the query took even longer since about 175,000 records have nonempty email fields. mysqld is using the huge.cnf configuration file for 1G-2G systems (settings from file are listed at end of this message) I've also played around with tweaking command line settings via the safe_mysqld commands suggested via http://www.mysql.com/doc/S/e/Server_parameters.html such as shell safe_mysqld -O key_buffer=64M -O table_cache=256 \ -O sort_buffer=4M -O record_buffer=1M with little or no improvement. Can someone explain why MySQL isn't using more memory or point me in the right direction to improve MySQL's performance? Many thanks in advance, Joe Kaiping == cnf file settings: == [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=4 # Try number of CPU's*2 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 1 [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout - 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: Can't get MySQL to use available memory (performance very slow)
Thanks for the quick reply, Dan. The EXPLAIN SELECT for that SELECT outputs: mysql EXPLAIN SELECT count(id) FROM ind WHERE cust=1 AND email''; +---+--+---+--+-+--++--- -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++--- -+ | ind | ALL | NULL | NULL |NULL | NULL | 181831 | where used | +---+--+---+--+-+--++--- -+ I first tried using a 2 field index with slower results and then tried using separate single field indexes, but that was also slower than not using any index. The InnoDB table type sounds promising, so I'll start looking into that. If anyone else has other suggestions, they really are most appreciated!! Thanks again, Joe -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 12, 2001 9:17 AM To: Joe Kaiping Cc: [EMAIL PROTECTED] Subject: Re: Can't get MySQL to use available memory (performance very slow) In the last episode (Sep 12), Joe Kaiping said: Hi there, I'm having trouble configuring MySQL 3.23.36 running on a Sparc/SunOS 5.8 to use the available 1 gig of memory and was wondering if anyone might be able to help find a solution. /usr/ucb/ps -aux gives the stats: USER PID %CPU %MEM SZ RSS TT SSTART TIME COMMAND root 2120 36.6 7.044881669800 ?O Sep 11 16:46 /u01/opt/MySQL/lib when a single user is running a simple query like: SELECT count(id) FROM ind WHERE cust=1 AND email''; This query takes 8.29 seconds to run and there are less than 200,000 records in the ind table. Different queries don't change the percentage of memory being used, only the CPU usage seems to change. Mysql does not cache table data for the MyISAM type, so you won't see any memory change there. What does an EXPLAIN SELECT.. print for the above query? I tried adding an index to the ind table for the cust and email fields, but the query took even longer since about 175,000 records have nonempty email fields. A single 2-column index, or two separate indexes? Mysql can only use one index per query, so a compound index would help the most here. If your problem truly is disk I/O, you can try using the InnoDB table type, which caches both index and table data in memory. -- 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
