RE: Mysql Table partitioning
Thanks Joe, Its really intersteing .. joe [EMAIL PROTECTED] wrote: Hope this helps Data is stored in the RANGE partition that the partition key falls into regardless if its an insert or update. One of the benefit of this is partition pruning. By specifing the partitioned field in a where cluase the optimizer know that it only needs to look into those partitions that fit into the range values of the partition key. Below is your table defintion, some inserts, and selects and explain plans with partitions showing the partition pruning and where the data is. create table `customers`( `CustomerID` varchar(40) not null, `TimeCreated` DATETIME not null default 0, PRIMARY KEY(`CustomerID`, `TimeCreated`) )ENGINE InnoDB PARTITION BY RANGE ( YEAR(TimeCreated) ) (PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE ); insert into customers values (1,now()); insert into customers values (2,date_sub(now(),interval 5 year)); insert into customers values (3,date_sub(now(),interval 10 year)); insert into customers values (4,date_sub(now(),interval 15 year)); insert into customers values (5,date_sub(now(),interval 20 year)); mysql select * from customers; ++-+ | CustomerID | TimeCreated | ++-+ | 1 | 2007-11-28 08:12:48 | | 2 | 2002-11-28 08:13:08 | | 3 | 1997-11-28 08:13:09 | | 4 | 1992-11-28 08:13:09 | | 5 | 1987-11-28 08:13:09 | ++-+ analyze table customers; select table_name, partition_name, table_rows from information_schema.partitions where table_name = 'customers'; ++++ | table_name | partition_name | table_rows | ++++ | customers | NULL | 9 | | customers | p0 | 1 | | customers | p1 | 1 | | customers | p2 | 1 | | customers | p3 | 2 | ++++ mysql explain partitions select * from customers where timecreated = '1990-12-31'; ++-+---++---+---+--- --+-+--+--+--+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+---+--- --+-+--+--+--+ | 1 | SIMPLE | customers | p0 | index | NULL | PRIMARY | 50 | NULL | 2 | Using where; Using index | ++-+---++---+---+--- --+-+--+--+--+ mysql select * from customers where timecreated = '1990-12-31'; ++-+ | CustomerID | TimeCreated | ++-+ | 5 | 1987-11-28 08:13:09 | ++-+ --- mysql explain partitions select * from customers where timecreated between '1991-01-01' and '1995-12-31'; ++-+---++---+---+--- --+-+--+--+--+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+---+--- --+-+--+--+--+ | 1 | SIMPLE | customers | p1 | index | NULL | PRIMARY | 50 | NULL | 2 | Using where; Using index | ++-+---++---+---+--- --+-+--+--+--+ mysql select * from customers where timecreated between '1991-01-01' and '1995-12-31'; ++-+ | CustomerID | TimeCreated | ++-+ | 4 | 1992-11-28 08:13:09 | ++-+ mysql explain partitions select * from customers where timecreated = '1995-12-31'; ++-+---++---+---+--- --+-+--+--+--+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+---+--- --+-+--+--+--+ | 1 | SIMPLE | customers | p0,p1 | index | NULL | PRIMARY | 50 | NULL | 2 | Using where; Using index | ++-+---++---+---+--- --+-+--+--+--+ 1 row in set (0.00 sec) mysql select * from customers where timecreated = '1995-12-31'; ++-+ | CustomerID | TimeCreated | ++-+ | 4 | 1992-11-28 08:13:09 | | 5 | 1987-11-28 08:13:09 | ++-+ -Original Message- From: gunwant walbe [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 28, 2007 12:21 AM To:
MySQL University session November 29 postponed
Unfortunately, we'll have to cancel today's MySQL University session because the presenter is sick. It will be postponed to * December 20 * Please register for this session by filling in your name on the session Wiki page that you can find here: http://forge.mysql.com/wiki/EC2 Registering is not required but appreciated. Thanks! That Wiki page also contains a section to post questions. Please use it! Those planning to attend a MySQL University session for the very first time should probably read the instructions for attendees, http://forge.mysql.com/wiki/Instructions_for_Attendees. -- Regards, Stefan Hinz [EMAIL PROTECTED], MySQL AB Documentation Manager Berlin, Germany (UTC +1:00/winter, +2:00/summer) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to drop index if exists
Folks How can one conditionally drop an index in MySQL? Googling shows that the drop index does not support an if exists qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help to use java to connect to MYSQL DB
Hi all,
i need to use Eclipse(java SDK) to connect to MYSQL DB,
i write the following code for connection, but when i run it no thing
happen, no output appear, when i debug it, i found that the debuger was
stopped @ the line
conn=DriverManager.getConnection(connectionURL,Eman,Eman);
my code for connection
String driver =com.mysql.jdbc.Driver;
String dbName=travel;
String connectionURL = jdbc:mysql://localhost/ + dbName;
try{
//load the driver
Class.forName(driver).newInstance();
conn=DriverManager.getConnection(connectionURL,Eman,Eman);
System.out.println(Successfully connected to +
MySQL server using TCP/IP...);
}
catch(Exception e)
{
e.printStackTrace();
System.out.println(Error +e.getMessage());
}
-
-note: i added the connector/j.jar to the classpath environment variable
-Is the connector/J version must be consistent with the jdk version?
-i consume more time to solve this problem but till now didn't reach to any
thing.any help please,
waitting for reply
--
Eman Hossny,
Teacher Assistant,
Computer Science Department,
Faculty of Computers and Information
Cairo University, Egypt
يارب العتق من النار لى ولوالديا
repost timestamp for update insert
i asked this question before, but when i tried what D.Vin suggested, i got an error. mysql version is 4.1.12 i tried: CREATE TABLE tbl_spdate( spdate_ID int NOT NULL PRIMARY KEY AUTO_INCREMENT , spdate_date date NOT NULL , spdate_notes varchar( 100 ) , spdate_created timestamp default 0, spdate_updated default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); and i got this: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )' at line 7 am i have a wrong version? Thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: repost timestamp for update insert
Your missing your data type for the spdate_updated attribute CREATE TABLE tbl_spdate( spdate_ID int NOT NULL PRIMARY KEY AUTO_INCREMENT , spdate_date date NOT NULL , spdate_notes varchar( 100 ) , spdate_created timestamp default 0, spdate_updated timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Thursday, November 29, 2007 8:24 AM To: mysql@lists.mysql.com Subject: repost timestamp for update insert i asked this question before, but when i tried what D.Vin suggested, i got an error. mysql version is 4.1.12 i tried: CREATE TABLE tbl_spdate( spdate_ID int NOT NULL PRIMARY KEY AUTO_INCREMENT , spdate_date date NOT NULL , spdate_notes varchar( 100 ) , spdate_created timestamp default 0, spdate_updated default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); and i got this: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )' at line 7 am i have a wrong version? Thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: repost timestamp for update insert
I think you are missing the data type for spdate_updated. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Thursday, November 29, 2007 10:24 AM To: mysql@lists.mysql.com Subject: repost timestamp for update insert i asked this question before, but when i tried what D.Vin suggested, i got an error. mysql version is 4.1.12 i tried: CREATE TABLE tbl_spdate( spdate_ID int NOT NULL PRIMARY KEY AUTO_INCREMENT , spdate_date date NOT NULL , spdate_notes varchar( 100 ) , spdate_created timestamp default 0, spdate_updated default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); and i got this: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )' at line 7 am i have a wrong version? Thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Falls Out Of Sync With LOAD DATA
On Nov 28, 2007 11:18 PM, B. Keith Murphy [EMAIL PROTECTED] wrote: The reason I asked about version is that it looks like there is problem replcating a load data infile command from some versions of 4.x to 5.x slaves. Master and Slaves are 5.x. Hopefully I've figured out the issue. When the slave would fall out of sync, I would resync using rsync. In that rsync command I was using the --delete option which would delete the relay log from the slave machine. Since the relay log was being removed constantly, the replication was in a strange state. That problem is fixed so hopefully things should stay in sync. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
