Re: Why does the limit use the early row lookup.

2012-04-24 Thread Jan Steinman
On 24 Apr 12, at 15:57, mysql-digest-h...@lists.mysql.com wrote: > From: shawn green > > > On 4/22/2012 11:18 PM, Zhangzhigang wrote: >> Why does not the mysql developer team to do this optimization? > > When the Optimizer is told to sort a result set in the order determined > by a random val

Re: Postal code searching

2012-04-24 Thread Grant Allen
That still leaves the question, what are the actual rules/business logic by which you want to group things and get their "abbreviations"? Are you adhering to Royal Mail/Post Office residual selection/direct selection rules, or do you have your own scheme? It seems like the latter ... the RM ru

RE: Why does the limit use the early row lookup.

2012-04-24 Thread Rick James
http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf Let me know if that is not clear enough. > -Original Message- > From: Zhangzhigang [mailto:zzgang_2...@yahoo.com.cn] > Sent: Monday, April 23, 2012 6:56 PM > To: Rick James > Cc: mysql@lists.mysql.com > Subject: RE: Why does the

Re: Postal code searching

2012-04-24 Thread Neil Tompkins
At the moment im concentrating on london postal codes but future would be us zip codes too On 24 Apr 2012, at 18:09, Rick James wrote: > Please be more precise about the rules. In the US, "12345-6789" would become > "12345". This would follow a different rule. > > Is your rule "stop after t

RE: Postal code searching

2012-04-24 Thread Rick James
Please be more precise about the rules. In the US, "12345-6789" would become "12345". This would follow a different rule. Is your rule "stop after the first digit"? That gets quite messy in SQL, and would be better done in an application code. See also http://dev.mysql.com/doc/refman/5.0/en/

Re: Postal code searching

2012-04-24 Thread Lars Nilsson
On Tue, Apr 24, 2012 at 12:24 PM, Tompkins Neil wrote: > How about if I want to only return postal codes that are like W1U 8JE > not W13 0SU. > > Because in this example I have W1 as the postal code and W13 is the other > postal code Perhaps something like following? Though, to be honest, I'm not

Re: Postal code searching

2012-04-24 Thread Gary Smith
On 24/04/2012 17:24, Tompkins Neil wrote: How about if I want to only return postal codes that are like W1U 8JE not W13 0SU. Because in this example I have W1 as the postal code and W13 is the other postal code Then you'd do: like 'W1 %' to return anything starting W1 like 'W13 %' to return an

Re: Postal code searching

2012-04-24 Thread Tompkins Neil
How about if I want to only return postal codes that are like W1U 8JE not W13 0SU. Because in this example I have W1 as the postal code and W13 is the other postal code On Tue, Apr 24, 2012 at 5:18 PM, Gary Smith wrote: > On 24/04/2012 17:16, Gary Smith wrote: > >> http://dev.mysql.com/doc/**r

Re: Postal code searching

2012-04-24 Thread Gary Smith
On 24/04/2012 17:16, Gary Smith wrote: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html Specifically, replace % with _ as this means "match one character" not "match any number of characters". So, you can do: like "W1 %" like "W1_ %" etc. Oh, and you can also get really

Re: Postal code searching

2012-04-24 Thread Gary Smith
On 24/04/2012 17:11, Tompkins Neil wrote: Hi I've a number of different postal codes in a system for example WC1B 5JA WC1H 8EJ W1J 7BX W1H 7DL NW1 1NY I can use like statements for example SELECT * FROM postal_codes WHERE zip LIKE 'W1%' giving me W1J 7BX W1H 7DL In addition I have a number

Postal code searching

2012-04-24 Thread Tompkins Neil
Hi I've a number of different postal codes in a system for example WC1B 5JA WC1H 8EJ W1J 7BX W1H 7DL NW1 1NY I can use like statements for example SELECT * FROM postal_codes WHERE zip LIKE 'W1%' giving me W1J 7BX W1H 7DL In addition I have a number of abbreviated postal codes like W1 WC1 WC2