Speed this up?
I have this query below than seems to be running really slow. I have tried indexing key JOIN fields but it seems to have not helped. Any pointers would be greatly appreciated. (ver: MySQL 3.23.53) side note: seems to run fairly quick in 4.0.18 SELECT dl.email as download_email, CONCAT(dl.first, , dl.last) as download_name, o.id as orderID, c.id as cust_id, dl.id as dlId, c.email as customer_email FROM download_users as dl LEFT JOIN orders as o ON dl.email = o.email LEFT JOIN customers as c ON (dl.email=c.email OR c.id = o.cust_id OR concat(dl.first, dl.last) = concat(c.bill_first,c.bill_last)) GROUP BY download_email ORDER BY orderID DESC, cust_id DESC -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Category System schema
Does anyone know the best schema to achieve a multi-level category system? I need a person to be able to add categories on the fly, but then also specify sub categories and even possibly sub-categories of sub-cats. I have found two possible ways inside one table with using parentID, groupID, etc. But the issue I am now running into is that I have to sort on groupID to get the items to group correctly when showing in a HTML drop down list. Which means I can't sort alphabetically. If I do the order is not right...using Lasso 6 with some looping code to indent the subs in the list. On top of that I would also like the ability to assign a priority field for listing in that order as well. There has to be some kind of solution to this that I don't see. Any ideas? Thanks! -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Extracting DATE from a TIMESTAMP field
I have an old table that has about 2000 records and hasn't been modified. I realized it was missing a create_date. Do anyone know or have any slick SQL code to extract out the date from the timestamp field and update and insert the date into the create_date field? Thanks! -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.642.8225 CELL --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Extracting DATE from a TIMESTAMP field
At 4:22 PM -0500 6/6/03, Paul DuBois wrote: At 17:17 -0400 6/6/03, Alex Pilson wrote: I have an old table that has about 2000 records and hasn't been modified. I realized it was missing a create_date. Do anyone know or have any slick SQL code to extract out the date from the timestamp field and update and insert the date into the create_date field? Thanks! -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.642.8225 CELL --- FROM_DAYS(TO_DAYS(t)) will strip out the time part and convert a DATETIME or TIMESTAMP value to a DATE. So you could do this: UPDATE tbl_name SET create_date = FROM_DAYS(TO_DAYS(timestamp_field)); NICE! Thanks for the fast response! I was trying to use LEFT and other Functions to achieve it. -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.642.8225 CELL --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doorstop II now available
At 4:52 PM -0600 1/27/03, Paul DuBois wrote: At 14:38 -0800 1/27/03, Jeremy Zawodny wrote: On Mon, Jan 27, 2003 at 01:17:26PM -0600, Paul DuBois wrote: Okay, it seems that the book is making its way to the booksellers now, so it's time for a general announcement: MySQL, Second Edition (a.k.a. Doorstop II) is now available. Information is available at the companion web site, including a page indicating what's new, links for downloading the accompanying software distribution, and links to bookseller listings. See: http://www.kitebird.com/mysql-book/ Sorry, no reviews yet. Book's too new! :-) Looks good at first glance! But there are a ton of pages to flip thru. (My copy arrived this morning...) Maybe I should work up a snappy slogan like: 65% more pages, but only 25% more thickness! I guess that's not too snappy. But a'm glad they used thin enough paper that it didn't turn into a completely giant book. I just ordered it on bookpool but it says it is out of stock. -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL --- - 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
mysqldump vs. BACKUP TABLE
Ok, I know some of the differences to both of these, but is there any strong reason to use mysqldump over issuing a SQL to BACKUP tables? *Note. I will be looping through known tables of a particular database and issuing the BACKUP TABLE command, are there any other implications for issuing this command in succession for about twenty tables? -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL --- - 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
Updating text that ends with...
I am stumped on how to create a statement that will find all instances of a field (price) and change the .98 to .99. Looking through the MySQL book of P. Dubois, it seems I need to do pattern matching but I can't figure out how to combine the statement into an UPDATE. Doea anyone have an off - the shelf query for something like this? -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL --- - 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: Updating text that ends with...
At 8:23 AM +1100 11/12/02, Daniel Kasak wrote: Alex Pilson wrote: I am stumped on how to create a statement that will find all instances of a field (price) and change the .98 to .99. Looking through the MySQL book of P. Dubois, it seems I need to do pattern matching but I can't figure out how to combine the statement into an UPDATE. Doea anyone have an off - the shelf query for something like this? update MyTable set price=price+.01 where price like '%.98' Wow, I must have been over thinking it, this was simple. Thanks big time! -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL --- - 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: Export, then Import HUGE number of rows/data?
At 4:46 PM -0500 10/15/02, Paul DuBois wrote: At 14:56 -0400 10/15/02, Bill Leonard wrote: We have a client who wants to switch their hosting over to us, but has an issue with his MySQL database. It's about 1.4 million rows, and about 350MB of data. PhpMyAdmin apparently times out when he is doing the dump. Is that a MySQL issue, or a phpMyAdmin issue? That or the browser connection. I have seen a browser time out that had a large number of rows being retrieved via Lasso 5. Perhaps try a third party MySQL app such as Pro SQL or SQLBoss. -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 3.23.51 install on OS X
I am installing a new MySQL installation on a new Xserve and I am getting this message; [xserve:/usr/local/mysql] admin% sudo ./scripts/mysql_install_db Sorry, the host 'xserve' could not be looked up. Please configure the 'hostname' command to return a correct hostname. If you want to solve this at a later stage, restart this script with the --force option [xserve:/usr/local/mysql] admin% What am I missing? I do have the DNS setup for this host... -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL --- - 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
restarting MySQL on OS X
I was wondering if anyone knew the proper way to restart MySQL on OS X? I did mysqladmin -p shutdown then safe_mysqld...is this the same as if the machine started up? -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso 5 Web Development/Hosting // MySQL Development // Apple DVD Studio Pro Authoring/Training // Macromedia Director/Flash Authoring --- - 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: restarting MySQL on OS X
At 9:20 AM -0400 5/21/02, Reid Sutherland (mysql) wrote: Alex Pilson wrote: I was wondering if anyone knew the proper way to restart MySQL on OS X? I did mysqladmin -p shutdown then safe_mysqld...is this the same as if the machine started up? Not exactly the same. But for intents and purposes it does what you want. If you want to truly make it seem like the system went down and came up, do the following: # kill -9 safe_mysql pid # kill `cat /path/to/mysql.pid` # /path/to/safe_mysqld Or you can try this. # /etc/init.d/mysqld stop (or something similar) then # /etc/init.d/mysqld start I knew there was more to this than what I was doing. Thanks I think this is what I am looking for. -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso 5 Web Development/Hosting // MySQL Development // Apple DVD Studio Pro Authoring/Training // Macromedia Director/Flash Authoring --- - 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
TIMESTAMP field
Is it possible to alter or modify a timestamp field to a different date? -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso 5 Web Development/Hosting // MySQL Development // Apple DVD Studio Pro Authoring/Training // Macromedia Director/Flash Authoring --- - 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: TIMESTAMP field : nevermind
At 3:22 PM -0400 5/20/02, Alex Pilson wrote: Is it possible to alter or modify a timestamp field to a different date? Doh. I found the answer... The answer is yes. -- --- Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso 5 Web Development/Hosting // MySQL Development // Apple DVD Studio Pro Authoring/Training // Macromedia Director/Flash Authoring --- - 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
character
Does anyone know if there is a way to preserve the trademark character when importing it into to MySQL? Using Lasso 5 I can add it through a form submission and retrieve it in tact, so there must be a way... I am using LOAD DATA to import my text file... -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - 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: character
At 12:23 AM +0300 4/25/02, Gelu wrote: Hi, because you must use the optional command for fields or lines : Eg. load data infile 'yourfile' into table your_table fields terminated by '\your_delimiter_char_for_fields' lines terminated by '\r\n'; I do. So how does that have anything to do with the trademark character getting encoded correctly into a MySQL field? -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - 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
GRANT questions on OS X.
Why is this producing a syntax error at the %? This is supposed to create a global user, yes? GRANT ALL ON *.* TO myuser@% IDENTIFIED BY mypassword; Is there a quick command to show all GRANTS? or Users? -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - 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: GRANT questions on OS X.
At 12:59 PM -0500 4/19/02, Paul DuBois wrote: Why is this producing a syntax error at the %? This is supposed to create a global user, yes? GRANT ALL ON *.* TO myuser@% IDENTIFIED BY mypassword; myuser@% Andrew Hazen emailed me to use single quotes and it worked. So does mysql care if it is single or double quotes? -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - 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
Converting VARCHAR 255 column to TEXT 64
Does converting one type of column to a bigger type such as listed in my subject lose the information that is contained in that row/column or does it just expand the limit? -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - 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: Keeping MySQL Databases Heathly
At 10:09 AM -0500 4/4/02, John Klein wrote: [EMAIL PROTECTED] wrote: Do MySQL server administrators recommend running a cron script daily to isamchk? Is there any other things that should be run daily to keep MySQL running top-notch? If so, does anyone have any pointers to a script that has already been developed? I actually find it helpful to periodically run the 'SHOW STATUS' command on the database and dump the results to a file. Examining this can give all sorts of information on how much use the database is getting, whether queries are optimized, etc. Documentation on the command is here: http://www.mysql.com/doc/S/H/SHOW_STATUS.html. I'm actually running this every half-hour, because I'm a freak. The script I use isn't exactly production quality (one of those ten-minute jobs). I keep intending to fix it someday or just use some internal MySQL statement that does the same thing, but I'm sure you know how it is. The script takes one parameter (a filename to dump results to). If called without one, it prints to STDOUT. Here's my code: Cool. I will give it whirl to see what information it reveals. -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - 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: Case tools
At 10:19 AM -0500 4/4/02, Smith.Michelle wrote: Can someone please recommend CASE tools that function well with MySQL databases? Need to do some reverse engineering ... create the ERD, data dictionary, etc Very cool tool... http://www.datanamic.com/dezign/index.html -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - 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
