Spatial functions
Hi! Can i use MySQL spatial functions directly, to compute distance between two GPS coordinate pairs in WGS84 format? Thank you! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow query using string functions
I haven't had a chance to try that, and this might be totally useless information, but to create a clone of the bigger table (CREATE LIKE and INSERT INTO) takes about 12 seconds. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com >-Original Message- >From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of >Baron Schwartz >Sent: Thursday, May 27, 2010 9:09 AM >To: MySql >Subject: Re: Slow query using string functions > >Jerry, > >On Wed, May 26, 2010 at 5:13 PM, Jerry Schwartz wrote: >> I have a pretty simple query that seems to take a lot longer than it ought >> to >> (over 2 minutes). >> > >I suspect that if you watch Handler_ stats, you'll find that the >EXPLAIN estimate is wrong for some reason and it's accessing many more >rows than you think in the second table, or something similar. In any >case, I'd start by measuring what the query is actually doing, not >what EXPLAIN thinks. What does that show? > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Slow query using string functions
>-Original Message-
>From: Gavin Towey [mailto:gto...@ffn.com]
>Sent: Wednesday, May 26, 2010 7:39 PM
>To: je...@gii.co.jp; mysql@lists.mysql.com
>Subject: RE: Slow query using string functions
>
>Jerry,
>
>Are you sure this is really your explain plan for this query? That's not at
>all what I would expect to see.
>
>Regards,
>Gavin Towey
>
[JS] I'm as sure as I can be. Here's a lot more information:
==
SET @PUBID = (SELECT pub_id FROM pub WHERE pub_code = 'DC');
DROP TEMPORARY TABLE IF EXISTS feed_new;
CREATE TEMPORARY TABLE feed_new (
new_title VARCHAR(255), INDEX (new_title)
);
INSERT INTO feed_new
VALUES
('Automotive Aftermarket in France - Channel Analysis to 2014'),
('Automotive Aftermarket in Germany - Channel Analysis to 2014'),
('Automotive Aftermarket in Italy - Channel Analysis to 2014'),
... about 900 more rows
SELECT
feed_new.new_title AS `New Title FROM Feed`,
prod.prod_pub_prod_id AS `Lib Code FROM DB`,
prod.prod_title AS `Title FROM DB`,
prod.prod_num AS `Prod Num`,
prod.prod_published AS `Published FROM DB`
FROM feed_new JOIN prod
ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) =
LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5)
WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY feed_new.new_title;
==
>explain
-> SELECT
-> feed_new.new_title AS `New Title FROM Feed`,
-> prod.prod_pub_prod_id AS `Lib Code FROM DB`,
-> prod.prod_title AS `Title FROM DB`,
-> prod.prod_num AS `Prod Num`,
-> prod.prod_published AS `Published FROM DB`
-> FROM feed_new JOIN prod
-> ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) =
LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5)
-> WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0
-> ORDER BY feed_new.new_title
-> \G
*** 1. row ***
id: 1
select_type: SIMPLE
table: feed_new
type: index
possible_keys: NULL
key: new_title
key_len: 768
ref: NULL
rows: 1
Extra: Using index
*** 2. row ***
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id,pub_id_2
key: pub_id
key_len: 48
ref: const
rows: 9817
Extra: Using where
2 rows in set (0.00 sec)
==
I assume that pub_id is the best key to use to limit the rows pulled from
prod, since it cuts it down to 9817 rows from 11. I'm guessing that even
though prod_title is a key (MUL), using it in the expression
LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5)
keeps it from being used as a key.
If I replace the expressions in the ON clause with simple matches, I get a
very different result:
>explain
-> SELECT
-> feed_new.new_title AS `New Title FROM Feed`,
-> prod.prod_pub_prod_id AS `Lib Code FROM DB`,
-> prod.prod_title AS `Title FROM DB`,
-> prod.prod_num AS `Prod Num`,
-> prod.prod_published AS `Published FROM DB`
-> FROM feed_new JOIN prod
-> ON feed_new.new_title = prod.prod_title
-> WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0
-> ORDER BY feed_new.new_title
-> \G
*** 1. row **
id: 1
select_type: SIMPLE
table: feed_new
type: index
possible_keys: new_title
key: new_title
key_len: 768
ref: NULL
rows: 882
Extra: Using index
*** 2. row **
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id,prod_title,pub_id_2,prod_title_fulltext
key: prod_title
key_len: 768
ref: giiexpr_db.feed_new.new_title
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
=
Of course, in this case the number of qualifying rows is vastly different; but
I would think the optimization has to happen before the results of the SELECT
are known. No optimizer is that prescient. :-)
I'm not really surprised by the different optimizations, I'm surprised by the
difference in execution time. Using the function-based expressions in the ON
clause takes 2:03.38 minutes to return 267 rows (the first time, the second
time is a little faster); using a simple comparison with no functions takes
.03 seconds to return 1 row.
I benchmarked the expression I'm using
select benchmark(1000,left("asbcdkdfsaklfjdakl;fjasdl;fjasdl;fjasd",
length("asbcdkdfsaklfjdakl;fjasdl;fjasdl;fjasd") - 5));
and the result was 2.87 seconds. That's ten million evaluations.
So where is the time going?
Re: Slow query using string functions
Jerry, On Wed, May 26, 2010 at 5:13 PM, Jerry Schwartz wrote: > I have a pretty simple query that seems to take a lot longer than it ought to > (over 2 minutes). > I suspect that if you watch Handler_ stats, you'll find that the EXPLAIN estimate is wrong for some reason and it's accessing many more rows than you think in the second table, or something similar. In any case, I'd start by measuring what the query is actually doing, not what EXPLAIN thinks. What does that show? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow query using string functions
Hi! Jerry Schwartz wrote: > I have a pretty simple query that seems to take a lot longer than it ought to > (over 2 minutes). > > [[...]] > > SELECT > feed_new.new_title AS `New Title FROM Feed`, > prod.prod_pub_prod_id AS `Lib Code FROM DB`, > prod.prod_title AS `Title FROM DB`, > prod.prod_num AS `Prod Num`, > prod.prod_published AS `Published FROM DB` > FROM feed_new JOIN prod > ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) = > LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5) > WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0 > ORDER BY feed_new.new_title; > > [[...]] > > The query is doing a scan of the 9816 records that have pub_id = @PUBID, but > even so this seems like a long time. Are the built-in string functions really > that slow? The general rule is: If you are not taking the value of a column directly but are applying any function to it (like "LEFT" in your statement), an index cannot be used. So the table needs to be accessed (scanned) and the function computed on each row, to evaluate the predicate (the comparison). I am no expert in checking "explain" output, so I may well be wrong in my guess: I think the execution will scan the whole "feed_new" table (895 records) for each of those 9816 matches of pub_id, so it is doing 8785320 calls of "LEFT()" followed by a string comparison. > > I suspect it would be faster if I built separate tables that had just the > shortened versions of the titles, but I wouldn't think that would be > necessary. So IMO you have two choices: - Either you accept the performance implications of a table scan (which will of course get worse when your data grows), - or you introduce another column in your table in which you store the function result (maintained on INSERT and UPDATE) and create an index on this column. An additional table with the shortened columns is no good idea IMO, because you would need to maintain it in sync with your "real" data. In your example, it should be sufficient to add the new column to table "feed", because your execution strategy should start by evaluating prod.pub_id = @PUBID HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Slow query using string functions
Jerry, Are you sure this is really your explain plan for this query? That's not at all what I would expect to see. Regards, Gavin Towey -Original Message- From: Jerry Schwartz [mailto:je...@gii.co.jp] Sent: Wednesday, May 26, 2010 2:14 PM To: mysql@lists.mysql.com Subject: Slow query using string functions I have a pretty simple query that seems to take a lot longer than it ought to (over 2 minutes). Table `feed_new` has a single VARCHAR(255) column, `new_title`, that is an index. Table `prod` has many fields: `prod_title` and `pub_id` are both indexes (VARCHAR). `feed_new` has 895 records, `prod` has 110432. SELECT feed_new.new_title AS `New Title FROM Feed`, prod.prod_pub_prod_id AS `Lib Code FROM DB`, prod.prod_title AS `Title FROM DB`, prod.prod_num AS `Prod Num`, prod.prod_published AS `Published FROM DB` FROM feed_new JOIN prod ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) = LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5) WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0 ORDER BY feed_new.new_title; *** 1. row *** id: 1 select_type: SIMPLE table: feed_new type: index possible_keys: NULL key: new_title key_len: 768 ref: NULL rows: 1 Extra: Using index *** 2. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: pub_id,pub_id_2 key: pub_id key_len: 48 ref: const rows: 9816 Extra: Using where The query is doing a scan of the 9816 records that have pub_id = @PUBID, but even so this seems like a long time. Are the built-in string functions really that slow? I suspect it would be faster if I built separate tables that had just the shortened versions of the titles, but I wouldn't think that would be necessary. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Slow query using string functions
I have a pretty simple query that seems to take a lot longer than it ought to (over 2 minutes). Table `feed_new` has a single VARCHAR(255) column, `new_title`, that is an index. Table `prod` has many fields: `prod_title` and `pub_id` are both indexes (VARCHAR). `feed_new` has 895 records, `prod` has 110432. SELECT feed_new.new_title AS `New Title FROM Feed`, prod.prod_pub_prod_id AS `Lib Code FROM DB`, prod.prod_title AS `Title FROM DB`, prod.prod_num AS `Prod Num`, prod.prod_published AS `Published FROM DB` FROM feed_new JOIN prod ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) = LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5) WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0 ORDER BY feed_new.new_title; *** 1. row *** id: 1 select_type: SIMPLE table: feed_new type: index possible_keys: NULL key: new_title key_len: 768 ref: NULL rows: 1 Extra: Using index *** 2. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: pub_id,pub_id_2 key: pub_id key_len: 48 ref: const rows: 9816 Extra: Using where The query is doing a scan of the 9816 records that have pub_id = @PUBID, but even so this seems like a long time. Are the built-in string functions really that slow? I suspect it would be faster if I built separate tables that had just the shortened versions of the titles, but I wouldn't think that would be necessary. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.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: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)
It works great for me. After working out the bugs and adding the spatial index I am now searching in the 0.05 second timeframe vs. minutes otherwise. Dan On Sun, 2 May 2010 23:39:41 -0700, Rob Wultsch wrote: >>>> >> >>> On Sat, May 1, 2010 at 11:12 PM, dan wrote: >>>> >> >>> >>>> >> >>>> >>>> >> >>>> Can any one help me with understanding the mysql spatial >>>> >> >>>> functions? >>>> >> I >>>> >> >>>> can >>>> >> >>>> only seem to find bits and pieces of how-to's etc. >>>> >> >>>> >>>> >> >>>> I have an existing table of lat / long data representing unique >>>> >> >>>> boundaries >>>> >> >>>> i.e. rectangles and I want to search the table to find the >>>> rectangle >>>> >> >> that >>>> >> >>>> bounds a specific point. >>>> >> >>>> >>>> >> >>>> Dan >>>> >> >>>> > > > Please at least considered PostGIS. > > In my limited experience all the good GIS people I know use PG. Also > spatial indexes are limited to MyISAM in MySQL, which is a significant > limitation for many users. > > http://postgis.refractions.net/ > > > > > -- > Rob Wultsch > wult...@gmail.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: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)
>>> >> >>> On Sat, May 1, 2010 at 11:12 PM, dan wrote: >>> >> >>> >>> >> >>>> >>> >> >>>> Can any one help me with understanding the mysql spatial >>> >> >>>> functions? >>> >> I >>> >> >>>> can >>> >> >>>> only seem to find bits and pieces of how-to's etc. >>> >> >>>> >>> >> >>>> I have an existing table of lat / long data representing unique >>> >> >>>> boundaries >>> >> >>>> i.e. rectangles and I want to search the table to find the >>> rectangle >>> >> >> that >>> >> >>>> bounds a specific point. >>> >> >>>> >>> >> >>>> Dan >>> >> >>>> Please at least considered PostGIS. In my limited experience all the good GIS people I know use PG. Also spatial indexes are limited to MyISAM in MySQL, which is a significant limitation for many users. http://postgis.refractions.net/ -- Rob Wultsch wult...@gmail.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: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)
Ok... I am close I forgot an extra () in my POLYGON statement:
UPDATE `grid` SET lsd_poly = GeomFromText(CONCAT('POLYGON((',n,' ',e,',
',s,' ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,'))'));
(I also created a new GEOMETRY lsd_poly column rather than the poly POLYGON
one).
Now I need to figure out the rest of it...
Dan
On Sun, 2 May 2010 14:00:16 -0700, Ted Yu wrote:
> Have you declared poly to be of spatial type ?
> Cheers
>
> On Sun, May 2, 2010 at 1:03 PM, dan wrote:
>
>>
>> Tried it but no luck:
>>
>> mysql> UPDATE `grid` SET poly = GeomFromText(CONCAT('POLYGON(',n,'
',e,',
>> ',s,' ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));
>> Query OK, 0 rows affected (2 min 3.86 sec)
>> Rows matched: 7876282 Changed: 0 Warnings: 0
>>
>> mysql> select poly from grid limit 10;
>> +--+
>> | poly |
>> +--+
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> | NULL |
>> +--+
>> 10 rows in set (0.01 sec)
>>
>>
>>
>> On Sun, 2 May 2010 12:54:07 -0700, Ted Yu wrote:
>> > Have you tried replacing GeomFromText in place of PolygonFromText ?
>> >
>> > On Sun, May 2, 2010 at 10:59 AM, dan wrote:
>> >
>> >>
>> >> I am still lost... I tried this:
>> >>
>> >> UPDATE `grid` SET poly = PolygonFromText(CONCAT('POLYGON(',n,' ',e,',
>> >> ',s,'
>> >> ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));
>> >>
>> >> I had my delimiters mixed up and I know my CONCAT worked:
>> >>
>> >> mysql> select CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,',
>> >> ',n,'
>> >> ',w,', ',n,' ',e,')') from grid limit 3;
>> >>
>> >>
>>
>>
+---+
>> >> | CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,', ',n,' ',w,',
>> >> ',n,'
>> >> ',e,')')|
>> >>
>> >>
>>
>>
+---+
>> >> | POLYGON(49.07756615 101.36211395, 49.07390213 101.36211395,
>> 49.07390213
>> >> 101.36764145, 49.07756615 101.36764145, 49.07756615 101.36211395) |
>> >> | POLYGON(49.08123016 101.36211395, 49.07756615 101.36211395,
>> 49.07756615
>> >> 101.36764145, 49.08123016 101.36764145, 49.08123016 101.36211395) |
>> >> | POLYGON(49.08489418 101.36211395, 49.08123016 101.36211395,
>> 49.08123016
>> >> 101.36764145, 49.08489418 101.36764145, 49.08489418 101.36211395) |
>> >>
>> >>
>>
>>
+---+
>> >>
>> >> But after my UPDATE my poly column is still full of NULL values.
>> >>
>> >> Dan
>> >>
>> >> On Sun, 2 May 2010 13:34:14 -0400, Baron Schwartz
>> >> wrote:
>> >> > Dan,
>> >> >
>> >> > I think you are trying to create a polygon based on the values in
>> >> > other columns in the same row. I think these other columns are
>> >> > named
>> >> > `n` and so on.
>> >> >
>> >> > Your mistake is that you are creating a text string,
>> >> > "POLYGON(..)"
>> >> > and embedding column names inside it. That won't work. Those
>> >> > column
>> >> > names are just part of a string. They are not literal values that
>> >> > the
>> >> > POLYGON() function can interpret. You will need to use
Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)
,
>> >> >
>> >> > I think you are trying to create a polygon based on the values in
>> >> > other columns in the same row. I think these other columns are
>> >> > named
>> >> > `n` and so on.
>> >> >
>> >> > Your mistake is that you are creating a text string,
>> >> > "POLYGON(..)"
>> >> > and embedding column names inside it. That won't work. Those
>> >> > column
>> >> > names are just part of a string. They are not literal values that
>> >> > the
>> >> > POLYGON() function can interpret. You will need to use CONCAT() or
>> >> > similar to build a string that POLYGON() can interpret.
>> >> >
>> >> > On Sun, May 2, 2010 at 11:15 AM, dan wrote:
>> >> >>
>> >> >> I have seen that but I am stuck at just populating my POLYGON
>> >> >> column
>> >> >> (poly). I have tried this:
>> >> >>
>> >> >> UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s`
`e`,
>> >> >> `s`
>> >> >> `w`, `n` `w`, `n` `e`)');
>> >> >>
>> >> >> but my poly column just reports back NULL.
>> >> >>
>> >> >> the n, e, s & w columns are decimal lat / long data.
>> >> >>
>> >> >> Dan
>> >> >>
>> >> >> On Sun, 2 May 2010 06:43:13 -0700, Ted Yu
>> wrote:
>> >> >>> I think you may have seen this:
>> >> >>>
>> http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html
>> >> >>>
>> >> >>> On Sat, May 1, 2010 at 11:12 PM, dan wrote:
>> >> >>>
>> >> >>>>
>> >> >>>> Can any one help me with understanding the mysql spatial
>> >> >>>> functions?
>> >> I
>> >> >>>> can
>> >> >>>> only seem to find bits and pieces of how-to's etc.
>> >> >>>>
>> >> >>>> I have an existing table of lat / long data representing unique
>> >> >>>> boundaries
>> >> >>>> i.e. rectangles and I want to search the table to find the
>> rectangle
>> >> >> that
>> >> >>>> bounds a specific point.
>> >> >>>>
>> >> >>>> Dan
>> >> >>>>
>> >> >>>> --
>> >> >>>> MySQL General Mailing List
>> >> >>>> For list archives: http://lists.mysql.com/mysql
>> >> >>>> To unsubscribe:
>> >> >> http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com
>> >> >>>>
>> >> >>>>
>> >> >>
>> >> >> --
>> >> >> MySQL General Mailing List
>> >> >> For list archives: http://lists.mysql.com/mysql
>> >> >> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=ba...@xaprb.com
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Baron Schwartz
>> >> > Percona Inc <http://www.percona.com/>
>> >> > Consulting, Training, Support & Services for MySQL
>> >>
>> >> --
>> >> MySQL General Mailing List
>> >> For list archives: http://lists.mysql.com/mysql
>> >> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.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: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)
Tried it but no luck:
mysql> UPDATE `grid` SET poly = GeomFromText(CONCAT('POLYGON(',n,' ',e,',
',s,' ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));
Query OK, 0 rows affected (2 min 3.86 sec)
Rows matched: 7876282 Changed: 0 Warnings: 0
mysql> select poly from grid limit 10;
+--+
| poly |
+--+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+--+
10 rows in set (0.01 sec)
On Sun, 2 May 2010 12:54:07 -0700, Ted Yu wrote:
> Have you tried replacing GeomFromText in place of PolygonFromText ?
>
> On Sun, May 2, 2010 at 10:59 AM, dan wrote:
>
>>
>> I am still lost... I tried this:
>>
>> UPDATE `grid` SET poly = PolygonFromText(CONCAT('POLYGON(',n,' ',e,',
>> ',s,'
>> ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));
>>
>> I had my delimiters mixed up and I know my CONCAT worked:
>>
>> mysql> select CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,',
>> ',n,'
>> ',w,', ',n,' ',e,')') from grid limit 3;
>>
>>
+---+
>> | CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,', ',n,' ',w,',
>> ',n,'
>> ',e,')')|
>>
>>
+---+
>> | POLYGON(49.07756615 101.36211395, 49.07390213 101.36211395,
49.07390213
>> 101.36764145, 49.07756615 101.36764145, 49.07756615 101.36211395) |
>> | POLYGON(49.08123016 101.36211395, 49.07756615 101.36211395,
49.07756615
>> 101.36764145, 49.08123016 101.36764145, 49.08123016 101.36211395) |
>> | POLYGON(49.08489418 101.36211395, 49.08123016 101.36211395,
49.08123016
>> 101.36764145, 49.08489418 101.36764145, 49.08489418 101.36211395) |
>>
>>
+---+
>>
>> But after my UPDATE my poly column is still full of NULL values.
>>
>> Dan
>>
>> On Sun, 2 May 2010 13:34:14 -0400, Baron Schwartz
>> wrote:
>> > Dan,
>> >
>> > I think you are trying to create a polygon based on the values in
>> > other columns in the same row. I think these other columns are named
>> > `n` and so on.
>> >
>> > Your mistake is that you are creating a text string, "POLYGON(..)"
>> > and embedding column names inside it. That won't work. Those column
>> > names are just part of a string. They are not literal values that the
>> > POLYGON() function can interpret. You will need to use CONCAT() or
>> > similar to build a string that POLYGON() can interpret.
>> >
>> > On Sun, May 2, 2010 at 11:15 AM, dan wrote:
>> >>
>> >> I have seen that but I am stuck at just populating my POLYGON column
>> >> (poly). I have tried this:
>> >>
>> >> UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s` `e`,
>> >> `s`
>> >> `w`, `n` `w`, `n` `e`)');
>> >>
>> >> but my poly column just reports back NULL.
>> >>
>> >> the n, e, s & w columns are decimal lat / long data.
>> >>
>> >> Dan
>> >>
>> >> On Sun, 2 May 2010 06:43:13 -0700, Ted Yu
wrote:
>> >>> I think you may have seen this:
>> >>>
http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html
>> >>>
>> >>> On Sat, May 1, 2010 at 11:12 PM, dan wrote:
>> >>>
>> >>>>
>> >>>> Can any one help me with understanding the mysql spatial functions?
>> I
>> >>>> can
>> >>>> only seem to find bits and pieces of how-to's etc.
>> >>>>
>> >>>> I have an existing table of lat / long data representing unique
>> >>>> boundaries
>> >>>> i.e. rectangles and I want to search the table to find the
rectangle
>> >> that
>> >>>> bounds a specific point.
>> >>>>
>> >>>> Dan
>> >>>>
>> >>>> --
>> >>>> MySQL General Mailing List
>> >>>> For list archives: http://lists.mysql.com/mysql
>> >>>> To unsubscribe:
>> >> http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com
>> >>>>
>> >>>>
>> >>
>> >> --
>> >> MySQL General Mailing List
>> >> For list archives: http://lists.mysql.com/mysql
>> >> To unsubscribe:http://lists.mysql.com/mysql?unsub=ba...@xaprb.com
>> >>
>> >>
>> >
>> >
>> >
>> > --
>> > Baron Schwartz
>> > Percona Inc <http://www.percona.com/>
>> > Consulting, Training, Support & Services for MySQL
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.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: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)
I am still lost... I tried this:
UPDATE `grid` SET poly = PolygonFromText(CONCAT('POLYGON(',n,' ',e,', ',s,'
',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));
I had my delimiters mixed up and I know my CONCAT worked:
mysql> select CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,', ',n,'
',w,', ',n,' ',e,')') from grid limit 3;
+---+
| CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,', ',n,' ',w,', ',n,'
',e,')')|
+---+
| POLYGON(49.07756615 101.36211395, 49.07390213 101.36211395, 49.07390213
101.36764145, 49.07756615 101.36764145, 49.07756615 101.36211395) |
| POLYGON(49.08123016 101.36211395, 49.07756615 101.36211395, 49.07756615
101.36764145, 49.08123016 101.36764145, 49.08123016 101.36211395) |
| POLYGON(49.08489418 101.36211395, 49.08123016 101.36211395, 49.08123016
101.36764145, 49.08489418 101.36764145, 49.08489418 101.36211395) |
+---+
But after my UPDATE my poly column is still full of NULL values.
Dan
On Sun, 2 May 2010 13:34:14 -0400, Baron Schwartz wrote:
> Dan,
>
> I think you are trying to create a polygon based on the values in
> other columns in the same row. I think these other columns are named
> `n` and so on.
>
> Your mistake is that you are creating a text string, "POLYGON(..)"
> and embedding column names inside it. That won't work. Those column
> names are just part of a string. They are not literal values that the
> POLYGON() function can interpret. You will need to use CONCAT() or
> similar to build a string that POLYGON() can interpret.
>
> On Sun, May 2, 2010 at 11:15 AM, dan wrote:
>>
>> I have seen that but I am stuck at just populating my POLYGON column
>> (poly). I have tried this:
>>
>> UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s` `e`, `s`
>> `w`, `n` `w`, `n` `e`)');
>>
>> but my poly column just reports back NULL.
>>
>> the n, e, s & w columns are decimal lat / long data.
>>
>> Dan
>>
>> On Sun, 2 May 2010 06:43:13 -0700, Ted Yu wrote:
>>> I think you may have seen this:
>>> http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html
>>>
>>> On Sat, May 1, 2010 at 11:12 PM, dan wrote:
>>>
>>>>
>>>> Can any one help me with understanding the mysql spatial functions?
I
>>>> can
>>>> only seem to find bits and pieces of how-to's etc.
>>>>
>>>> I have an existing table of lat / long data representing unique
>>>> boundaries
>>>> i.e. rectangles and I want to search the table to find the rectangle
>> that
>>>> bounds a specific point.
>>>>
>>>> Dan
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com
>>>>
>>>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=ba...@xaprb.com
>>
>>
>
>
>
> --
> Baron Schwartz
> Percona Inc <http://www.percona.com/>
> Consulting, Training, Support & Services for MySQL
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)
Dan,
I think you are trying to create a polygon based on the values in
other columns in the same row. I think these other columns are named
`n` and so on.
Your mistake is that you are creating a text string, "POLYGON(..)"
and embedding column names inside it. That won't work. Those column
names are just part of a string. They are not literal values that the
POLYGON() function can interpret. You will need to use CONCAT() or
similar to build a string that POLYGON() can interpret.
On Sun, May 2, 2010 at 11:15 AM, dan wrote:
>
> I have seen that but I am stuck at just populating my POLYGON column
> (poly). I have tried this:
>
> UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s` `e`, `s`
> `w`, `n` `w`, `n` `e`)');
>
> but my poly column just reports back NULL.
>
> the n, e, s & w columns are decimal lat / long data.
>
> Dan
>
> On Sun, 2 May 2010 06:43:13 -0700, Ted Yu wrote:
>> I think you may have seen this:
>> http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html
>>
>> On Sat, May 1, 2010 at 11:12 PM, dan wrote:
>>
>>>
>>> Can any one help me with understanding the mysql spatial functions? I
>>> can
>>> only seem to find bits and pieces of how-to's etc.
>>>
>>> I have an existing table of lat / long data representing unique
>>> boundaries
>>> i.e. rectangles and I want to search the table to find the rectangle
> that
>>> bounds a specific point.
>>>
>>> Dan
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com
>>>
>>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=ba...@xaprb.com
>
>
--
Baron Schwartz
Percona Inc <http://www.percona.com/>
Consulting, Training, Support & Services for MySQL
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)
I have seen that but I am stuck at just populating my POLYGON column
(poly). I have tried this:
UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s` `e`, `s`
`w`, `n` `w`, `n` `e`)');
but my poly column just reports back NULL.
the n, e, s & w columns are decimal lat / long data.
Dan
On Sun, 2 May 2010 06:43:13 -0700, Ted Yu wrote:
> I think you may have seen this:
> http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html
>
> On Sat, May 1, 2010 at 11:12 PM, dan wrote:
>
>>
>> Can any one help me with understanding the mysql spatial functions? I
>> can
>> only seem to find bits and pieces of how-to's etc.
>>
>> I have an existing table of lat / long data representing unique
>> boundaries
>> i.e. rectangles and I want to search the table to find the rectangle
that
>> bounds a specific point.
>>
>> Dan
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com
>>
>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)
Can any one help me with understanding the mysql spatial functions? I can only seem to find bits and pieces of how-to's etc. I have an existing table of lat / long data representing unique boundaries i.e. rectangles and I want to search the table to find the rectangle that bounds a specific point. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL University session on November 19: Memcached Functions for MySQL
Memcached Functions for MySQL http://forge.mysql.com/wiki/Memcached_Functions_for_MySQL_%28UDFs%29 This Thursday (November 19th, 14:00 UTC), Patrick Galbraith will present memcached Functions for MySQL (UDFs). This session is about a suite of functions available to use with MySQL that allow you to store, retrieve and delete data, as well as most of the functions and operations that are available with libmemcached, such as server connectivity to the client, server status, client behaviors, and more. You can combine the fetching of data from one or more tables with the fetching of data from memcached and be able to apply any SQL operations on that result set such as LIMIT, sorting and other conditional operations. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. Here's the schedule for the rest of this year: * November 26: The Spider Storage Engine (Giuseppe Maxia) * December 3: Practical Full-Text Search in MySQL (Bill Karwin) The schedule is not engraved in stone at this point. Please visit http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions for the up-to-date list. On that page, you can also find the starting time for many time zones. Cheers, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.de/mysql mailto: stefan.h...@sun.com Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Custom agregate functions
In the last episode (Jun 30), Dainis Polis said: > Hi MySQL fans! > > Is there way to create subj ? http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html http://dev.mysql.com/doc/refman/5.1/en/adding-udf.html http://dev.mysql.com/doc/refman/5.1/en/udf-aggr-calling.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Custom agregate functions
Hi MySQL fans! Is there way to create subj ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: group by different time period than functions allow
Correction !!! select week(A.mydate),count(A.mystuff) from table A, (SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW, 9 HR) AA) AAA) B Where A.mydate >= B.DT1 And A.mydate < B.DT2 group by week(A.mydate); Give this one a Try !!! Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Rolando Edwards [mailto:redwa...@logicworks.net] Sent: Thursday, June 11, 2009 12:34 PM To: Andrey Dmitriev; mysql@lists.mysql.com Subject: RE: group by different time period than functions allow SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW,9 HR) AA) A; This query will produce the previous Wed at 9AM to the next Wed 9AM. Run it in the MySQL Client and note the output: lw...@localhost (DB information_schema) :: SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW,9 HR) AA) A; +-+-+ | DT1 | DT2 | +-+-+ | 2009-06-10 09:00:00 | 2009-06-17 09:00:00 | +-+-+ 1 row in set (0.00 sec) Note the subquery SELECT 4 DOW,9 HR The DAYOFWEEK function returns 1 for Sun, 2 for Mon, 3 for Tue, 4 for Wed, ..., 7 for Sat If you need Fri to Fri change the DOW to 5 If you need Mon to Mon change the DOW to 2 If you need 3AM to 3AM change the HR to 3 Applying it to your query, it should look something like this select week(A.mydate),count(A.mystuff) from table A, (SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW, 9 HR) AA) AAA) B Where A.mydate >= B. DT1_9AM And A.mydate < B. DT2_9AM group by week(A.mydate); Give it a Try !!! Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Andrey Dmitriev [mailto:admitr...@mentora.biz] Sent: Thursday, June 11, 2009 11:30 AM To: mysql@lists.mysql.com Subject: group by different time period than functions allow Can someone point a link, or show an example. basically, i have something like select week(mydate), count(mystuff) from table group by week(mydate); however, I need week to start on Wed 9am and end next Wed. What's the easiest way to accomplish that? thanks, andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: group by different time period than functions allow
SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW,9 HR) AA) A; This query will produce the previous Wed at 9AM to the next Wed 9AM. Run it in the MySQL Client and note the output: lw...@localhost (DB information_schema) :: SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW,9 HR) AA) A; +-+-+ | DT1 | DT2 | +-+-+ | 2009-06-10 09:00:00 | 2009-06-17 09:00:00 | +-+-+ 1 row in set (0.00 sec) Note the subquery SELECT 4 DOW,9 HR The DAYOFWEEK function returns 1 for Sun, 2 for Mon, 3 for Tue, 4 for Wed, ..., 7 for Sat If you need Fri to Fri change the DOW to 5 If you need Mon to Mon change the DOW to 2 If you need 3AM to 3AM change the HR to 3 Applying it to your query, it should look something like this select week(A.mydate),count(A.mystuff) from table A, (SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW, 9 HR) AA) AAA) B Where A.mydate >= B. DT1_9AM And A.mydate < B. DT2_9AM group by week(A.mydate); Give it a Try !!! Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Andrey Dmitriev [mailto:admitr...@mentora.biz] Sent: Thursday, June 11, 2009 11:30 AM To: mysql@lists.mysql.com Subject: group by different time period than functions allow Can someone point a link, or show an example. basically, i have something like select week(mydate), count(mystuff) from table group by week(mydate); however, I need week to start on Wed 9am and end next Wed. What's the easiest way to accomplish that? thanks, andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
group by different time period than functions allow
Can someone point a link, or show an example. basically, i have something like select week(mydate), count(mystuff) from table group by week(mydate); however, I need week to start on Wed 9am and end next Wed. What's the easiest way to accomplish that? thanks, andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: where is the stored functions STORED?
Hi, Dan Nelson & Rolando Edwards It works, thank you. :-) 2009/3/26 Rolando Edwards : > The table mysql.proc always contains the hard code of stored procedures and > functions. > > Triggers in 5.0 are stored in a .TRG file > Make sure, if you used mysqldump, to include --triggers as a dump option > > Rolando A. Edwards > MySQL DBA (CMDBA) > > 155 Avenue of the Americas, Fifth Floor > New York, NY 10013 > 212-625-5307 (Work) > 201-660-3221 (Cell) > AIM : RolandoLogicWorx > Skype : RolandoLogicWorx > redwa...@logicworks.net > > -Original Message- > From: Cui Shijun [mailto:rancp...@gmail.com] > Sent: Thursday, March 26, 2009 7:44 AM > To: mysql@lists.mysql.com > Subject: where is the stored functions STORED? > > hi, > It seems that the stored functions isn't stored in a seperated file > like trigger does. > Where are them? > If I copy the database directory from a server to another, can I get > the same functions on the both servers? > > Thank you. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: where is the stored functions STORED?
The table mysql.proc always contains the hard code of stored procedures and functions. Triggers in 5.0 are stored in a .TRG file Make sure, if you used mysqldump, to include --triggers as a dump option Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM : RolandoLogicWorx Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Cui Shijun [mailto:rancp...@gmail.com] Sent: Thursday, March 26, 2009 7:44 AM To: mysql@lists.mysql.com Subject: where is the stored functions STORED? hi, It seems that the stored functions isn't stored in a seperated file like trigger does. Where are them? If I copy the database directory from a server to another, can I get the same functions on the both servers? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: where is the stored functions STORED?
In the last episode (Mar 26), Cui Shijun said: > It seems that the stored functions isn't stored in a seperated file > like trigger does. > Where are them? > If I copy the database directory from a server to another, can I get > the same functions on the both servers? They're stored in the mysql.proc table. If you're only copying one database, it might be easier to use "mysqldump --no-create-info --no-data --routines mydb" , which will give you the commands to recreate the stored procedures for the mydb database. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
where is the stored functions STORED?
hi, It seems that the stored functions isn't stored in a seperated file like trigger does. Where are them? If I copy the database directory from a server to another, can I get the same functions on the both servers? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question on default database for stored functions
select get_area(11); ERROR 1305 (42000): FUNCTION test2.get_area does not exist Can someone tell me what I'm doing wrong? Thanks. SELECT dbWhereFunctionWasCreated.get_area(11); PB - Jerry Schwartz wrote: -Original Message- From: blue.trapez...@gmail.com [mailto:blue.trapez...@gmail.com] On Behalf Of Vikram Vaswani Sent: Thursday, December 25, 2008 5:47 AM To: mysql@lists.mysql.com Subject: Question on default database for stored functions Hi According to the MySQL manual, "By default, a routine is associated with the default database.When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates)" However, when I tried accessing a stored routine from another database, I received an error. Output below: mysql> USE test [JS] Stupid question, but do you have a semicolon at the end of the USE statement? mysql> DELIMITER // mysql> CREATE FUNCTION get_area(radius INT) -> RETURNS FLOAT -> BEGIN -> RETURN PI() * radius * radius; -> END -> // Query OK, 0 rows affected (0.13 sec) mysql> DELIMITER ; mysql> USE test2 Database changed mysql> select get_area(11); ERROR 1305 (42000): FUNCTION test2.get_area does not exist Can someone tell me what I'm doing wrong? Thanks. Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.9.19/1853 - Release Date: 12/17/2008 8:31 AM
RE: Question on default database for stored functions
>-Original Message- >From: blue.trapez...@gmail.com [mailto:blue.trapez...@gmail.com] On >Behalf Of Vikram Vaswani >Sent: Thursday, December 25, 2008 5:47 AM >To: mysql@lists.mysql.com >Subject: Question on default database for stored functions > >Hi > >According to the MySQL manual, "By default, a routine is associated with >the default database.When the routine is invoked, an implicit USE >db_name is performed (and undone when the routine terminates)" > >However, when I tried accessing a stored routine from another database, >I received an error. Output below: > >mysql> USE test [JS] Stupid question, but do you have a semicolon at the end of the USE statement? >mysql> DELIMITER // >mysql> CREATE FUNCTION get_area(radius INT) > -> RETURNS FLOAT > -> BEGIN > -> RETURN PI() * radius * radius; > -> END > -> // >Query OK, 0 rows affected (0.13 sec) >mysql> DELIMITER ; >mysql> USE test2 >Database changed >mysql> select get_area(11); >ERROR 1305 (42000): FUNCTION test2.get_area does not exist > >Can someone tell me what I'm doing wrong? Thanks. > >Vikram > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question on default database for stored functions
Hi According to the MySQL manual, "By default, a routine is associated with the default database.When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates)" However, when I tried accessing a stored routine from another database, I received an error. Output below: mysql> USE test mysql> DELIMITER // mysql> CREATE FUNCTION get_area(radius INT) -> RETURNS FLOAT -> BEGIN -> RETURN PI() * radius * radius; -> END -> // Query OK, 0 rows affected (0.13 sec) mysql> DELIMITER ; mysql> USE test2 Database changed mysql> select get_area(11); ERROR 1305 (42000): FUNCTION test2.get_area does not exist Can someone tell me what I'm doing wrong? Thanks. Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: FW: How do I (can I) use aggregate functions inside a select
Why? Because it's Friday and I'm feeling silly :) mysql> SELECT * FROM sales; +--+---++ | company | state | sales | +--+---++ | ABC | AZ| 140.01 | | XYZ | AZ| 17.76 | | ABC | NY| 123.45 | | XYZ | NY| 123.00 | | Widgets Inc. | NY| 45.09 | | ABC | NY| 23.73 | | Widgets Inc. | CA| 97.30 | +--+---++ 7 rows in set (0.00 sec) mysql> SELECT 'each' AS 'By', company AS Company, state AS State, LPAD( SUM(sales), 9, ' ' ) AS Sales, -> LPAD( FORMAT( SUM(sales)*100/(SELECT SUM(sales) FROM sales),2), 9, ' ' ) AS Percent, -> REPEAT( '*', SUM(sales)*50/(SELECT SUM(sales) FROM sales) ) -> AS Graph -> FROM sales -> GROUP BY company, state -> UNION -> SELECT ' company', company, '', LPAD( SUM(sales), 9, ' ' ), -> LPAD( FORMAT( SUM(sales)*100/(SELECT SUM(sales) FROM sales),2), 9, ' ' ), -> REPEAT( '*', SUM(sales)*50/(SELECT SUM(sales) FROM sales) ) -> FROM sales -> GROUP BY company -> UNION -> SELECT ' state', '', state, LPAD( SUM(sales), 9, ' ' ), -> LPAD( FORMAT( SUM(sales)*100/(SELECT SUM(sales) FROM sales),2), 9, ' ' ), -> REPEAT( '*', SUM(sales)*50/(SELECT SUM(sales) FROM sales) ) -> FROM sales -> GROUP BY state -> UNION -> SELECT ' -', '-', '-', REPEAT( '-', 9 ), REPEAT( '-', 9 ), REPEAT( ' ', 50 ) -> UNION -> SELECT '-', '-', '-', REPEAT( '-', 9 ), REPEAT( '-', 9 ), REPEAT( ' ', 50 ) -> ORDER BY 1,2 -> ; +---+--+---+---+---++ | By| Company | State | Sales | Percent | Graph | +---+--+---+---+---++ | company | ABC | |287.19 | 50.35 | * | | company | Widgets Inc. | |142.39 | 24.97 | | | company | XYZ | |140.76 | 24.68 | | | -| -| - | - | - | | | state| | AZ|157.77 | 27.66 | ** | | state| | CA| 97.30 | 17.06 | * | | state| | NY|315.27 | 55.28 | | | - | -| - | - | - | | | each | ABC | AZ|140.01 | 24.55 | | | each | ABC | NY|147.18 | 25.81 | * | | each | Widgets Inc. | CA| 97.30 | 17.06 | * | | each | Widgets Inc. | NY| 45.09 | 7.91 | | | each | XYZ | AZ| 17.76 | 3.11 | ** | | each | XYZ | NY|123.00 | 21.57 | *** | +---+--+---+---+---++ 14 rows in set, 2 warnings (0.01 sec) mysql> -- Just my 0.0002 million dollars worth, Shawn "Where there's duct tape, there's hope." "Perl is the duct tape of the Internet." Hassan Schroeder, Sun's first webmaster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: How do I (can I) use aggregate functions inside a select
Sum() is driven by the group by. I need it to be equivalent to this: Select company, state, sales, sales / (select sum(sales) from sales) as percent from sales Which of course I could just use that, but the select I'm actually working with isn't that simple and if there was some way to do what I asked below, that would be much easier. I've gathered that is really the only option when it comes down to it so I'm working on writing my select using the above syntax. Thanks all! Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: Ian Simpson [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2008 11:15 AM To: David Ruggles Cc: 'mysql' Subject: RE: How do I (can I) use aggregate functions inside a select Hi David, Try Select company, state, sales, sum(sales) / sales as percent >From Sales GROUP BY company, state; Not sure if you always want to group by state; if each company exists in only one state then the group by is irrelevant, if not then it will give you the by-state breakdown. On Fri, 2008-07-25 at 10:53 -0400, David Ruggles wrote: > I get: > Error Code : 1140 > Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is > illegal if there is no GROUP BY clause > > Thanks, > > David Ruggles > CCNA MCSE (NT) CNA A+ > Network Engineer Safe Data, Inc. > (910) 285-7200[EMAIL PROTECTED] > > > > -Original Message- > From: Ananda Kumar [mailto:[EMAIL PROTECTED] > Sent: Friday, July 25, 2008 10:37 AM > To: David Ruggles > Cc: mysql > Subject: Re: How do I (can I) use aggregate functions inside a select > > > yes, u can use the below sql. > > regards > anandkl > > > On 7/25/08, David Ruggles <[EMAIL PROTECTED]> wrote: > > > > I may be approaching this all wrong, but I need to know a percentage of > > total sales within a select statement. > > > > So I can do something like this: > > Select company, state, sales, sum(sales) / sales as percent > > From Sales > > > > > > Thanks, > > > > David Ruggles > > CCNA MCSE (NT) CNA A+ > > Network EngineerSafe Data, Inc. > > (910) 285-7200 [EMAIL PROTECTED] > > > > > > > > -- > > 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 - http://www.avg.com > Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 > 6:51 AM > > > -- Ian Simpson System Administrator MyJobGroup No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 6:51 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I (can I) use aggregate functions inside a select
>-Original Message- >From: David Ruggles [mailto:[EMAIL PROTECTED] >Sent: Friday, July 25, 2008 10:53 AM >To: 'mysql' >Subject: RE: How do I (can I) use aggregate functions inside a select > >I get: >Error Code : 1140 >Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns >is >illegal if there is no GROUP BY clause > [JS] I think you can do what you want with a little bit of modification, but I'm not sure exactly what you want. If what you want is to have the sales % for each company, for example, you could use SELECT MIN(company) AS comp, MIN(state) AS st, SUM(sales) AS comp_tot, SUM(sales) * 100 / (SELECT SUM(sales) FROM sales_table)) AS pct_sales FROM sales_table GROUP BY company, state; I tested a simplified case, but I believe this will work. The use of the MIN function is because you need a group function, and because the MIN and MAX of company is the same through the entire group it works just fine. >Thanks, > >David Ruggles >CCNA MCSE (NT) CNA A+ >Network Engineer Safe Data, Inc. >(910) 285-7200 [EMAIL PROTECTED] > > > >-Original Message- >From: Ananda Kumar [mailto:[EMAIL PROTECTED] >Sent: Friday, July 25, 2008 10:37 AM >To: David Ruggles >Cc: mysql >Subject: Re: How do I (can I) use aggregate functions inside a select > > >yes, u can use the below sql. > >regards >anandkl > > >On 7/25/08, David Ruggles <[EMAIL PROTECTED]> wrote: >> >> I may be approaching this all wrong, but I need to know a percentage >of >> total sales within a select statement. >> >> So I can do something like this: >> Select company, state, sales, sum(sales) / sales as percent >> From Sales >> >> >> Thanks, >> >> David Ruggles >> CCNA MCSE (NT) CNA A+ >> Network EngineerSafe Data, Inc. >> (910) 285-7200 [EMAIL PROTECTED] >> >> >> >> -- >> 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 - http://www.avg.com >Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: >7/25/2008 >6:51 AM > > > >-- >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: How do I (can I) use aggregate functions inside a select
Hi David, Try Select company, state, sales, sum(sales) / sales as percent >From Sales GROUP BY company, state; Not sure if you always want to group by state; if each company exists in only one state then the group by is irrelevant, if not then it will give you the by-state breakdown. On Fri, 2008-07-25 at 10:53 -0400, David Ruggles wrote: > I get: > Error Code : 1140 > Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is > illegal if there is no GROUP BY clause > > Thanks, > > David Ruggles > CCNA MCSE (NT) CNA A+ > Network Engineer Safe Data, Inc. > (910) 285-7200[EMAIL PROTECTED] > > > > -Original Message- > From: Ananda Kumar [mailto:[EMAIL PROTECTED] > Sent: Friday, July 25, 2008 10:37 AM > To: David Ruggles > Cc: mysql > Subject: Re: How do I (can I) use aggregate functions inside a select > > > yes, u can use the below sql. > > regards > anandkl > > > On 7/25/08, David Ruggles <[EMAIL PROTECTED]> wrote: > > > > I may be approaching this all wrong, but I need to know a percentage of > > total sales within a select statement. > > > > So I can do something like this: > > Select company, state, sales, sum(sales) / sales as percent > > From Sales > > > > > > Thanks, > > > > David Ruggles > > CCNA MCSE (NT) CNA A+ > > Network EngineerSafe Data, Inc. > > (910) 285-7200 [EMAIL PROTECTED] > > > > > > > > -- > > 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 - http://www.avg.com > Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 > 6:51 AM > > > -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I (can I) use aggregate functions inside a select
David Ruggles wrote: > I may be approaching this all wrong, but I need to know a percentage of > total sales within a select statement. > > So I can do something like this: > Select company, state, sales, sum(sales) / sales as percent > From Sales > > mmh, you want sum(sales where company="foo")/sum(sales) you can do this only when doing 2 queries and storing al least one result re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I (can I) use aggregate functions inside a select
I get: Error Code : 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2008 10:37 AM To: David Ruggles Cc: mysql Subject: Re: How do I (can I) use aggregate functions inside a select yes, u can use the below sql. regards anandkl On 7/25/08, David Ruggles <[EMAIL PROTECTED]> wrote: > > I may be approaching this all wrong, but I need to know a percentage of > total sales within a select statement. > > So I can do something like this: > Select company, state, sales, sum(sales) / sales as percent > From Sales > > > Thanks, > > David Ruggles > CCNA MCSE (NT) CNA A+ > Network EngineerSafe Data, Inc. > (910) 285-7200 [EMAIL PROTECTED] > > > > -- > 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 - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 6:51 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I (can I) use aggregate functions inside a select
yes, u can use the below sql. regards anandkl On 7/25/08, David Ruggles <[EMAIL PROTECTED]> wrote: > > I may be approaching this all wrong, but I need to know a percentage of > total sales within a select statement. > > So I can do something like this: > Select company, state, sales, sum(sales) / sales as percent > From Sales > > > Thanks, > > David Ruggles > CCNA MCSE (NT) CNA A+ > Network EngineerSafe Data, Inc. > (910) 285-7200 [EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >
How do I (can I) use aggregate functions inside a select
I may be approaching this all wrong, but I need to know a percentage of total sales within a select statement. So I can do something like this: Select company, state, sales, sum(sales) / sales as percent >From Sales Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Date Functions in Where Clause
Hi, On Mon, Apr 14, 2008 at 1:54 PM, Jamie Madill <[EMAIL PROTECTED]> wrote: > Hello, > > Basically I want to know if this is a good query for indexing. > > I have the following query: > > select > count(1) as count > from > session > where > last >= DATE_SUB(NOW(), INTERVAL :from SECOND) > > Is it safe to assume that the expression calling the function DATE_SUB > is evaluated just once to a fixed date? Yes, that's correct. -- Baron Schwartz, Senior Consultant, Percona Inc. Tel: +1 888 401 3401 ext 507 24/7 Emergency Line +1 888 401 3401 ext 911 Our Services: http://www.percona.com/services.html Our Blog: http://www.mysqlperformanceblog.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using Date Functions in Where Clause
Hello, Basically I want to know if this is a good query for indexing. I have the following query: select count(1) as count from session where last >= DATE_SUB(NOW(), INTERVAL :from SECOND) Is it safe to assume that the expression calling the function DATE_SUB is evaluated just once to a fixed date? Thanks Jamie Madill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL FUNCTIONS
On Mon, 10 Mar 2008, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: While i was going through mysql reference manual. I saw that "A query cannot be cached if it contains any of the functions shown below" ... NOW() On my production server, the following query is being used. select * from student where regis_date=now(); Then what should i do so that the query get cached. <http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/> is a paper that explains a bit about MySQL caching. It starts First let me clarify what MySQL Query Cache is - I've seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache - meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache the plan but full result sets. That appears to be an expansion of the official text at <http://dev.mysql.com/doc/refman/5.0/en/query-cache.html>, which is The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. (6.0's page has the same sentence.) That leads me to think that the only way to cache the proposed query would be to stop time. Otherwise, the result of running NOW() will change from run to run, causing a different result set. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL FUNCTIONS
Krishna Chandra Prajapati schrieb: Hi All, While i was going through mysql reference manual. I saw that "A query cannot be cached if it contains any of the functions shown below" BENCHMARK() CONNECTION_ID() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME()DATABASE() ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK() LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT() NOW() RAND() RELEASE_LOCK() UNIX_TIMESTAMP() with no paramet- SLEEP() SYSDATE() USER() On my production server, the following query is being used. select * from student where regis_date=now(); Then what should i do so that the query get cached. this would be like a time service would record once the current time, and than always just send this recorded time ... wired, not? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL FUNCTIONS
Hi All, While i was going through mysql reference manual. I saw that "A query cannot be cached if it contains any of the functions shown below" BENCHMARK() CONNECTION_ID() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME()DATABASE() ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK() LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT() NOW() RAND() RELEASE_LOCK() UNIX_TIMESTAMP() with no paramet- SLEEP() SYSDATE() USER() On my production server, the following query is being used. select * from student where regis_date=now(); Then what should i do so that the query get cached. Thanks, Prajapati
Can anybody please let me know the counterpart functions for SetSRID(),Makebox2D(),Distance_Sphere() of PostGIS in MySQL
Hello
I want to use functions - SetSRID(),Makebox2D(),Distance_Sphere() of
Post GIS in MySQL. Can please let me know the similar functions in
MySQL.
Environment
===
Operating System : Red Hat Linux 3.4
Database :
MySQL Version 5.0.51
PostgreSQL 8.2.6 with PostGIS 1.2.1
Functions which we are trying to use
a) To retreive the distance between two geometries (co-ordinates).
Basically trying to find and use a function in MySQL similar to
distance_sphere in PostGIS
Query Used
===
SELECT X(s.geom),Y(s.geom),todofuken||shigun||kuchoson AS address,
(distance_sphere(s.geom,GeomFromText('POINT(135 35)',4326))) AS distance
FROM todofuken_tbl t,shikuchoson_tbl s.
Error Occurred
==
ERROR 1305 (42000): FUNCTION .DISTANCE _SPHERE does not exist
b) To find and use a function in MySQL similar to MakeBox2D function of
PostGIS
Query Used
===
select MakeBox2D(GeomFromText('POINT(135.0
34.0)',4326),GeomFromText('POINT(135.5 34.5)',4326));
Error Occurred
==
ERROR 1305 (42000): FUNCTION blog_gisdb.MakeBox2D does not exist
c) To find and use a function in MySQL similar to MakeBox2D function of
PostGIS
Query Used
===
SELECT uri,X(geom),Y(geom),ROUND(dist,2) AS distance FROM (
SELECT
uri,geom,distance(geom,GeomFromText('POINT(135.25 34.25)',4326)) AS dist
FROM geom_tbl g,uri_tbl u WHERE g.id=u.id
AND geom && SetSRID(MakeBox2D(GeomFromText('POINT(135.0
34.0)',4326),GeomFromText('POINT(135.5 34.5)',4326)),4326)
) AS d ORDER BY dist;
Error Occurred
==
ERROR 1305 (42000): FUNCTION blog_gisdb.SetSRID does not exist
Can somebody please let me know if similar functions exist and if they
exist, please provide me the names of those functions.
Regards
Ahmad
Please do not print this email unless it is absolutely necessary. Spread
environmental awareness.
The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain proprietary, confidential or privileged information. If you are not the
intended recipient, you should not disseminate, distribute or copy this e-mail.
Please notify the sender immediately and destroy all copies of this message and
any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should
check this email and any attachments for the presence of viruses. The company
accepts no liability for any damage caused by any virus transmitted by this
email.
www.wipro.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexing tables using my owns functions
> >, as far as i can see, from mysql 5.0 and upper it is possible create >index using functions. > >http://www.faqs.org/docs/ppbook/r24254.htm > >But i keep having problems with the exemple from the link. Is there any bug >in mysql 5.0.24a-log? The above website says: "Practical PostgreSQL" I cannot find "MySQL" anywhere on that page. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development 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/[EMAIL PROTECTED]
Re: indexing tables using my owns functions
, as far as i can see, from mysql 5.0 and upper it is possible create index using functions. http://www.faqs.org/docs/ppbook/r24254.htm But i keep having problems with the exemple from the link. Is there any bug in mysql 5.0.24a-log? 2007/11/13, Martijn Tonies <[EMAIL PROTECTED]>: >mysql> create index AA on precalc (IDR(P1,P4,P6,P7,P9,'HLA-DRB13')); > > > >But i Get the following error: > > > >ERROR 1064 (42000): 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 ''P1','P4','P6','P7','P9','HLA-DRB13'))' at line 1 > > > >Some one knows where is the error? > > As far as I can see ( > http://dev.mysql.com/doc/refman/5.0/en/create-index.html ) > you can only use columns, not a function. > > Martijn Tonies > Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle > & > MS SQL Server > Upscene Productions > http://www.upscene.com > My thoughts: > http://blog.upscene.com/martijn/ > Database development 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/[EMAIL PROTECTED] > > -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
indexing tables using my owns functions
Hi I've created a function that return a float value the code for it is : create function IDR(pin1 varchar(20),pin4 varchar(20),pin6 varchar(20),pin7 varchar(20),pin9 varchar(20),MOL varchar(20)) returns float DETERMINISTIC begin declare output float; declare P1 float; declare P4 float; declare P6 float; declare P7 float; declare P9 float; select VALUE into P1 from PSSMS where AA=pin1 and POS='1' and MOLEC=MOL; select VALUE into P4 from PSSMS where AA=pin4 and POS='4' and MOLEC=MOL; select VALUE into P6 from PSSMS where AA=pin6 and POS='6' and MOLEC=MOL; select VALUE into P7 from PSSMS where AA=pin7 and POS='7' and MOLEC=MOL; select VALUE into P9 from PSSMS where AA=pin9 and POS='9' and MOLEC=MOL; select P1+P4+P6+P7+P9 into output; return output; end // And it works, now, i would like index a table using this function. The table description is: mysql> describe precalc; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(6)| NO | PRI | NULL| auto_increment | | P1| char(1) | YES || NULL|| | P4| char(1) | YES || NULL|| | P6| char(1) | YES || NULL|| | P7| char(1) | YES || NULL|| | P9| char(1) | YES || NULL|| +---+-+--+-+-++ 6 rows in set (0.01 sec) and i try index by the following command: mysql> create index AA on precalc (IDR(P1,P4,P6,P7,P9,'HLA-DRB13')); But i Get the following error: ERROR 1064 (42000): 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 ''P1','P4','P6','P7','P9','HLA-DRB13'))' at line 1 Some one knows where is the error? Thanks Pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED] -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED] -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: indexing tables using my owns functions
>mysql> create index AA on precalc (IDR(P1,P4,P6,P7,P9,'HLA-DRB13')); > >But i Get the following error: > >ERROR 1064 (42000): 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 ''P1','P4','P6','P7','P9','HLA-DRB13'))' at line 1 > >Some one knows where is the error? As far as I can see ( http://dev.mysql.com/doc/refman/5.0/en/create-index.html ) you can only use columns, not a function. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development 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/[EMAIL PROTECTED]
indexing tables using my owns functions
Hi I've created a function that return a float value the code for it is : create function IDR(pin1 varchar(20),pin4 varchar(20),pin6 varchar(20),pin7 varchar(20),pin9 varchar(20),MOL varchar(20)) returns float DETERMINISTIC begin declare output float; declare P1 float; declare P4 float; declare P6 float; declare P7 float; declare P9 float; select VALUE into P1 from PSSMS where AA=pin1 and POS='1' and MOLEC=MOL; select VALUE into P4 from PSSMS where AA=pin4 and POS='4' and MOLEC=MOL; select VALUE into P6 from PSSMS where AA=pin6 and POS='6' and MOLEC=MOL; select VALUE into P7 from PSSMS where AA=pin7 and POS='7' and MOLEC=MOL; select VALUE into P9 from PSSMS where AA=pin9 and POS='9' and MOLEC=MOL; select P1+P4+P6+P7+P9 into output; return output; end // And it works, now, i would like index a table using this function. The table description is: mysql> describe precalc; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(6)| NO | PRI | NULL| auto_increment | | P1| char(1) | YES || NULL|| | P4| char(1) | YES || NULL|| | P6| char(1) | YES || NULL|| | P7| char(1) | YES || NULL|| | P9| char(1) | YES || NULL|| +---+-+--+-+-++ 6 rows in set (0.01 sec) and i try index by the following command: mysql> create index AA on precalc (IDR(P1,P4,P6,P7,P9,'HLA-DRB13')); But i Get the following error: ERROR 1064 (42000): 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 ''P1','P4','P6','P7','P9','HLA-DRB13'))' at line 1 Some one knows where is the error? Thanks Pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED] -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
declaration of constants in MySQL functions
Hello, is there a possibility to declare constants in MySQL functions? declare test_name varchar(50) DEFAULT 'test'; The variable test_name here ist changeable but it should not be. Oracle syntax: test_name CONSTANT VARCHAR2(50) := 'test'; Regards, Spiker -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why doesn't MySQL support gzip encode/decode functions.
Why doesn't mysql support gzip for COMPRESS/UNCOMPRESS and only zlib For network applications zlib is a lot less compatible than gzip. For example I could send gzip'd content directly from the database within a larger gzip'd stream. Kevin -- Founder/CEO Tailrank.com Location: San Francisco, CA AIM/YIM: sfburtonator Skype: burtonator Blog: feedblog.org Cell: 415-637-8078
Re: Arbitrary Boolean Functions as Relational Database Structure?
This seems like a simple query to me. Correct me if I am wrong but as
I understand it you want to get a list of SwReleases that a user has
access to. Would something like this not work
SELECT s.ID, s.Name
FROM SwRelease as s
WHERE s.ID IN (
SELECT SwID
FROM GroupSwRel-- this table is the group to SwRelease relation ship
WHERE GroupID IN (
SELECT GroupID
FROM UserGroupRel -- this table is the user to group relationship.
WHERE UserID = 'someuserid'))
I've never done a query with a sub query in a sub query but it seems
like it should work to me. In fact, if I'm not mistaken you may be able
to rewrite this just using joins.
Chris W
David T. Ashley wrote:
Hi,
I'm implementing a software release database. Users may aribtrarily be
members of groups (a many-to-many mapping), and each software release may
contain multiple files.
I'd like to allow users the maximum flexibility in deciding who may view
what software releases. The most obvious approach is to allow
specification
in the form of "Release X may be viewed by Users in Group Y or Group
Z", per
release.
In this case, the database design would be something like:
[Users] (many:many) [Groups] (many:many) [SwReleases] (1:many) [Files]
The many:many relationship between groups and software releases
specifies a
Boolean function, of the form "is in Group X or is in Group Y or ...".
Since one knows the user who is logged in (for a web database), one
can do
an outer join and quickly find all the software releases that the user
may
view. I believe one can do it in O(log N) time.
However, the Boolean function is of a fairly special form ("is in
Group X or
is in Group Y ..."). This is the only form where it seems to
translate to
an SQL query naturally.
Here is my question:
Is there any interesting way to structure a database so that other
forms of
permissions can be specified and translate directly into SQL queries?
For example, what if, for a software release, one says, "to view this
software release, a user must be in Group X or Group Y, but not in
Group Z
and not user Q"? Is there a database structure and a corresponding
O(log N)
query that will quickly find for a given user what software releases
may be
viewed?
Thanks.
--
Chris W
KE5GIX
"Protect your digital freedom and privacy, eliminate DRM,
learn more at http://www.defectivebydesign.org/what_is_drm";
Gift Giving Made Easy
Get the gifts you want &
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Arbitrary Boolean Functions as Relational Database Structure?
Hi David,
David T. Ashley wrote:
Hi,
I'm implementing a software release database. Users may aribtrarily be
members of groups (a many-to-many mapping), and each software release may
contain multiple files.
I'd like to allow users the maximum flexibility in deciding who may view
what software releases. The most obvious approach is to allow
specification
in the form of "Release X may be viewed by Users in Group Y or Group Z",
per
release.
In this case, the database design would be something like:
[Users] (many:many) [Groups] (many:many) [SwReleases] (1:many) [Files]
The many:many relationship between groups and software releases specifies a
Boolean function, of the form "is in Group X or is in Group Y or ...".
Since one knows the user who is logged in (for a web database), one can do
an outer join and quickly find all the software releases that the user may
view. I believe one can do it in O(log N) time.
However, the Boolean function is of a fairly special form ("is in Group
X or
is in Group Y ..."). This is the only form where it seems to translate to
an SQL query naturally.
Here is my question:
Is there any interesting way to structure a database so that other forms of
permissions can be specified and translate directly into SQL queries?
For example, what if, for a software release, one says, "to view this
software release, a user must be in Group X or Group Y, but not in Group Z
and not user Q"? Is there a database structure and a corresponding
O(log N)
query that will quickly find for a given user what software releases may be
viewed?
I have developed such a system over the past 7 years or so. It is quite complex
to explain, but it's really simple when you get down to it. I wrote a two-part
series about it on my blog:
http://www.xaprb.com/blog/2006/08/16/how-to-build-role-based-access-control-in-sql/
http://www.xaprb.com/blog/2006/08/18/role-based-access-control-in-sql-part-2/
From your description of the problem, I would say a subset of my solution could
fit your needs exactly, and be about as simple and efficient as I believe is
possible.
Cheers
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Arbitrary Boolean Functions as Relational Database Structure?
Hi,
I'm implementing a software release database. Users may aribtrarily be
members of groups (a many-to-many mapping), and each software release may
contain multiple files.
I'd like to allow users the maximum flexibility in deciding who may view
what software releases. The most obvious approach is to allow specification
in the form of "Release X may be viewed by Users in Group Y or Group Z", per
release.
In this case, the database design would be something like:
[Users] (many:many) [Groups] (many:many) [SwReleases] (1:many) [Files]
The many:many relationship between groups and software releases specifies a
Boolean function, of the form "is in Group X or is in Group Y or ...".
Since one knows the user who is logged in (for a web database), one can do
an outer join and quickly find all the software releases that the user may
view. I believe one can do it in O(log N) time.
However, the Boolean function is of a fairly special form ("is in Group X or
is in Group Y ..."). This is the only form where it seems to translate to
an SQL query naturally.
Here is my question:
Is there any interesting way to structure a database so that other forms of
permissions can be specified and translate directly into SQL queries?
For example, what if, for a software release, one says, "to view this
software release, a user must be in Group X or Group Y, but not in Group Z
and not user Q"? Is there a database structure and a corresponding O(log N)
query that will quickly find for a given user what software releases may be
viewed?
Thanks.
Re: Network address functions in MySQL?
Hello, Good post, but not what i'm looking for. Well, Postgresql seems to be the winner, at least for handling network addresses. (Don't take me wrong. I'm not saying Postgresql is better RDBMS) Thanks. On 5/15/07, Baron Schwartz <[EMAIL PROTECTED]> wrote: Hi, js wrote: > Hi Baron. > Thanks for reply. > > If I understand correctly, > inet_ntoa() and inet_aton() are not capable of handling CIDR notation. Very true, I didn't quite understand the syntax you were using. But you can still use bitwise arithmetic to work around this. Scott Noyes wrote a nice post with examples: http://thenoyes.com/littlenoise/?p=49 > On 5/15/07, Baron Schwartz <[EMAIL PROTECTED]> wrote: >> Hello, >> >> js wrote: >> > Hi. >> > >> > Today I found postgresql's neat feature, inet operators, >> > which allows you to do >> > >> > inet '192.168.1/24' >> inet '192.168.1.5' >> > >> > http://www.postgresql.org/docs/current/static/functions-net.html >> > >> > Is there anyway to do this using MySQL? >> >> Yes. Have a look at the inet_ntoa() and inet_aton() functions. >> >> Cheers >> Baron >> -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Network address functions in MySQL?
Hi, js wrote: Hi Baron. Thanks for reply. If I understand correctly, inet_ntoa() and inet_aton() are not capable of handling CIDR notation. Very true, I didn't quite understand the syntax you were using. But you can still use bitwise arithmetic to work around this. Scott Noyes wrote a nice post with examples: http://thenoyes.com/littlenoise/?p=49 On 5/15/07, Baron Schwartz <[EMAIL PROTECTED]> wrote: Hello, js wrote: > Hi. > > Today I found postgresql's neat feature, inet operators, > which allows you to do > > inet '192.168.1/24' >> inet '192.168.1.5' > > http://www.postgresql.org/docs/current/static/functions-net.html > > Is there anyway to do this using MySQL? Yes. Have a look at the inet_ntoa() and inet_aton() functions. Cheers Baron -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Network address functions in MySQL?
> -Original Message- > From: js [mailto:[EMAIL PROTECTED] > Sent: 15 May 2007 15:31 > To: MySQL List > Subject: Network address functions in MySQL? > > > Hi. > > Today I found postgresql's neat feature, inet operators, > which allows you to do > > inet '192.168.1/24' >> inet '192.168.1.5' > > http://www.postgresql.org/docs/current/static/functions-net.html > > Is there anyway to do this using MySQL? > Although it wouldn't be as need as using the >> and similar operators, you could achieve the same with MySQL's INET_ATON and INET_NTOA functions. Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Network address functions in MySQL?
Hi Baron. Thanks for reply. If I understand correctly, inet_ntoa() and inet_aton() are not capable of handling CIDR notation. On 5/15/07, Baron Schwartz <[EMAIL PROTECTED]> wrote: Hello, js wrote: > Hi. > > Today I found postgresql's neat feature, inet operators, > which allows you to do > > inet '192.168.1/24' >> inet '192.168.1.5' > > http://www.postgresql.org/docs/current/static/functions-net.html > > Is there anyway to do this using MySQL? Yes. Have a look at the inet_ntoa() and inet_aton() functions. Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Network address functions in MySQL?
Hello, js wrote: Hi. Today I found postgresql's neat feature, inet operators, which allows you to do inet '192.168.1/24' >> inet '192.168.1.5' http://www.postgresql.org/docs/current/static/functions-net.html Is there anyway to do this using MySQL? Yes. Have a look at the inet_ntoa() and inet_aton() functions. Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Network address functions in MySQL?
Hi. Today I found postgresql's neat feature, inet operators, which allows you to do inet '192.168.1/24' >> inet '192.168.1.5' http://www.postgresql.org/docs/current/static/functions-net.html Is there anyway to do this using MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Functions and NULL with standard install
Hello Lucas, [EMAIL PROTECTED] wrote: I don't see how to use this here, I will have to research the |/||/| select rpad(|IFNULL(|null, ''),5,'1'); |/||/| The function you want to use is IFNULL() documented here:http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Try it as he wrote it just without the pipes (|): SELECT RPAD(IFNULL(column_name, ''),5,'1'); Another option to the IFNULL and CASE is the COALESCE function: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN Join us at the 2007 MySQL Conference and Exposition April 23 to 26 - Santa Clara, California, USA http://www.mysqlconf.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Functions and NULL with standard install
I don't see how to use this here, I will have to research the |/||/| select rpad(|IFNULL(|null, ''),5,'1'); |/||/| but this below works thank you SELECT RPAD(CASE WHEN NULL IS NULL THEN '' ELSE '2' END, 5, '1'); If you are using it on an actual field, you would replace the first NULL and the '2' with the field to be checked. On 3/21/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > I am running a basic install of MySQL 5.0 with "strict mode" turned on I > would like to use RPAD however at times a NULL var will be sent to the > function. My goal is to have a function that will convert the NULL to a > blank string to get the result of EXAMPLE3 if a NULL is returned. > > > EXAMPLE 1 > > mysql> select rpad(null,5,'1'); > +--+ > | rpad(null,5,'1') | > +--+ > | NULL | > +--+ > 1 row in set (0.00 sec) Try this:
Re: Functions and NULL with standard install
select rpad(|IFNULL(|null, ''),5,'1');
|/||/|
[EMAIL PROTECTED] ha scritto:
I am running a basic install of MySQL 5.0 with "strict mode" turned on I
would like to use RPAD however at times a NULL var will be sent to the
function. My goal is to have a function that will convert the NULL to a
blank string to get the result of EXAMPLE3 if a NULL is returned.
EXAMPLE 1
mysql> select rpad(null,5,'1');
+--+
| rpad(null,5,'1') |
+--+
| NULL |
+--+
1 row in set (0.00 sec)
EXAMPLE 2
mysql> select rpad('0',5,'1');
+-+
| rpad('0',5,'1') |
+-+
| 0 |
+-+
1 row in set (0.00 sec)
mysql>
EXAMPLE 3
mysql> select rpad('',5,'1');
++
| rpad('',5,'1') |
++
| 1 |
++
1 row in set (0.02 sec)
Is there a function I can use to convert the null string to a blank string
before it is sent to RPAD in a basic installation of MySQL or will I need
to create a function?
Wishing you the best you know you deserve,
__
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ 08405
Phone 609.485.5401
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Functions and NULL with standard install
On 3/21/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I am running a basic install of MySQL 5.0 with "strict mode" turned on I would like to use RPAD however at times a NULL var will be sent to the function. My goal is to have a function that will convert the NULL to a blank string to get the result of EXAMPLE3 if a NULL is returned. EXAMPLE 1 mysql> select rpad(null,5,'1'); +--+ | rpad(null,5,'1') | +--+ | NULL | +--+ 1 row in set (0.00 sec) Try this: SELECT RPAD(CASE WHEN NULL IS NULL THEN '' ELSE '2' END, 5, '1'); If you are using it on an actual field, you would replace the first NULL and the '2' with the field to be checked.
Functions and NULL with standard install
I am running a basic install of MySQL 5.0 with "strict mode" turned on I
would like to use RPAD however at times a NULL var will be sent to the
function. My goal is to have a function that will convert the NULL to a
blank string to get the result of EXAMPLE3 if a NULL is returned.
EXAMPLE 1
mysql> select rpad(null,5,'1');
+--+
| rpad(null,5,'1') |
+--+
| NULL |
+--+
1 row in set (0.00 sec)
EXAMPLE 2
mysql> select rpad('0',5,'1');
+-+
| rpad('0',5,'1') |
+-+
| 0 |
+-+
1 row in set (0.00 sec)
mysql>
EXAMPLE 3
mysql> select rpad('',5,'1');
++
| rpad('',5,'1') |
++
| 1 |
++
1 row in set (0.02 sec)
Is there a function I can use to convert the null string to a blank string
before it is sent to RPAD in a basic installation of MySQL or will I need
to create a function?
Wishing you the best you know you deserve,
__
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ 08405
Phone 609.485.5401
Re: How to SELECT rows closest to lat/lng -- USING SPATIAL FUNCTIONS
Thanks! That really seems to do the trick. Amazing. Now, for some
stupid questions: The values 69.1 and 57.3... are you aware of some
documentation or tutorial explaining these values and the math? I'd
like to understand why it works.
Also, I would like to figure out how to, if possible, speed this up.
The query takes ~3.5 on a table of 800,000+ records--which is good
considering everything (e.g., that it works, and there's no index here).
Can anyone think of how this might run faster, either via spatial
extensions, or some other silver bullet? (Also, it appears the
distance value is returned as KMs--is that right?)
...Rene
On 3-Feb-07, at 7:27 AM, Lars Schwarz wrote:
haven't followed the complete thread so i don't know what you're
after, but maybe this helps?
SELECT latitude, longitude, SQRT( POW( 69.1 * ( latitude - [startlat]
) , 2 ) + POW( 69.1 * ( [startlng] - longitude ) * COS( latitude /
57.3 ) , 2 ) ) AS distance FROM foobar ORDER BY distance ASC
where [starlat] and [startlng] is the position where to start
messuring the distance.
On 2/3/07, M5 <[EMAIL PROTECTED]> wrote:
Thanks for the reply... It returns results, but not exactly what I am
hoping for--basically, I get the nearest latitudes (but not near
longitude), and nearest longitude (but not near latitudes). In other
words, it doesn't return the closest lat/lng pair... Also, the query
takes about 8 seconds to run, so maybe there's a better way?
I've been looking at the spatial extensions, and made some progress.
I created a new column of type POINT. Here's the table definition:
CREATE TABLE `map_data` (
`id` int(10) unsigned NOT NULL auto_increment,
`meridian` tinyint(3) unsigned NOT NULL,
`range` tinyint(3) unsigned NOT NULL,
`township` tinyint(3) unsigned NOT NULL,
`section` tinyint(3) unsigned NOT NULL,
`quartersection` varchar(2) collate latin1_general_ci NOT NULL,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point default NULL,
PRIMARY KEY (`id`),
KEY `latlng` (`latitude`,`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=0 ;
...And then reloaded the ~800,000 records into the table (and adding
the latitude and longitude to the POINT column)...
INSERT INTO map_data (meridian, range, township, section,
quartersection, latitude, longitude, coordinates)
VALUES('4','01','001','01','E4','49.00701238','-110.00507933',
GeomFromText('POINT(49.00701238 -110.00507933)') ),
('4','01','001','01','N4','49.01415809','-110.01615511', GeomFromText
('POINT(49.01415809 -110.01615511)') )
,('4','01','001','01','NE','49.01424023','-110.00508075',
GeomFromText
('POINT(49.01424023 -110.00508075)') );
So now that the table has a column that is spatially savvy, how would
I write a SELECT that fetches the one record with coordinates closest
(in terms of distance) to a given latitude/longitude? I don't mean to
be lazy, but there's very little documentation on this. Any help is
much appreciated. Thanks!
...Rene
On 2-Feb-07, at 8:50 PM, ViSolve DB Team wrote:
> Hi,
>
> Try like this
>
> (SELECT * FROM (SELECT * FROM tab_name ORDER BY latitute ASC,
> longitude ASC) a
> WHERE a.latitude<=givenvalue AND a.longitude<=givenvalue
> LIMIT 0,5)
> UNION
> (SELECT * FROM (SELECT * FROM tab_name ORDER BY latitute ASC,
> longitude ASC) b
> WHERE b.latitude>givenvalue AND b.longitude>givenvalue
> LIMIT 0,5)
> ;
>
> Thanks,
> ViSolveDB Team
>
> - Original Message - From: "M5" <[EMAIL PROTECTED]>
> To:
> Sent: Saturday, February 03, 2007 7:30 AM
> Subject: How to SELECT rows closest to value
>
>
>> Simply put, I have a table of ~800,000 records containing, among
>> other things, latitude and longitude values. Given a lat/lng pair,
>> I would like to SELECT from this table the 10 rows containing
>> latitude/ longitude values closest to the given lat/lng pair.
>> Possible?
>>
>> ...Rene
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?
>> [EMAIL PROTECTED]
>>
>>
>>
>> --
>> No virus found in this incoming message.
>> Checked by AVG Free Edition.
>> Version: 7.1.411 / Virus Database: 268.17.19/663 - Release Date:
>> 2/1/2007
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?
> [EMAIL PROTECTED]
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
Lars Schwarz
Gottorpstrasse 20
26122 Oldenburg
T 0441 2171 354 0
F 0441 2171 354 0
M 0179 512 4628
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT rows closest to lat/lng -- USING SPATIAL FUNCTIONS
haven't followed the complete thread so i don't know what you're
after, but maybe this helps?
SELECT latitude, longitude, SQRT( POW( 69.1 * ( latitude - [startlat]
) , 2 ) + POW( 69.1 * ( [startlng] - longitude ) * COS( latitude /
57.3 ) , 2 ) ) AS distance FROM foobar ORDER BY distance ASC
where [starlat] and [startlng] is the position where to start
messuring the distance.
On 2/3/07, M5 <[EMAIL PROTECTED]> wrote:
Thanks for the reply... It returns results, but not exactly what I am
hoping for--basically, I get the nearest latitudes (but not near
longitude), and nearest longitude (but not near latitudes). In other
words, it doesn't return the closest lat/lng pair... Also, the query
takes about 8 seconds to run, so maybe there's a better way?
I've been looking at the spatial extensions, and made some progress.
I created a new column of type POINT. Here's the table definition:
CREATE TABLE `map_data` (
`id` int(10) unsigned NOT NULL auto_increment,
`meridian` tinyint(3) unsigned NOT NULL,
`range` tinyint(3) unsigned NOT NULL,
`township` tinyint(3) unsigned NOT NULL,
`section` tinyint(3) unsigned NOT NULL,
`quartersection` varchar(2) collate latin1_general_ci NOT NULL,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point default NULL,
PRIMARY KEY (`id`),
KEY `latlng` (`latitude`,`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=0 ;
...And then reloaded the ~800,000 records into the table (and adding
the latitude and longitude to the POINT column)...
INSERT INTO map_data (meridian, range, township, section,
quartersection, latitude, longitude, coordinates)
VALUES('4','01','001','01','E4','49.00701238','-110.00507933',
GeomFromText('POINT(49.00701238 -110.00507933)') ),
('4','01','001','01','N4','49.01415809','-110.01615511', GeomFromText
('POINT(49.01415809 -110.01615511)') )
,('4','01','001','01','NE','49.01424023','-110.00508075', GeomFromText
('POINT(49.01424023 -110.00508075)') );
So now that the table has a column that is spatially savvy, how would
I write a SELECT that fetches the one record with coordinates closest
(in terms of distance) to a given latitude/longitude? I don't mean to
be lazy, but there's very little documentation on this. Any help is
much appreciated. Thanks!
...Rene
On 2-Feb-07, at 8:50 PM, ViSolve DB Team wrote:
> Hi,
>
> Try like this
>
> (SELECT * FROM (SELECT * FROM tab_name ORDER BY latitute ASC,
> longitude ASC) a
> WHERE a.latitude<=givenvalue AND a.longitude<=givenvalue
> LIMIT 0,5)
> UNION
> (SELECT * FROM (SELECT * FROM tab_name ORDER BY latitute ASC,
> longitude ASC) b
> WHERE b.latitude>givenvalue AND b.longitude>givenvalue
> LIMIT 0,5)
> ;
>
> Thanks,
> ViSolveDB Team
>
> - Original Message - From: "M5" <[EMAIL PROTECTED]>
> To:
> Sent: Saturday, February 03, 2007 7:30 AM
> Subject: How to SELECT rows closest to value
>
>
>> Simply put, I have a table of ~800,000 records containing, among
>> other things, latitude and longitude values. Given a lat/lng pair,
>> I would like to SELECT from this table the 10 rows containing
>> latitude/ longitude values closest to the given lat/lng pair.
>> Possible?
>>
>> ...Rene
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?
>> [EMAIL PROTECTED]
>>
>>
>>
>> --
>> No virus found in this incoming message.
>> Checked by AVG Free Edition.
>> Version: 7.1.411 / Virus Database: 268.17.19/663 - Release Date:
>> 2/1/2007
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?
> [EMAIL PROTECTED]
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Lars Schwarz
Gottorpstrasse 20
26122 Oldenburg
T 0441 2171 354 0
F 0441 2171 354 0
M 0179 512 4628
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT rows closest to lat/lng -- USING SPATIAL FUNCTIONS
Thanks for the reply... It returns results, but not exactly what I am
hoping for--basically, I get the nearest latitudes (but not near
longitude), and nearest longitude (but not near latitudes). In other
words, it doesn't return the closest lat/lng pair... Also, the query
takes about 8 seconds to run, so maybe there's a better way?
I've been looking at the spatial extensions, and made some progress.
I created a new column of type POINT. Here's the table definition:
CREATE TABLE `map_data` (
`id` int(10) unsigned NOT NULL auto_increment,
`meridian` tinyint(3) unsigned NOT NULL,
`range` tinyint(3) unsigned NOT NULL,
`township` tinyint(3) unsigned NOT NULL,
`section` tinyint(3) unsigned NOT NULL,
`quartersection` varchar(2) collate latin1_general_ci NOT NULL,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point default NULL,
PRIMARY KEY (`id`),
KEY `latlng` (`latitude`,`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=0 ;
...And then reloaded the ~800,000 records into the table (and adding
the latitude and longitude to the POINT column)...
INSERT INTO map_data (meridian, range, township, section,
quartersection, latitude, longitude, coordinates)
VALUES('4','01','001','01','E4','49.00701238','-110.00507933',
GeomFromText('POINT(49.00701238 -110.00507933)') ),
('4','01','001','01','N4','49.01415809','-110.01615511', GeomFromText
('POINT(49.01415809 -110.01615511)') )
,('4','01','001','01','NE','49.01424023','-110.00508075', GeomFromText
('POINT(49.01424023 -110.00508075)') );
So now that the table has a column that is spatially savvy, how would
I write a SELECT that fetches the one record with coordinates closest
(in terms of distance) to a given latitude/longitude? I don't mean to
be lazy, but there's very little documentation on this. Any help is
much appreciated. Thanks!
...Rene
On 2-Feb-07, at 8:50 PM, ViSolve DB Team wrote:
Hi,
Try like this
(SELECT * FROM (SELECT * FROM tab_name ORDER BY latitute ASC,
longitude ASC) a
WHERE a.latitude<=givenvalue AND a.longitude<=givenvalue
LIMIT 0,5)
UNION
(SELECT * FROM (SELECT * FROM tab_name ORDER BY latitute ASC,
longitude ASC) b
WHERE b.latitude>givenvalue AND b.longitude>givenvalue
LIMIT 0,5)
;
Thanks,
ViSolveDB Team
- Original Message - From: "M5" <[EMAIL PROTECTED]>
To:
Sent: Saturday, February 03, 2007 7:30 AM
Subject: How to SELECT rows closest to value
Simply put, I have a table of ~800,000 records containing, among
other things, latitude and longitude values. Given a lat/lng pair,
I would like to SELECT from this table the 10 rows containing
latitude/ longitude values closest to the given lat/lng pair.
Possible?
...Rene
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.411 / Virus Database: 268.17.19/663 - Release Date:
2/1/2007
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: [others] Re: Views with functions and performance issues
The hit with a join on indexed columns is negligible. Relational databases live for joins - they eat them for breakfast! Seriously, as long as it's indexed in both tables, it'll be super-speedy. Dan On 9/22/06, Christopher Brooks <[EMAIL PROTECTED]> wrote: Hi, thanks for the comments, > If I'm understanding right - the view contains an additional > column that is an MD5 hash of some or all of the data in the > base table, right? Close. It's got all of the data in the base table except for the colum that's being hashed - we show the hashed version of that instead. Like a password table, where the base table has the plain text passwords and usernames, and the view only shows the hashed passwords and usernames (though it's not for passwords). > set up second table, with two columns. One is id number to > correspond to id number in original table, second is to hold > Md5 hash. Createn index on the MD5 column for sure, possibly > a two column index. I'm not sure whether it would be better > to create it as (id, md5_col) or (md5_col, id); I'd test it > each way I guess. > > Set up insert/update/delete triggers on the first table to > add/update/delete records in the second table with ID and the > MD5 hash. > > Then re-create your view to show you all the columns from the > first table plus the MD5 column from the second table, > joining on the id column. This sounds good, and this way I don't have to change the first table much at all (as it's a replicated table and my luck with replication only lately seems to have gotten good). What kind of performance hit will I be taking because of the join in the view (e.g. every query to the view is going to have to have to do the join, yes?). Or is this neglegable as long as the hash and pointer of the second table are both indexed... Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [others] Re: Views with functions and performance issues
Hi, thanks for the comments, > If I'm understanding right - the view contains an additional > column that is an MD5 hash of some or all of the data in the > base table, right? Close. It's got all of the data in the base table except for the colum that's being hashed - we show the hashed version of that instead. Like a password table, where the base table has the plain text passwords and usernames, and the view only shows the hashed passwords and usernames (though it's not for passwords). > set up second table, with two columns. One is id number to > correspond to id number in original table, second is to hold > Md5 hash. Createn index on the MD5 column for sure, possibly > a two column index. I'm not sure whether it would be better > to create it as (id, md5_col) or (md5_col, id); I'd test it > each way I guess. > > Set up insert/update/delete triggers on the first table to > add/update/delete records in the second table with ID and the > MD5 hash. > > Then re-create your view to show you all the columns from the > first table plus the MD5 column from the second table, > joining on the id column. This sounds good, and this way I don't have to change the first table much at all (as it's a replicated table and my luck with replication only lately seems to have gotten good). What kind of performance hit will I be taking because of the join in the view (e.g. every query to the view is going to have to have to do the join, yes?). Or is this neglegable as long as the hash and pointer of the second table are both indexed... Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Views with functions and performance issues
If I'm understanding right - the view contains an additional column that is an MD5 hash of some or all of the data in the base table, right? Yes, I would expect that to be very very slow. When selecting, your database engine has tro calculate 700K MD5 hashes. Slow. When selecting a subset it has to do that plus what amounts to a table scan of those 700K MD5 hashes. Very slow. Wonder if you could do this: set up second table, with two columns. One is id number to correspond to id number in original table, second is to hold Md5 hash. Createn index on the MD5 column for sure, possibly a two column index. I'm not sure whether it would be better to create it as (id, md5_col) or (md5_col, id); I'd test it each way I guess. Set up insert/update/delete triggers on the first table to add/update/delete records in the second table with ID and the MD5 hash. Then re-create your view to show you all the columns from the first table plus the MD5 column from the second table, joining on the id column. I think this should end up being pretty fast since the hashes will only be calculated when the data changes, not all at once for every select, and an index will speed things up tremendously when selecting a subset. HTH, Dan On 9/22/06, Christopher Brooks <[EMAIL PROTECTED]> wrote: Hi, I've got a view of a base table that is 100% identical to that base table except for one column, which is a projection of the base table after its MD5 hashed. The table is largish (~700,000 rows) and is growing quickly. Queries on the base table are nice and fast, but on the hashed view are painfully slow - in particular when restricting the queries on the view based on the hashed colum. I assume this is because there is no index for the hashed colum. Is there a good way I can deal with this, without changing the base table? TIA, running MySQL 5 on windows, can change to a later build of MySQL (as long as I can replicate from 5) if there is some funky stuff in there that will do what I need... Chris -- Christopher A. Brooks Research Officer, ARIES Group University of Saskatchewan Email: [EMAIL PROTECTED] Mail: Christopher Brooks Department of Computer Science University of Saskatchewan 57 Campus Drive Saskatoon, Saskatchewan, S7N 5A9 Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Views with functions and performance issues
Hi, I've got a view of a base table that is 100% identical to that base table except for one column, which is a projection of the base table after its MD5 hashed. The table is largish (~700,000 rows) and is growing quickly. Queries on the base table are nice and fast, but on the hashed view are painfully slow - in particular when restricting the queries on the view based on the hashed colum. I assume this is because there is no index for the hashed colum. Is there a good way I can deal with this, without changing the base table? TIA, running MySQL 5 on windows, can change to a later build of MySQL (as long as I can replicate from 5) if there is some funky stuff in there that will do what I need... Chris -- Christopher A. Brooks Research Officer, ARIES Group University of Saskatchewan Email: [EMAIL PROTECTED] Mail: Christopher Brooks Department of Computer Science University of Saskatchewan 57 Campus Drive Saskatoon, Saskatchewan, S7N 5A9 Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Expressions, SQL Aggregate functions & SELECT Performance - index usage
Hi, I am having to write queries that contain operators such as MOD and DIV in addition to other normal arithmetic operators to add, multiply, divide, etc. I am also using MAX(), MIN() in the same queries. Actually, I am using INSERT ... SELECT MAX() * X ... FROM TABLE idiom to calculate the max key value to be inserted in the table instead of using the auto-increment (because it's rollback-unfriendly). However, I wonder if using such expressions (I not using any other functions except SQL Aggregate functions) in the select column-list would slow down the INSERTs and what the performance implications are if there are around 75 users. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date functions
Addison, Mark wrote: From: Chris W Sent: 07 July 2006 09:23 It's late and I just gave up reading the manual. Can someone please tell me the easiest way to do a query that will return all rows with a time stamp that is X number of seconds older than the current time? Something like this. SELECT * FROM t WHERE TimeCol < (now() - 60*60*24*3) Yes I know that is just 3 days but other times I will want to find records that are a few hours old so I like using the formula. SELECT * FROM t WHERE TimeCol < DATE_SUB(CURDATE(), INTERVAL 60*60*24*3 SECOND); Maybe it was just too late at night but I read about the DATE_SUB function in the manual and got the impression that it ignored the time part of a date time field so I could not use it for finding records only a few hours old. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want & give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date functions
The INTERVAL command is what you are looking for. It doesn't have to be SECOND (with no S), you could use day, hour ,week, etc. SELECT * FROM t WHERE TimeCol<(now() - INTERVAL X SECOND) http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html - Original Message - From: "Chris W" <[EMAIL PROTECTED]> To: Sent: Friday, July 07, 2006 4:23 AM Subject: Date functions It's late and I just gave up reading the manual. Can someone please tell me the easiest way to do a query that will return all rows with a time stamp that is X number of seconds older than the current time? Something like this. SELECT * FROM t WHERE TimeCol < (now() - 60*60*24*3) Yes I know that is just 3 days but other times I will want to find records that are a few hours old so I like using the formula. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want & give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date functions
Try this: SELECT * FROM t where TimeCol < date_sub( now(), INTERVAL x SECOND ) Dan On 7/7/06, Chris W <[EMAIL PROTECTED]> wrote: It's late and I just gave up reading the manual. Can someone please tell me the easiest way to do a query that will return all rows with a time stamp that is X number of seconds older than the current time? Something like this. SELECT * FROM t WHERE TimeCol < (now() - 60*60*24*3) Yes I know that is just 3 days but other times I will want to find records that are a few hours old so I like using the formula. -- Chris W KE5GIX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Date functions
From: Chris W Sent: 07 July 2006 09:23 > > It's late and I just gave up reading the manual. Can someone please > tell me the easiest way to do a query that will return all > rows with a > time stamp that is X number of seconds older than the current time? > Something like this. > > SELECT * FROM t > WHERE TimeCol < (now() - 60*60*24*3) > > Yes I know that is just 3 days but other times I will want to find > records that are a few hours old so I like using the formula. SELECT * FROM t WHERE TimeCol < DATE_SUB(CURDATE(), INTERVAL 60*60*24*3 SECOND); http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date functions
It's late and I just gave up reading the manual. Can someone please tell me the easiest way to do a query that will return all rows with a time stamp that is X number of seconds older than the current time? Something like this. SELECT * FROM t WHERE TimeCol < (now() - 60*60*24*3) Yes I know that is just 3 days but other times I will want to find records that are a few hours old so I like using the formula. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want & give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: functions in AS
[snip]
SET @sql=CONCAT('SELECT SUM(IF(SUBSTRING(updated,1,10)=CURDATE(), 1, 0)) AS ',
char(39),
CURDATE(),
CHAR(39),
' FROM tablename GROUP BY group by psDealer' );
PREPARE stmt FROM @sql;
[/snip]
Very clever Peter! Thanks for your help on this.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: functions in AS
Jay,
[snip]
We all know that you cannot do something like this;
sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate()
nor can you use user variables even though they get set properly
set @d1 = curdate();
sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1
So, does anyone know of a work around?
How about PREPARE?
[/snip]
PREPARE treats SELECT statements the same, unless I am missing
something. I have done some testing, and have been able to obtain the
desired results. Even the simplest example;
PREPARE stmt1 FROM 'SELECT curdate() AS ?';
SET @a = curdate();
EXECUTE stmt1 USING @a;
Gives a syntax error on the PREPARE statement since AS cannot be a
function. Perhaps there is something to a prepare that I should be more
aware of?
SET @sql=CONCAT('SELECT SUM(IF(SUBSTRING(updated,1,10)=CURDATE(),
1, 0)) AS ',
char(39),
CURDATE(),
CHAR(39),
' FROM tablename GROUP BY group by psDealer' );
PREPARE stmt FROM @sql;
PB
- ;
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.3/358 - Release Date: 6/7/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: functions in AS
[snip] >We all know that you cannot do something like this; >sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate() >nor can you use user variables even though they get set properly >set @d1 = curdate(); >sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1 >So, does anyone know of a work around? How about PREPARE? [/snip] PREPARE treats SELECT statements the same, unless I am missing something. I have done some testing, and have been able to obtain the desired results. Even the simplest example; PREPARE stmt1 FROM 'SELECT curdate() AS ?'; SET @a = curdate(); EXECUTE stmt1 USING @a; Gives a syntax error on the PREPARE statement since AS cannot be a function. Perhaps there is something to a prepare that I should be more aware of? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: functions in AS
Jay, We all know that you cannot do something like this; sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate() nor can you use user variables even though they get set properly set @d1 = curdate(); sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1 So, does anyone know of a work around? How about PREPARE? PB - Jay Blanchard wrote: select psDealerID, sum(if(substring(updated, 1, 10) = curdate(), 1, 0)), sum(if(substring(updated, 1, 10) = date_sub(curdate(), interval 1 day), 1, 0)) from provision group by psDealerID I love crosstab queries, but one thing really eats at me. I'd like to be able to add significance to the AS with a function. So that the return would look something like; ++++ | psDealerID | 2006-06-07 | 2006-06-06 | ++++ || 0 | 4 | | 301AA | 0 | 0 | | 301AB | 2 | 0 | | 302AA | 0 | 0 | | 303AA | 0 | 1 | We all know that you cannot do something like this; sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate() nor can you use user variables even though they get set properly set @d1 = curdate(); sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1 So, does anyone know of a work around? I have RTFM and STFW...but to no avail. Thanks! -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.8.3/358 - Release Date: 6/7/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
functions in AS
select psDealerID, sum(if(substring(updated, 1, 10) = curdate(), 1, 0)), sum(if(substring(updated, 1, 10) = date_sub(curdate(), interval 1 day), 1, 0)) from provision group by psDealerID I love crosstab queries, but one thing really eats at me. I'd like to be able to add significance to the AS with a function. So that the return would look something like; ++++ | psDealerID | 2006-06-07 | 2006-06-06 | ++++ || 0 | 4 | | 301AA | 0 | 0 | | 301AB | 2 | 0 | | 302AA | 0 | 0 | | 303AA | 0 | 1 | We all know that you cannot do something like this; sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate() nor can you use user variables even though they get set properly set @d1 = curdate(); sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1 So, does anyone know of a work around? I have RTFM and STFW...but to no avail. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining multiple tables with grouping functions
Ville Mattila wrote: I try to get a list of all Invoices with total sum of the invoice and paid sum of each invoices, as well as a customer name. I try following query: SELECT Invoices.*, SUM(InvoiceContents.Amount * InvoiceContents.Price) AS InvoiceTotal, Customers.Name, SUM(Payments.Amount) PaidTotal, MAX(Payments.Date) LastPayment FROM Invoices LEFT JOIN InvoiceContents ON (InvoiceContents.InvoiceID = Invoices.ID) LEFT JOIN Customers ON (Customers.ID = Invoices.CustomerID) LEFT JOIN Payments ON (Payments.InvocieID = Invoices.ID) GROUP BY Invoices.ID ORDER BY Invoices.ID; The query works fine, but multiples the total paid sum with the number of the matched InvoiceContents. If I remove the JOIN with InvoiceContents, the query works fine (except I can't get the total sum of the invoice). Hi Ville! Try this: SELECT i.* , ic.InvoiceTotal , c.Name , COALESCE(p.PaidTotal, 0.00) AS PaidTotal , COALESCE(p.LastPayment, 'No payment made') AS LastPayment FROM Invoices i INNER JOIN ( SELECT InvoiceID , SUM(Amount * Price) AS InvoiceTotal FROM InvoiceContents GROUP BY InvoiceID ) AS ic ON i.ID = ic.InvoiceID INNER JOIN Customers c ON i.CustomerID = c.ID LEFT JOIN ( SELECT InvoiceID , SUM(Amount) AS PaidTotal , MAX(Payments.Date) AS LastPayment FROM Payments GROUP BY InvoiceID ) AS p ON i.ID = p.InvoiceID; Just to explain, what I'm doing here is using two derived tables to get the aggregate information for both payments and invoices. I have changed some of your LEFT JOINs to INNER JOINs, because I could not see a way that an invoice would *not* have a customer, or that an invoice would *not* have any contents. However, I kept the LEFT JOIN on the payments aggregate because it is likely that an invoice would not have a payment associated with it. You will notice that I have taken the aggregates (the SUM and MAX functions) out of the main query and put them into the derived tables (the subqueries in the FROM clause). This prevents the "multiplication" effect you saw earlier. Also, I have used the COALESCE function in the outer SELECT in order to eliminate any possible NULL values from the results and replace those with a correct value. Hope this helps, -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining multiple tables with grouping functions
Ville, >SELECT Invoices.*, SUM(InvoiceContents.Amount * InvoiceContents.Price) >AS InvoiceTotal, Customers.Name, SUM(Payments.Amount) PaidTotal, >MAX(Payments.Date) LastPayment FROM Invoices LEFT JOIN InvoiceContents >ON (InvoiceContents.InvoiceID = Invoices.ID) LEFT JOIN Customers ON >(Customers.ID = Invoices.CustomerID) LEFT JOIN Payments ON >(Payments.InvocieID = Invoices.ID) GROUP BY Invoices.ID ORDER BY >Invoices.ID; >The query works fine, but multiples the total paid sum with the number >of the matched InvoiceContents. Try ... SELECT Invoices.*, ( SELECT SUM(Amount * .Price) FROM InvoiceContents WHERE InvoiceCOntents.InvoiceID = Invoices.ID ) AS InvoiceTotal, Customers.Name, SUM(Payments.Amount) PaidTotal, MAX(Payments.Date) LastPayment FROM Customers INNER JOIN Invoices ON (Customers.ID = Invoices.CustomerID) LEFT JOIN Payments ON (Payments.InvocieID = Invoices.ID) GROUP BY Invoices.ID There's an explanation at http://www.artfulsoftware.com/queries.php#4 PB - Ville Mattila wrote: Hello all, I'm sure that this situation is one of the most wondered questions with JOIN clauses. Anyway, I couldn't find any clear information how to carry out multiple joins in one query with proper results. I have four tables: 1. Invoices 2. InvoiceContents 3. Customers 4. Payments I try to get a list of all Invoices with total sum of the invoice and paid sum of each invoices, as well as a customer name. I try following query: SELECT Invoices.*, SUM(InvoiceContents.Amount * InvoiceContents.Price) AS InvoiceTotal, Customers.Name, SUM(Payments.Amount) PaidTotal, MAX(Payments.Date) LastPayment FROM Invoices LEFT JOIN InvoiceContents ON (InvoiceContents.InvoiceID = Invoices.ID) LEFT JOIN Customers ON (Customers.ID = Invoices.CustomerID) LEFT JOIN Payments ON (Payments.InvocieID = Invoices.ID) GROUP BY Invoices.ID ORDER BY Invoices.ID; The query works fine, but multiples the total paid sum with the number of the matched InvoiceContents. If I remove the JOIN with InvoiceContents, the query works fine (except I can't get the total sum of the invoice). How should I do the join to get proper results? Thanks, Ville -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: 5/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joining multiple tables with grouping functions
Hello all, I'm sure that this situation is one of the most wondered questions with JOIN clauses. Anyway, I couldn't find any clear information how to carry out multiple joins in one query with proper results. I have four tables: 1. Invoices 2. InvoiceContents 3. Customers 4. Payments I try to get a list of all Invoices with total sum of the invoice and paid sum of each invoices, as well as a customer name. I try following query: SELECT Invoices.*, SUM(InvoiceContents.Amount * InvoiceContents.Price) AS InvoiceTotal, Customers.Name, SUM(Payments.Amount) PaidTotal, MAX(Payments.Date) LastPayment FROM Invoices LEFT JOIN InvoiceContents ON (InvoiceContents.InvoiceID = Invoices.ID) LEFT JOIN Customers ON (Customers.ID = Invoices.CustomerID) LEFT JOIN Payments ON (Payments.InvocieID = Invoices.ID) GROUP BY Invoices.ID ORDER BY Invoices.ID; The query works fine, but multiples the total paid sum with the number of the matched InvoiceContents. If I remove the JOIN with InvoiceContents, the query works fine (except I can't get the total sum of the invoice). How should I do the join to get proper results? Thanks, Ville -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
String functions FORMAT(X,D)
Hello MySQL Users Is there a way to change the separator in the following example, e.g. from "," to "'"? SELECT FORMAT(12332.123456, 4); -> '12,332.1235' is this the easiest way? SELECT REPLACE(FORMAT(12332.123456, 4), ",", "'"); -> '12'332.1235' or can this be set somewhere? Thank you! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Crypt Functions
http://dev.mysql.com/doc/refman/4.1/en/encryption-functions.html On 5/21/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hi Guys, Can somebody enlighten me on AES_CRYPT & AES_DECRYPT functions on MySQL. I noticed that you can mention DES key file in mysqld statup options. how does the AES works? is there any one who used these functions (production systems)? Thanx in Advance, Kosala This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crypt Functions
Hi Guys, Can somebody enlighten me on AES_CRYPT & AES_DECRYPT functions on MySQL. I noticed that you can mention DES key file in mysqld statup options. how does the AES works? is there any one who used these functions (production systems)? Thanx in Advance, Kosala This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The MySQL Stored Procedures and Functions is ready ?
> The MySQL Stored Procedures and Functions is ready to use on production > systems ? > > or support is very ammature ? > > I need to know because is a project desing decision ! > > Tnks !! > > PLEASE I NEED OPINIONS By the time your project is finished designing, it will be ready ;-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development 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/[EMAIL PROTECTED]
The MySQL Stored Procedures and Functions is ready ?
The MySQL Stored Procedures and Functions is ready to use on production systems ? or support is very ammature ? I need to know because is a project desing decision ! Tnks !! PLEASE I NEED OPINIONS i'm crazy to use this ? Tnks in advance MySQL , InnoDB and Linux ! -- - ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A ***http://javacoffe.blogspot.com*** - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into "my eyes" Phone : +55 041 2106-1212 look: cannot open my eyes Fax : +55 041 3296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot create functions from a workstation
My response is mixed with your original post... --- Jim <[EMAIL PROTECTED]> wrote: > Hi All, > > > > We are unable to create functions, views and stored procs from > another > machine. > > The error message is Access denied for user 'root'@'%' to database > 'proplink'. > Then the user 'root' does not have permissions to do those actions from a different machine than the server. > > Is there some permissions setting we need to set to allow a user to > logon as > root and run scripts to add,etc functions and procs from a machine > other > than the server? > Yes. MySQL validates a login account not just against a name/password combination but also against the IP address the client is connecting from. All 3 pieces must exist before permissions are granted. Here is the chapter of the manual that deals with logins and security. http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html > > Thanks, > > Jim > Check the permissions for [EMAIL PROTECTED] by running the command SHOW GRANTS FOR 'root'@'%'; Then compare the results to SHOW GRANTS FOR 'root'; and you should see why your remote logins aren't able to do anything you wanted them to do. http://dev.mysql.com/doc/refman/5.0/en/show-grants.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Including other hash-functions
Hi, as you know, MD5 and SHA1 are more or less broken so I guess it is time to think about including other hash-functions in MySQL. First of all it would be sensible to include SHA256 / SHA512, because they are designed a bit different to SHA1 and though are more safe. Another issue is to include completely new algorithms like tiger and whirlpool. Is there any possibility to do this without touching MySQL's source and if not, are there any plans to include them? Greets, Lars -- "Kriterium des Wahren ist nicht seine unmittelbare Kommunizierbarkeit an jedermann" -- Theodor Wiesengrund Adorno, aus: »Negative Dialektik« name: Lars H. Strojny web: http://strojny.net street: Engelsstraße 23blog: http://usrportage.de city: D-51103 Köln mail/jabber: [EMAIL PROTECTED] f-print: 1FD5 D8EE D996 8E3E 1417 328A 240F 17EB 0263 AC07 signature.asc Description: Dies ist ein digital signierter Nachrichtenteil
Re: Aggregate functions in ORDER BY
Thanks for that Nicolas. Yasir Can you run without the Order By at all? If not, you may need to properly join the tables. I could reproduce the issue with MySQL 4.1 as well and I would go around it myself by creating a temporaty table, populating it with the aggregate, doing a select on the temp table and then of course dropping it. -Original Message- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 7:10 PM To: mysql@lists.mysql.com Subject: Aggregate functions in ORDER BY Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aggregate functions in ORDER BY
Thanks Dave. I was hoping I could avoid naming the columns myself (all my queries are built using an SQL code compiler from data stored in XML files). I'll have to write code to add the column alias names (but only if they don't use expressions). Yasir I dont think it is possible although I have never searched hard for the ability. It seems unlikely - since you get to name the columns in the table and the columns in the query (using 'as'). I suggest you use one of those abilities. -Original Message- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 11:10 PM To: Dave Pullin Cc: mysql@lists.mysql.com Subject: Re: Aggregate functions in ORDER BY Many thanks for that Dave. Do you know whether it's possible for MySQL to return fully qualified column names by default? For example, I'd like select * from foo; to return ++--+ | foo.a | foo.b| ++--+ | 1 | Rex | | 2 | Buddy| | 3 | Sam | | 4 | Lucky| | 5 | Tiny | | 6 | Snoopy | | 7 | Lady | | 8 | Tiger| | 9 | Speedy | ++--+ instead of ++--+ | a | b| ++--+ | 1 | Rex | | 2 | Buddy| | 3 | Sam | | 4 | Lucky| | 5 | Tiny | | 6 | Snoopy | | 7 | Lady | | 8 | Tiger| | 9 | Speedy | ++--+ Thanks, Yasir All you are doing when you quote the expression like `COUNT(pets.pet_id)` is referencing the column in the select by its default column name (which is the same as the expression.) That's why it only works when the expression is a column. If you dont want the order by column to appear in the results, make your select a subselect. select a,b,c from ( select a,b,c, COUNT(pets.pet_id) as count from etc order by count ) as subtable Dave -Original Message- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 9:10 PM To: mysql@lists.mysql.com Subject: Aggregate functions in ORDER BY Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Aggregate functions in ORDER BY
I dont think it is possible although I have never searched hard for the ability. It seems unlikely - since you get to name the columns in the table and the columns in the query (using 'as'). I suggest you use one of those abilities. -Original Message- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 11:10 PM To: Dave Pullin Cc: mysql@lists.mysql.com Subject: Re: Aggregate functions in ORDER BY Many thanks for that Dave. Do you know whether it's possible for MySQL to return fully qualified column names by default? For example, I'd like select * from foo; to return ++--+ | foo.a | foo.b| ++--+ | 1 | Rex | | 2 | Buddy| | 3 | Sam | | 4 | Lucky| | 5 | Tiny | | 6 | Snoopy | | 7 | Lady | | 8 | Tiger| | 9 | Speedy | ++--+ instead of ++--+ | a | b| ++--+ | 1 | Rex | | 2 | Buddy| | 3 | Sam | | 4 | Lucky| | 5 | Tiny | | 6 | Snoopy | | 7 | Lady | | 8 | Tiger| | 9 | Speedy | ++--+ Thanks, Yasir All you are doing when you quote the expression like `COUNT(pets.pet_id)` is referencing the column in the select by its default column name (which is the same as the expression.) That's why it only works when the expression is a column. If you dont want the order by column to appear in the results, make your select a subselect. select a,b,c from ( select a,b,c, COUNT(pets.pet_id) as count from etc order by count ) as subtable Dave -Original Message- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 9:10 PM To: mysql@lists.mysql.com Subject: Aggregate functions in ORDER BY Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1);
RE: Aggregate functions in ORDER BY
Can you run without the Order By at all? If not, you may need to properly join the tables. I could reproduce the issue with MySQL 4.1 as well and I would go around it myself by creating a temporaty table, populating it with the aggregate, doing a select on the temp table and then of course dropping it. -Original Message- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 7:10 PM To: mysql@lists.mysql.com Subject: Aggregate functions in ORDER BY Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aggregate functions in ORDER BY
Many thanks for that Dave. Do you know whether it's possible for MySQL to return fully qualified column names by default? For example, I'd like select * from foo; to return ++--+ | foo.a | foo.b| ++--+ | 1 | Rex | | 2 | Buddy| | 3 | Sam | | 4 | Lucky| | 5 | Tiny | | 6 | Snoopy | | 7 | Lady | | 8 | Tiger| | 9 | Speedy | ++--+ instead of ++--+ | a | b| ++--+ | 1 | Rex | | 2 | Buddy| | 3 | Sam | | 4 | Lucky| | 5 | Tiny | | 6 | Snoopy | | 7 | Lady | | 8 | Tiger| | 9 | Speedy | ++--+ Thanks, Yasir All you are doing when you quote the expression like `COUNT(pets.pet_id)` is referencing the column in the select by its default column name (which is the same as the expression.) That's why it only works when the expression is a column. If you dont want the order by column to appear in the results, make your select a subselect. select a,b,c from ( select a,b,c, COUNT(pets.pet_id) as count from etc order by count ) as subtable Dave -Original Message- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 9:10 PM To: mysql@lists.mysql.com Subject: Aggregate functions in ORDER BY Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Aggregate functions in ORDER BY
All you are doing when you quote the expression like `COUNT(pets.pet_id)` is referencing the column in the select by its default column name (which is the same as the expression.) That's why it only works when the expression is a column. If you dont want the order by column to appear in the results, make your select a subselect. select a,b,c from ( select a,b,c, COUNT(pets.pet_id) as count from etc order by count ) as subtable Dave -Original Message- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 9:10 PM To: mysql@lists.mysql.com Subject: Aggregate functions in ORDER BY Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Aggregate functions in ORDER BY
Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1);
Re: puzzled by date functions (long)
In the last episode (Mar 01), Giuseppe Maxia said: > Yesterday I was analyzing the behavior of the query optimizer, and I > stumbled into a most curious case. I created two functions returning > the extremes of a date range, and I wanted to see how many times > those functions were called when used in a WHERE clause So I added > log tracing instructions to both of them. The result was quite > surprising. Let's set the environment first. [..] > I can't imagine why this is happening. The only difference is that dt > is now primary key. Instead of being called once, the routine is > called twice. If I simply drop the primary key in t2, then the > routine is called once per query, as expected. The result does not > change if I use InnoDB tables instead of MyISAM. My guess is that the query optimizer is comparing the range endpoints against the index to see whether it needs to do a full table scan, index range scan, or optimize the table away. Depending on what data is shared between the optimizer and the query enging itself, it may need to evaluate the WHERE clause multiple times. Just a guess though; examining the source, or replacing your stored procedure with a UDF function that printed its own stack trace to a text file, would tell you for certain. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
