Re: LOAD DATA INFILE
From my experience, if you want to import data into a date field, you have to make sure that the date format in your csv file conforms to a date format required by MySQL. This part is covered in MySQL documentation. So, your 20.12.1999 date should become 2001-12-20 and so on. P.S. Don't forget to specify TERMINATED BY ',' while using LOAD DATA INFILE. I use this feature VERY often and never had any trouble.. :-) Bernhard Doebler wrote: Hi, I have a CSV-file exported from another database. It contains fields with dates and dates and times. A somplefied CSV-file version looks like so. 20.12.1999;21.12.1999 03:35:27 When I import it with LOAD DATA INFILE... and use fieldtypes date and datetime it sadly does not work. It imports as many records as lines are given in the file to import but each date is zero (NULL). Is there a possibility to import it that simple way rather to import it as text in one table and export it using date conversion-functions in another table? Best Regards Bernhard Dobler - 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 -- Bolek, URL: http://www.bolek.com URL: http://slash.bolek.com e-mail: [EMAIL PROTECTED] ICQ: 4086197, Address: 402905326 - 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
Modifiying values in table_a with values from table_b
What I want to do is not overly complicated (and it works the way I did it) but I want to find out if it can be done easily using MySQL only. Table_a: col1, col2, col3 Table_b: col1, col2, col3 Currently, to replace values in col1 in Table_a with values of col2 in Table_b, I do the following: while read col1 col2; do echo col1 echo col2 # so I can see what's happening mysql -uuser -ppassword database eof UPDATE Table_a SET col1 = $col2 WHERE Table_a.col2 = $col1 eof done Table_b # data from comma delimited file (exported) I hope that you can see what I am trying to do. I tried to do the same thing prior to importing it into MySQL but gave it up. It was simply too long to go over two WHILE loops with 1600 and 95000 records respectively. So now, I have this kludge where I have glued shell script with MySQL. How could I accomplish the same in MySQL alone? Thanks in advance. -- Bolek, URL: http://www.bolek.com URL: http://slash.bolek.com e-mail: [EMAIL PROTECTED] ICQ: 4086197, Address: 402905326 - 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 authentication module for Apache
Where can I get MySQL authentication module for Apache? Thanks. -- Bolek, URL: http://www.bolek.com URL: http://slash.bolek.com e-mail: [EMAIL PROTECTED] ICQ: 4086197, Address: 402905326 - 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: Restore from Logs?
It seems to me that you have misunderstood the manual. As you have pointed out yourself, you are running MysQL on Windows platform. The syntax you are referring below is for UNIX/Linux systems... (shell means current shell in use your system and the rest are commands available on UNIX systems). Lee Jenkins wrote: Hello, I'm having some trouble understanding the syntax for populating a backup with logged updates. In the manual, is says: shell ls -1 -t -r file_name.[0-9]* | xargs cat | mysql I'm running MySQL on Win98 and Win2000. I've tried using the syntax above, but to no avail. Any help would be greatly appreciated as I am in a crunch. Lee Jenkins -- Bolek, URL: http://www.bolek.com URL: http://slash.bolek.com e-mail: [EMAIL PROTECTED] ICQ: 4086197, Address: 402905326 - 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: most viewed
You should definitely read up the manual. If you don't do this, will not do you homework and simply expect an answer handed down to you, you might get disappointed. Question you have asked is like MySQL 101. To get you started this time, however, here it is: select * from table bands order by views DESC limit 2; Tyler Longren wrote: I've got a table that contains a list of bands. There's a field named views. This field contains how many times each band's page has been viewed. Ex: id artist views 1 No names6 2 WISH10 3 Deftones45 What's the SQL to select the top 2 most viewed bands(which would be WISH Deftones)? I've tried a few things, but haven't gotten anything to work correctly for me. Thanks. Tyler Longren - 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 -- Bolek, URL: http://www.bolek.com URL: http://slash.bolek.com e-mail: [EMAIL PROTECTED] ICQ: 4086197, Address: 402905326 - 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 this a normal behaviour?
Thanks. Just needed a confirmation. :-) Cal Evans wrote: Yes and I believe it's explained in the manual better than I can explain it here. Roughly speaking, if the field contains: foobar then LIKE 'foo%' says match anything starting with foo. Since it's STARTING with foo then the index can be used. Just like the Index of a book would be useless if you wanted to find references in a book for all words ending in bar. LIKE '%foo%' won't use and index either for the same reason. LIKE '%bar' is right out. HTH, Cal http://www.calevans.com -Original Message- From: --==[bMan]==-- [mailto:[EMAIL PROTECTED]] Sent: Monday, March 26, 2001 7:40 PM To: MySQL List Subject: Is this a normal behaviour? O.K. I use MySQL version 3.23 (RedHat 7.0 release) and have a question about this query: 1.) [...] LIKE '%pattern' 2.) [...] LIKE 'pattern%' The first query does not use an index while the second does. Is there any explenation for this? Thanks. -- Bolek, URL: http://www.bolek.com URL: http://slash.bolek.com e-mail: [EMAIL PROTECTED] ICQ: 4086197, Address: 402905326 - 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 -- Bolek, URL: http://www.bolek.com URL: http://slash.bolek.com e-mail: [EMAIL PROTECTED] ICQ: 4086197, Address: 402905326 - 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: print out rows from array/perl
This is a question for PERL group but anyhow, read again the chapter in MySQL
on printing results from MySQL using PERL. It prints continentA because
that's what you have asked for.
On Sunday 11 February 2001 15:36, Andreas Antes wrote:
=I have created 2 columns:
=col1 varchar(20)
=col2 varchar(20)
=when I add content to the columns it looks like
=this for example:
=col1
=
=contentA
=NULL
=contentB
=
=Now when I try to print out the content in Perl
=using this code:
=
=while($dirty_words = $sth-fetchrow_array) {
=print "$dirty_wordsbr\n";
=}
=
=it just prints the first row 'contentA' then stops.
=
=How can I print all rows in the column no matter if
=NULL or even empty rows in it?
=
=Thanks for your help
=
=__
=Do You Yahoo!?
=Get personalized email addresses from Yahoo! Mail - only $35
=a year! http://personal.mail.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
--
Bolek,
URL: http://www.bolek.com
URL: http://slash.bolek.com
e-mail: [EMAIL PROTECTED]
ICQ: 4086197, Address: 402905326
-
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: Performance issues.
"Only" 272235??? I enter on average about 75,000 to 80,000 records a day (and some times, I break 100,000 records a day. I do monthly rotations so it's easy to calculate how big my table gets). Granted, I don't know what your table structure is but mine is very simple. All I do is run imports every morning of CACI.txt file generated by EchoScope (network traffic monitoring tool). I have about 15 fields per records (as far as I can remember) and most of them are indexed. I wish only, that I could only do unique index on more than just ID field but due to the uniqueness of what I'm doing, I have to live with regular indexes. Some of the fields are longer than 100 characters so I made sure that my indexes are no longer than 20 characters. I was playing a lot before I got performance I have right now. One thing I've noticed was that if I indexed more than 20 characters (some times, I was crazy trying to index all 255 characters), performance was actually dropping down drastically. Now, things I want to share with: 1. I use my database as a back-end for my PERL scripts and web interface for easy data querying by end users. Having said that, I could not really afford long delays between queries and data display to the browser. 2. In my queries I use =, LIKE and used to use REGEXP within MySQL. Out of these three, REGEXP was the WORST in performance (and rightly so). I decided to drop it altogether. An example: I created a keyword list so clients would not have to type individual words to find what they want. So rather than type: red, yellow, green, gray, blue and so on, they would only type "colors" in the query field. It was my task, then, to go and fetch all those. It's, of course a simple example but you can imagine the implications (sex sites, e.g.). Initially, I used REGEXP and it took FOREVER to return a query. It took on average 5-7 minutes to return a query to a browser searching through 1,000,000+ database. It was totally unacceptable so I had to re-write it. I have decided to write a routine that would simply write a query for me. So, if I had 30 words I am interested it, my routine would build a query for me like: AND (field LIKE %something% OR field LIKE %something1% OR field LIKE %something2%) AND NOT (field LIKE %thisthing% OR field LIKE %thisthing1%) and so on (it can get quite long). As you can see, I use %word% yet still, I get a great performance out of it. After re-writing my code, query of that type dropped to about 60 seconds running over 1,000,000+ records. It's sevenfold+ improvement over MySQL's builtin REGEXP. Doing '=' returns a query almost in less than a second to a second on the server. Very rarely it's more than a few seconds. Doing 'LIKE "something%"' query is also very fast but doing 'LIKE %something%' is not much slower either. The longest it took for any of my queries to run was 90 seconds. Overall, I am very pleased with the way things go. I was comparing how MS SQL would hold up against MySQL. I tried to import 500,000 records from ASCII file to MS SQL and it took half a day only to completely die (I guess, machine ran out of resources). That was done on a comparable machine running Windows NT 4.0. The same task on MySQL took me between 1 to 5 minutes (if table was indexed). Running some test against commercial databases (in my case, Progress. I don't know if anybody knows it here), MySQL also came up a winner. It was about 10 times faster in read queries. I guess, what I am trying to say is that it's up to you to optimize it as much as possible. Run tests and see what's best for you. P.S. If you let people run queries like 's%', expect long delays. It's only natural. I always tell my users that if they want speed up their queries, type in as much as possible. Unless it's only absolutely necessary, use query of that type. Now the hardware (don't laugh, please): IBM Pentium II, 450MHz with 324Mb of RAM (so I have less than you do) :-) One thing that really bugs me, though, is the disks. On that particular machine they are VERY VERY slow. It's a simple off the assembly line machine so I guess, I can't really complain. If I switched them to something better, I'm sure I would get even a better performance. On Tuesday 06 February 2001 17:24, Ryan Hadley wrote: - I'm not much of a db admin so I really don't know how to get better - performance out of our database... but it seriously needs some speeding up. - - We have this huge dictionary... It's 272235 rows. We have to be able t - search through it. - - We run about 800,000 visits a day. - - Right now we're doing a "WHERE keyWord LIKE 'word%'"... but the "LIKE"'s are - killing the machine. We had to upgrade our memory to 512M. That helped a - bit, now our machine doesn't just die. But it's still way to slow. The CPU - is maxing out and we're hitting like 10-15% idle during slow periods and - 0.0% idle
Re: Big Time
If RPM used: rpm -ql mysql (or if unsure about the package: rpm -qa | grep -i mysql) Location: /var/lib/mysql There you will find your databases where one directory under mysql corresponds to one database. Documentation on line and searchable on-line manual come also handy. Just read On Tuesday 06 February 2001 16:55, kentj wrote: - I have read that IBM has concluded that Linux is ready for the big time. - That may be true for Linux but I would doubt that the same could be said - for MySQL. For example the missing host.frm has been in threads in the - archive for a long time, I have been - bitten by the beast with no success yet in sight despite following the - leads from kind folk on this list. Such simple problems as - locating the database on a large capacity drive after installing MySQL - from an RPM need to be addressed in a simple and reliable fashion. - Trying to guess what values in which of the 16 parameters for - safe_mysqld might make a difference is not simple. - - I suspect users could come up with their own pet problems. - - - - - 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 -- Bolek, URL: http://www.bolek.com URL: http://slash.bolek.com e-mail: [EMAIL PROTECTED] ICQ: 4086197, Address: 402905326 - 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
Using MySQL for web authentication and others..
I just want to ask a quick question which is probably only indirectly related to this group. Where can I find some useful info on how to integrate MySQL into Apache authentication scheme? Basically, I want to use MySQL database to store users' ids and passwords rather than .htpasswd file generated by Apache's htpasswd program. Thanks. -- Bolek, URL: http://www.bolek.com URL: http://slash.bolek.com e-mail: [EMAIL PROTECTED] ICQ: 4086197, Address: 402905326 - 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 not make mysql
Why gamma??? Go to www.mysql.com and download a stable version of mysql-3.23.31. It's official now! On Saturday 27 January 2001 16:11, CheongMeng wrote: = Hi, = I failed to install mysql-3.23.30-gamma on bsdi4.2. = this is the err mesg I got: = Making all in myisam = make: don't know how to make mi_test_all. Stop = *** Error code 1 = = Pls help = Thanks. = = = = = - = 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 -- Bolek, URL: http://www.bolek.com URL: http://slash.bolek.com e-mail: [EMAIL PROTECTED] ICQ: 4086197, Address: 402905326 - 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
