How to specify CHAR column to accept specific characters
Hi I'm using MySQL 5.1. How do we create a constraint on char column to accept only character 'A' - 'Z'? Thank you! -- Hez
Re: How to specify CHAR column to accept specific characters
Hi, I'm using MySQL 5.1. How do we create a constraint on char column to accept only character 'A' - 'Z'? MySQL doesn't have CHECK constraints, I think the only way to do this is via a BEFORE INSERT trigger. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: myisamchk buffer_size warnings
On Tue, 16 Jun 2009, Thomas Spahni wrote:
Hi
I have MySQL 5.0.64 compiled from source. When I run myisamchk on any
table I get the following warnings:
Warning: option 'key_buffer_size': unsigned value 18446744073709551615
adjusted to 4294963200
Warning: option 'read_buffer_size': unsigned value 18446744073709551615
adjusted to 4294967295
Warning: option 'write_buffer_size': unsigned value 18446744073709551615
adjusted to 4294967295
Warning: option 'sort_buffer_size': unsigned value 18446744073709551615
adjusted to 4294967295
/etc/my.cnf contains the following:
[myisamchk]
key_buffer_size=20971520
sort_buffer_size=20971520
read_buffer_size=2097152
write_buffer_size=2097152
What's wrong here?
Thomas Spahni
Hi all,
Replying to myself as the problem is fixed in the meantime (at least for
me). It turned out to be bug #33785 described here:
http://bugs.mysql.com/bug.php?id=33785
The solution is to fix the source code of myisamchk.c and recompile. The
diff is:
--- myisamchk.c.original2009-06-18 13:01:55.0 +0200
+++ myisamchk.c 2009-06-18 13:17:34.0 +0200
@@ -295,7 +295,7 @@
{ key_buffer_size, OPT_KEY_BUFFER_SIZE, ,
(gptr*) check_param.use_buffers, (gptr*) check_param.use_buffers, 0,
GET_ULONG, REQUIRED_ARG, (long) USE_BUFFER_INIT, (long) MALLOC_OVERHEAD,
-(long) ~0L, (long) MALLOC_OVERHEAD, (long) IO_SIZE, 0},
+(ulong) ~0, (long) MALLOC_OVERHEAD, (long) IO_SIZE, 0},
{ key_cache_block_size, OPT_KEY_CACHE_BLOCK_SIZE, ,
(gptr*) opt_key_cache_block_size,
(gptr*) opt_key_cache_block_size, 0,
@@ -309,17 +309,17 @@
(gptr*) check_param.read_buffer_length,
(gptr*) check_param.read_buffer_length, 0, GET_ULONG, REQUIRED_ARG,
(long) READ_BUFFER_INIT, (long) MALLOC_OVERHEAD,
-(long) ~0L, (long) MALLOC_OVERHEAD, (long) 1L, 0},
+(ulong) ~0, (long) MALLOC_OVERHEAD, (long) 1L, 0},
{ write_buffer_size, OPT_WRITE_BUFFER_SIZE, ,
(gptr*) check_param.write_buffer_length,
(gptr*) check_param.write_buffer_length, 0, GET_ULONG, REQUIRED_ARG,
(long) READ_BUFFER_INIT, (long) MALLOC_OVERHEAD,
-(long) ~0L, (long) MALLOC_OVERHEAD, (long) 1L, 0},
+(ulong) ~0, (long) MALLOC_OVERHEAD, (long) 1L, 0},
{ sort_buffer_size, OPT_SORT_BUFFER_SIZE, ,
(gptr*) check_param.sort_buffer_length,
(gptr*) check_param.sort_buffer_length, 0, GET_ULONG, REQUIRED_ARG,
(long) SORT_BUFFER_INIT, (long) (MIN_SORT_BUFFER + MALLOC_OVERHEAD),
-(long) ~0L, (long) MALLOC_OVERHEAD, (long) 1L, 0},
+(ulong) ~0, (long) MALLOC_OVERHEAD, (long) 1L, 0},
{ sort_key_blocks, OPT_SORT_KEY_BLOCKS, ,
(gptr*) check_param.sort_key_blocks,
(gptr*) check_param.sort_key_blocks, 0, GET_ULONG, REQUIRED_ARG,
Unfortunately MySQL 5.0.64 is packed with SuSE-11.1 and thus a lot of
installations will be broken. Some evil things may happen when you have
less physical memory than the maximum default values as applied by the
broken code.
Thomas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How to specify CHAR column to accept specific characters
-Original Message- From: hezjing [mailto:hezj...@gmail.com] Sent: Thursday, June 18, 2009 5:04 AM To: mysql@lists.mysql.com Subject: How to specify CHAR column to accept specific characters Hi I'm using MySQL 5.1. How do we create a constraint on char column to accept only character 'A' - 'Z'? [JS] Would it be practical to use an ENUM in this case? Thank you! -- Hez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ndbcluster problem
Is there any record limitation in ndbcluster? Because I can't insert more records after it reached 108 records. How to solve this? Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ndbcluster problem
Have you checked the type of column you are using. Depending on what the 108 number means, it could be altering the table to say int or bigint column. If it means total number of records, it does not seem to correspond to a medint value, either signed or not. If it means the record number, your column might have a large start number. Some new companies don't like invoicing starting out at record 1. I have no experience in ndbcluster but I would assume that it has some kind of column limit for performance gains in indexing. Mike O'Krongli Acorg Inc http://www.acorg.com - Original Message - From: sangprabv sangpr...@gmail.com To: mysql@lists.mysql.com Sent: Thursday, June 18, 2009 1:10 PM Subject: ndbcluster problem Is there any record limitation in ndbcluster? Because I can't insert more records after it reached 108 records. How to solve this? Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mike_...@acorg.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ndbcluster problem
That 108 is the total number of records. This caused by the DataMemory directive in the config and I must increase the value. Willy On Thu, 2009-06-18 at 14:10 -0400, Mike OK wrote: Have you checked the type of column you are using. Depending on what the 108 number means, it could be altering the table to say int or bigint column. If it means total number of records, it does not seem to correspond to a medint value, either signed or not. If it means the record number, your column might have a large start number. Some new companies don't like invoicing starting out at record 1. I have no experience in ndbcluster but I would assume that it has some kind of column limit for performance gains in indexing. Mike O'Krongli Acorg Inc http://www.acorg.com - Original Message - From: sangprabv sangpr...@gmail.com To: mysql@lists.mysql.com Sent: Thursday, June 18, 2009 1:10 PM Subject: ndbcluster problem Is there any record limitation in ndbcluster? Because I can't insert more records after it reached 108 records. How to solve this? Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mike_...@acorg.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
I'm converting a PHP app from using Visual FoxPro as the database backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on Mac OSX 10.4. The end application will be deployed cross platform and to both 4.x and 5.x MySQL servers. This query returned 21 records in .27 seconds. SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5 This query returned 21442 records in 1.08 seconds. SELECT custzip FROM customers This query is still running half an hour later, with a Time of 2167 and a State of Sending Data (according to the mysql process list) SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5) When I try to EXPLAIN the query it gives me the following... id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Using where; Using index 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where If it matters both tables are INNODB and both customers.custzip and zipcodes.zip are indexed. We used a program called DBConvert from DMSoft to convert the data so it's exactly the same on both the VFP side and the MySQL side. With all that in mind... VFP returns the exact same query in 5-10 seconds and that includes render time in the web browser. By comparison... the query WHERE id IN (SELECT id FROM phone WHERE phonedate = '2001-01-01' AND phonedate = '2009-06-18') returns almost instantly. I'm at a complete loss... The suggestions I've seen online for optimizing Dependent Subquery's basically revolve around changing it from a sub-query to a join but that would require more re-architecturing than I want to do... (Unless I'm forced) Especially since more than a few of those solutions suggested precalculating the distance between zipcodes which only works if the distances are known (only allow 10, 50 and 100 mile radi for example) Any ideas? Thanks in advance! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
I often find doing the IN (subquery...) is really slow versus doing a join: SELECT cutzip FROM customers INNER JOIN zipcodes ON customers.zipcode=zipcodes.zip WHERE degrees(acos(sin(radians(39.0788994)) * sin(radians(latitude)) + cos(radians(39.0788994)) * cos(radians(latitude)) * cos(radians(-77.1227036-longitude)) ) ) *60 *1.1515 5 That query may have un-matched ()'s, not sure. hard to tell =) Try a join. -jw On Thu, Jun 18, 2009 at 8:06 PM, Matt Neimeyer m...@neimeyer.org wrote: I'm converting a PHP app from using Visual FoxPro as the database backend to using MySQL as the backend. I'm testing on MySQL 4.1.22 on Mac OSX 10.4. The end application will be deployed cross platform and to both 4.x and 5.x MySQL servers. This query returned 21 records in .27 seconds. SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5 This query returned 21442 records in 1.08 seconds. SELECT custzip FROM customers This query is still running half an hour later, with a Time of 2167 and a State of Sending Data (according to the mysql process list) SELECT custzip FROM customers WHERE custzip IN (SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5) When I try to EXPLAIN the query it gives me the following... id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra 1,PRIMARY,customers,index,NULL,cw_custzip,30,NULL,21226,Using where; Using index 2,DEPENDENT SUBQUERY,zipcodes,ALL,NULL,NULL,NULL,NULL,42144,Using where If it matters both tables are INNODB and both customers.custzip and zipcodes.zip are indexed. We used a program called DBConvert from DMSoft to convert the data so it's exactly the same on both the VFP side and the MySQL side. With all that in mind... VFP returns the exact same query in 5-10 seconds and that includes render time in the web browser. By comparison... the query WHERE id IN (SELECT id FROM phone WHERE phonedate = '2001-01-01' AND phonedate = '2009-06-18') returns almost instantly. I'm at a complete loss... The suggestions I've seen online for optimizing Dependent Subquery's basically revolve around changing it from a sub-query to a join but that would require more re-architecturing than I want to do... (Unless I'm forced) Especially since more than a few of those solutions suggested precalculating the distance between zipcodes which only works if the distances are known (only allow 10, 50 and 100 mile radi for example) Any ideas? Thanks in advance! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
How to Optimize distinct with index
Hi, I have a sql : Select distinct user_id from user where key1=value and key2=value2 and key3=value2; I add index on (key1,key2,key3,user_id), this sql use temporary table howevery I have thousands of queries per second. How to optimize it? Anthoer question: Select * from user where user_id in(id1,id2,id3,id4,.) order by use_id; I add index on user_id,but after in,order use temporary table, How to optimize it? Thanks! zhouyanwei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
