RE: Using the Index (Tables)....
Here's how I do it with tables that have multiple keys: CREATE TABLE Users ( Id SMALLINT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT, FirstName VARCHAR(25) NOT NULL, LastName VARCHAR(25) NOT NULL, PRIMARY KEY(Id), KEY LastNameIndex (LastName), Key FirstNameIndex (FirstName) ) ; And every once in a while I also do an optimize table tablename when my queries are getting slower. The only thing you have to watch out for with the optimize command is that it can take a while if you have a large table and that table will become unavailable for access while the command is running. -Pete -Original Message- From: Scott Fletcher [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 11:01 To: [EMAIL PROTECTED] Subject: Using the Index (Tables) I have one question. Once I create 2 or more indexes to a table, should I instruct MySQL to use which index in certain order? (Like use that 2nd index, not the 1st one). Another question, do I need to run the maintance on the index? If so, how? With IBM DB2, I had to run the index maintance weekly, so I wonder if MySQL do it automatically or do I have to do it manually. If so, how? Thanks, FletchSOD *** This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom it is addressed. It may contain sensitive and private proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you are not the intended recipient, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Tradition Asiel Securities Inc. and Tradition (North America) Inc. reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal written confirmation. Tradition Asiel Securities Inc. and Tradition (North America) Inc. are not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. This footnote also confirms that this email message has been swept by Anti-virus detection software for the presence of computer viruses. *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date computation in MySQL PHP
On Sat, 2003-09-13 at 02:50, delz wrote: I want to know how do I use computations on dates in php or mysql like for example, today is September 13, 2003 (2003-09-13) and I want to get the date output if I add 5 days from the given date so that my output would be September 18, 2003 (2003-09-18). And if any given day falls on a Sunday, it will be moved to Monday. I just want to disregard Sunday and want to use Monday to Saturday. Anyone who can help me will be appreciated. Delz, Read this article. I found it very informative when I was looking into doing Date Arithmetic with MySQL. http://www.devshed.com/Server_Side/MySQL/DateArithmetic/ -Pete -- perl -e 'print pack(H*, 70766572746573406E79632E72722E636F6D0A)'
RE: Programmatic MySQL backup in Perl
The way I do it is to call mysqldump.exe from perl, granted it's not the most elegant
way but it's certainly the fastest (to code). Just do:
---
# name or IP address of dbmachine
my $DB_server = localhost.localdomain.tld
# db login authorised to do db dump
my $DB_User = sa
# location and name of dumpfile
my $DB_dumpfile = /tmp/db_dumps/my_dump.sql
# define command with parameters to dump the database
my $cmd = mysqldump --add-locks --add-drop-tables --all-databases --lock-tables
--host=$DB_Server --user=$DB_User $DB_dumpfile;
# run command to dump the database
my $retval = system($cmd);
# check the return value of the dump command
if ($retval != 0)
{
# there was a problem do something about it...
}
---
-Pete
P.S.: This is not production grade code, just something I threw together for you while
eating a sandwich :)
sql
---
-Original Message-
From: Jeff Snoxell [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 11:16
To: [EMAIL PROTECTED]
Subject: Programmatic MySQL backup in Perl
Hi,
is there an easy way to programmatically backup MySQL database tables using
Perl? My Perl code will be running on a machine remote to the MySQL server.
Ta,
Jeff
-
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 MySQL handle 120 million records?
Hi, I've been using MySQL intercompany for a while now with great results. Even the diehard MSSQL people are amazed at how fast it can be at time. One of the things I use it for is to store syslog events in it. I wrote a backend that parses a syslog file as data is being written into it and does multiple things with each syslog entry depending what the entry contains. When I'm done with it the syslog entry goes into a MySQL database where I can store the data and let the operations team access it through a PHP enabled webpage to see either what is going on in the system real-time of be able to do queries about certain hosts, processes or show some stats (what happened to machine x on date y and what processes were running on it, etc...). The MySQL database is being hosted on a Dell Precisions 540 workstation box. It's a P4 1.7GHz Xeon with 512MB of ram and a 40GB IDE disc running Windows 2000 Server. That MySQL database is also being used for other things (nothing too intensive) and I muck around with it also and use it as a test db. The machine also handles webserving chores and runs backup chores and other operations related tasks. The database only holds about 1 months worth of data in it, the rest we don't really need but we keep around for a while outside of the db zipped up. As of when I'm writing this there were about 18.7 million entries in that table: mysql select count(*) from notifications; +--+ | count(*) | +--+ | 18711190 | +--+ 1 row in set (0.00 sec) All these entries have been accumulated from December 1, 2002 till present day: mysql select distinct syslogdate from notifications order by syslogdate; ++ | syslogdate | ++ | 2002-12-01 | | 2002-12-02 | | 2002-12-03 | | 2002-12-04 | | 2002-12-05 | | 2002-12-06 | | 2002-12-07 | | 2002-12-08 | | 2002-12-09 | | 2002-12-10 | | 2002-12-11 | | 2002-12-12 | | 2002-12-13 | | 2002-12-14 | | 2002-12-15 | | 2002-12-16 | | 2002-12-17 | | 2002-12-18 | ++ 18 rows in set (12.95 sec) Notice it took almost 13 seconds to complete that last query. I tried this on a MSSQL server and after 2 minutes I turned the query off. That kind of performance was unacceptable for a webapp that uses a database that does real time queries. I'm quite happy with the performance of MySQL and I just love to see the MSSQL guys retreat when I show off how fast some queries can be (they always strike back with transactional stuff, blah, blah, blah :) Anyway, I would suggest you use Linux for your dbserver with some kind of journaling file system. I would go with ReiserFS because if memory serves correctly it can handle files up to 4 terabytes but you might want to double check since I'm quite forgetful with facts like that :) I would also recommend the fastest SCSI drives you can find. When I do queries in any 10 million+ database I barely get any CPU activity but I get A LOT of disk activity and I think this IDE drive is holding MySQL back. When I have time I'm thinking about moving this database/webapp beast onto a SCSI Linux box and see how well it performs. I think you'll be very pleased with the performance you'll get out of MySQL. -Pete P.S.: Thanks again MySQL team :) - 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
Nested MySQL Statements
Hello All, Is it possible to do nested select statements with MySQL ? Basically I'm trying to display the last 5 rows that got inserted into a table. My SQL query looks like this: select * from tablename limit ((select count(*) from tablename) - 5), -1; In theory it works for me :) but MySQL complains. Is it possible to do nested queries with MySQL ? Does anyone have a better way of displaying the last x amount of rows inserted into a table ? Thanks in advance... -Pete Peter Vertes Beast Financial Systems 404 Fifth Avenue New York, NY 10018 - 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: Remote Connection, please help
I've never connected your way before. I'm using J/Connector from the MySQL site
(http://www.mysql.com/downloads/api-jdbc-stable.html). That way I have no problems
connecting. I do it this way:
// connect to the database
Class.forName(com.mysql.jdbc.Driver);
Connection dbConnection =
DriverManager.getConnection(jdbc:mysql://localhost/myTable, username, password);
Works like a charm. Give it a try...
-Pete
-Original Message-
From: Muhammad Salman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 12, 2002 11:36
To: [EMAIL PROTECTED]
Subject: Remote Connection, please help
I am using the official type 4 mysql driver from
www.mysql.com but unable to make a remote connection
with the mysql database running on our companie's
remote server. I mailed some mailing mailing lists but
didnt find out the right answer yet. I am using the
following code for simple remote connection test:
Class.forName(org.gjt.mm.mysql.Driver).newInstance();
Connection con =
DriverManager.getConnection(jdbc:mysql://66.137.7.185:3306/prefill,prefill,jeem);
out.println(Testing Database Connectivity);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(select * from
state);
int i = 0;
while(rs.next() i 10)
{
out.println(h2 Output: +rs.getString(2) +
/h2);
i ++;
}
rs.close();
stmt.close();
con.close();
Your quick response will be highly appreciated.
=
Muhammad Salman
Sun Certified Java 2 Programmer
+92214932737 (H)
+923332234828(C 1)
+923332103334(C 2)
Email: [EMAIL PROTECTED]
Text Pager: [EMAIL PROTECTED]
Work Web: http://www.jeemsolutions.com
__
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
-
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
