Re: Partitioning on a Substring of Varchar Column in Mysql
On Fri, Sep 21, 2012 at 9:43 PM, Rick James wrote: > Some statements are incorrect... > > If you PARTITION on (`x`), then `x` must be included in every UNIQUE key. > That includes the PRIMARY KEY. > > You do not need to PARTITION on the PK. > Yes true, but my application requires primary key on ( `id` varchar(255) NOT NULL, ) column. I cannot alter the schema. So if i need to partition on created column then i need to put primary key as ( id,created_time ) that is not accepted because this causes duplicate records of id under different created times. i.e i ask in my first can we partition on substring from id column. > > An AUTO_INCREMENT value, say `id` must be the _first_ field in _some_ key. > `id` does not have to be UNIQUE, nor does it have to be the PK. (However, > if you do not have "UNIQUE(`id`)", by itself, it is possible to > _explicitly_ create duplicate ids. This is not likely in normal practice.) > > So, if you are partitioning on dt_dtamp, and you have id AUTO_INCREMENT, > then PRIMARY KEY ( 'id','dt_dtamp') is sufficient. UNIQUE ('id') is > unnecessary (and disallowed). > I don't have auto increment column in my table , my primary key is ( `id` varchar(255) NOT NULL, ) > > If `id` is a string, then you _could_ (in 5.5.x?) PARTITION BY RANGE(id) > and specify VALUES LESS THAN... > Is the below function works in 5.5 PARTITION BY RANGE ( substring(id,9,6) ) ( -> PARTITION c0 VALUES LESS THAN ( substring('014-120412124227546-o-C@1469',9,6) ), -> PARTITION c1 VALUES LESS THAN ( substring('014-120512124227546-o@1469',9,6) ), Rt now not supported in 5.1.5 > WHY do you want to use PARTITIONing? I ask because I find a lot of people > think they want to PARTITION, yet they have not determined that there will > be any benefit. > > I need partitioning because three tables in my database are populated in seconds , right now size > 40 GB. We don't need data older than 15 days , right now we are deleting ( purging ) older records manually that causes fragmentation and a lot of manual effort.PFA schema of a table. So I decided to partiton on created_time column & purge records by deleting partitions but I cannot change the primary key (id) to primary key ( id,created_time) column i.e the only concern i have. All is working fine as the proper indexes guided by you but I cannot use partitioning to solve this issue if i need to change primary key i.e i m moving to partition on substring of id column if feasible. Any other way i can solve this issue. Thanks for your link also , it is very helpfull. Thanks > See tips in > http://mysql.rjweb.org/doc.php/ricksrots > > > > -Original Message- > > From: Adarsh Sharma [mailto:eddy.ada...@gmail.com] > > Sent: Friday, September 21, 2012 5:09 AM > > To: mysql@lists.mysql.com > > Subject: Re: Partitioning on a Substring of Varchar Column in Mysql > > > > Just update the string needed function substring(id,9,6). > > > > On Fri, Sep 21, 2012 at 5:18 PM, Adarsh Sharma > > wrote: > > > > > Hi all, > > > > > > I created a partition on a timstamp column ( dt_dtamp ) in > > > mysql-5.1.58 testing table.But as all of us know that partitioned > > > column need to be primary key. > > > I have already id column as primary key but my application cann't > > > afford primary key ('id','dt_stamp') in a table. > > > > > > I thought & tried PRIMARY KEY ( 'id','dt_dtamp') & UNIQUE ('id') > > will > > > work but below error came : > > > > > > ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the > > > table's partitioning function > > > > > > as it is clearly mentioned in > > > http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations- > > partit > > > ioning-keys-unique-keys.html > > > . > > > > > > Is it possible i can create partitioning on a substring of > > > varchar(255) ( id ) column. Below are the some contents of my id > > > column :- > > > > > > 0038000-120614070130414-sudoie-sudoi-C@86 | > > > | 0038000-120614070130414-sudoie-sudoi-C@87 | > > > | 0038000-120614070130414-sudoie-sudoi-C@88 | > > > | 0038000-120614070130414-sudoie-sudoi-C@89 | > > > | 0038000-120614070130414-sudoie-sudoi-C@90 | > > > | 0038000-120614070130414-sudoie-sudoi-C@91 | > > > | 0038000-120614070130414-sudoie-sudoi-C@92 | > > > | 0038000-120614070130414-sudoie-sudoi-C@93 | > > > | 0038000-120614070130414-sudoie-sudoi-C@94 | > > > | 0038000-120614070130414-sudoie-sudoi-C@95 | > > > | 0038000-120614070130414-sudoie-sudoi-C@96 | > > > > > > It includes date in it. Can i make my partitioning on string after > > > first hyphen : 120614 > > > > > > Is it possible in mysql5.1.58 or may be higher versions so that i > > have > > > only primary key ('id') on my table. Need a crack for it. > > > > > > > > > Thanks > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Mysql cluster installation error
Hello, I am trying to install MySQL cluster on three physical machines. Management Node on one machine. Data Node on two machines. SQL node on the same machine as Management Node. Management node started Data Nodes started *SQL node started but not connected to Management NOde and it gives no error * C:\mysql\bin>ndb_mgm -e show Connected to Management Server at: localhost:1186 Cluster Configuration - [ndbd(NDB)] 2 node(s) id=8@172.16.56.8 (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0, Master) id=9@172.16.56.9 (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=6@172.16.56.7 (mysql-5.5.25 ndb-7.2.7) [mysqld(API)] 1 node(s) id=7 (not connected, accepting connect from 172.16.56.7) *config.ini* [ndbd default] # Options affecting ndbd processes on all data nodes: NoOfReplicas=2# Number of replicas DataDir=C:/mysql/bin/cluster-data # Directory for each data node's data files DataMemory=80M# Memory allocated to data storage IndexMemory=18M # Memory allocated to index storage [ndb_mgmd] # Management process options: HostName=172.16.56.7# Hostname or IP address of management node DataDir=C:/mysql/bin/cluster-logs # Directory for management node log files NodeId=5 [ndbd] # Options for data node "A": HostName=172.16.56.8 # Hostname or IP address NodeId=8 MaxNoOfOrderedIndexes=1024 MaxNoOfAttributes=3000 # added 2012.8.08 [ndbd] # Options for data node "B": HostName=172.16.56.9 # Hostname or IP address NodeId=9 MaxNoOfOrderedIndexes=1024 MaxNoOfAttributes=3000 # added 2012.8.08 [mysqld] # SQL node options: HostName=172.16.56.7 # Hostname or IP address NodeId=7 *my.ini* * * [mysql_cluster] # Options for management node process config-file=c:/mysql/bin/config.ini configdir=c:/mysql/bin/cluster-cache/ [mysqld] # Options for mysqld process: ndbcluster # run NDB storage engine ndb-connectstring=172.16.56.7 # location of management server ndb-nodeid=7 server-id=7 default-storage-engine=ndbcluster Could anyone help to identify/ Aastha Gupta
RE: Aggregate
2012/09/04 11:43 -0700, Rick James int(1) does not mean what you think. Probably you want TINYINT UNSIGNED. Yeap, a real misfeature of MySQL. It is also one of the ways wherin MySQL puts in C and takes PL1 away. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Risks involved in MyISAM to Innodb
Am 21.09.2012 18:18, schrieb Rick James: > (Apologies to the rare bottom-poster.) bullshit on mostly egvery mailing-list there are guidlines that you NOT should top-post, try it out on the postfix-list as example and wait what Wietse wille xplain you about both * your top-posting * and your stupid reply all resulting in get answers twice the case where you should be clever enough to do so is when there is a answer below and you post on top in stupidity to surround the question on both sides with answers to make it unable for anybody to read a thread anymore it is suitable if EVERY answer is on top but plain stupid after a answer at the bottom ___ AND YES my business and private communication is ALWAYS top-posting but i was smart enough to accept the it is a guidline at virtual all mailing-lists not do it there signature.asc Description: OpenPGP digital signature
Re: InnoDB vs. other storage engines
Hi! > "Manuel" == Manuel Arostegui writes: Manuel> 2012/9/19 Mark Haney >> I hope this doesn't end in some kind of flame war. I'm looking to >> optimize my tables (and performance in general) of the DB my web app is >> using. I'm tweaking things a little at a time, but I'm curious as to what >> the rest of the MySQL list thinks about changing my storage engine from >> InnoDB to something else so I can optimize the tables on a regular basis. >> >> Is it worth the effort? Any caveats? Manuel> Hi Mark, Manuel> I would depend on what your workload would be. Mostly writes, mostly reads, Manuel> how many writes/reads do you expect etc. Manuel> The best approach, from my point of view, would be, firstly, tune your Manuel> MySQL server (if you've not done it yet) before getting into engine/tables Manuel> optimizations which can be more complicated. InnoDB is a great engine, but not suitable for everything. Depending on your usage, moving some tables to another engine may help. Here is some suggestions (in no particular order): - If you want to have small footprint but don't need commit, foreign keys or explicite rollback then ARIA is an option. http://kb.askmonty.org/en/aria-formerly-known-as-maria/ - Duplicating some data in the MEMORY engine may also be beneficially. - If your problem is a lot of write, then you should take a look at Tokutek. It's an engine that is optimized for a lot of inserts. http://www.tokutek.com/products/tokudb-for-mysql/ - If you want to utilize a lot of computers to analyze BIG data then ScaleDB (http://www.scaledb.com) or InfiniDB (http://infinidb.org/) may be an option. Good luck and please post/blog about your experiences! Regards, Monty Creator of MySQL and MariaDB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql