Re: Does MySQL have RETURNING in the language?

[Rob Wultsch]

On Wed, Oct 15, 2008 at 2:09 PM, Rob Wultsch <[EMAIL PROTECTED]> wrote:
> On Wed, Oct 15, 2008 at 2:00 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote:
>> Rob Wultsch wrote:
>>>
>>> On Wed, Oct 15, 2008 at 12:25 PM, D. Dante Lorenso <[EMAIL PROTECTED]>
>>> wrote:

 There's an awesome feature that was added to PostgreSQL a while back
 called
 RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement
 behave like a SELECT statement.  You can do something like this:

  INSERT INTO mytable (id, value)
  VALUES (1, 'something')
  RETURNING any_column_you_want;

 This would be equivalent to running something like this in MySQL:

  INSERT INTO mytable (id, value)
  VALUES (1, 'something');

  SELECT any_column_you_want
  FROM mytable
  WHERE id = 1;

 Here is another example with an UPDATE query:

  UPDATE mytable SET
   value = 'something'
  WHERE id = 1
  RETURNING id, other_number;

 The nice thing about this is that every insert or update can return any
 column you want (even multiple columns) without having to do the
 INSERT/UPDATE then turn around and perform another SELECT query.

 I want to use this because when I insert a value into a table, I don't
 always want to get the primary key returned to me.  Sometimes I want
 another
 column which may contain a candidate key and I'd like to avoid the
 round-trip and additional logic incurred with running multiple queries.

 Does RETURNING exist in any current release of MySQL or is it on the TODO
 list even?  If it's not, how can I go about asking to have it put on
 there?

 -- Dante

 --
 D. Dante Lorenso
 [EMAIL PROTECTED]
>>>
>>>
>>> You can do your insert through a stored procedure and then at the end
>>> do a select of those values.
>>>
>>>
>>> http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-22-4-14
>>> "22.4.14:  Can MySQL 5.0 stored routines return result sets?
>>>
>>> Stored procedures can, but stored functions cannot. If you perform an
>>> ordinary SELECT inside a stored procedure, the result set is returned
>>> directly to the client. You need to use the MySQL 4.1 (or above)
>>> client-server protocol for this to work. This means that — for
>>> instance — in PHP, you need to use the mysqli extension rather than
>>> the old mysql extension. "
>>
>> This is an interesting strategy in that all your queries would turn into
>> CALL statements.
>>
>> There are several reasons why I would NOT want to turn all my queries into
>> stored procedures, though.  The main problem I have is that it is difficult
>> to deploy stored procedures from DEV to PROD environments and have those
>> deployments synchronized with the deployment of the web code.  SQL which is
>> kept with the application is easily deployed when the application is
>> deployed and the same goes for version control of the SQL if you are using
>> something like Subversion to maintain change history.
>>
>> So, I suppose you CAN perform an UPDATE and run a SELECT from a stored
>> procedure, but this strategy is not much better than doing both calls from
>> the client and still does not act like the RETURNING feature I was hoping
>> for.
>>
>> -- Dante
>
> For the record I am not a big fan of stored procedures, particularly
> because the svn/cvn issues. Also debugging is a bear.
>
> However, what I am suggesting is not to run an update and then a
> SELECT ... FROM ...(unless you are using triggers, or a virtual
> columns when that is merged, etc) because you will already have all
> the values passed in as variables you should be able to do something
> like:
> SELECT var1 AS 'colname', var2 AS 'col2';
>
> (Note the lack of a FROM clause.)
>
> Alternatively, you could set a user defined variable in insert
> triggers and then reuse the variables later on.
>
> Other than the above strategies I think you are probably out of luck.

Example:
mysql> use test;
Database changed

mysql> create table t1(c int);
Query OK, 0 rows affected (0.08 sec)

mysql> create table t2(c int);
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TRIGGER triggy AFTER INSERT
-> ON t1 FOR EACH ROW
->  set @c=NEW.c;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t1 values(3);
Query OK, 1 row affected (0.06 sec)

mysql> insert into t2 values(@c);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t2;
+--+
| c|
+--+
|3 |
+--+
1 row in set (0.00 sec)


-- 
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Does MySQL have RETURNING in the language?

[D. Dante Lorenso]

D. Dante Lorenso wrote:
There's an awesome feature that was added to PostgreSQL a while back 
called RETURNING that allows you to make an INSERT, UPDATE, and DELETE 
statement behave like a SELECT statement.

...
Does RETURNING exist in any current release of MySQL or is it on the 
TODO list even?  If it's not, how can I go about asking to have it put 
on there?


For more information on RETURNING for INSERT statements, read a little 
of this from the PostgreSQL documentation:


  http://www.postgresql.org/docs/8.3/interactive/sql-insert.html

From what I can tell this is unique to PostgreSQL.  I really want this 
functionality in MySQL.  Where do I go to ask for it?


-- Dante

--
D. Dante Lorenso
[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Does MySQL have RETURNING in the language?

[Rob Wultsch]

On Wed, Oct 15, 2008 at 2:00 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote:
> Rob Wultsch wrote:
>>
>> On Wed, Oct 15, 2008 at 12:25 PM, D. Dante Lorenso <[EMAIL PROTECTED]>
>> wrote:
>>>
>>> There's an awesome feature that was added to PostgreSQL a while back
>>> called
>>> RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement
>>> behave like a SELECT statement.  You can do something like this:
>>>
>>>  INSERT INTO mytable (id, value)
>>>  VALUES (1, 'something')
>>>  RETURNING any_column_you_want;
>>>
>>> This would be equivalent to running something like this in MySQL:
>>>
>>>  INSERT INTO mytable (id, value)
>>>  VALUES (1, 'something');
>>>
>>>  SELECT any_column_you_want
>>>  FROM mytable
>>>  WHERE id = 1;
>>>
>>> Here is another example with an UPDATE query:
>>>
>>>  UPDATE mytable SET
>>>   value = 'something'
>>>  WHERE id = 1
>>>  RETURNING id, other_number;
>>>
>>> The nice thing about this is that every insert or update can return any
>>> column you want (even multiple columns) without having to do the
>>> INSERT/UPDATE then turn around and perform another SELECT query.
>>>
>>> I want to use this because when I insert a value into a table, I don't
>>> always want to get the primary key returned to me.  Sometimes I want
>>> another
>>> column which may contain a candidate key and I'd like to avoid the
>>> round-trip and additional logic incurred with running multiple queries.
>>>
>>> Does RETURNING exist in any current release of MySQL or is it on the TODO
>>> list even?  If it's not, how can I go about asking to have it put on
>>> there?
>>>
>>> -- Dante
>>>
>>> --
>>> D. Dante Lorenso
>>> [EMAIL PROTECTED]
>>
>>
>> You can do your insert through a stored procedure and then at the end
>> do a select of those values.
>>
>>
>> http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-22-4-14
>> "22.4.14:  Can MySQL 5.0 stored routines return result sets?
>>
>> Stored procedures can, but stored functions cannot. If you perform an
>> ordinary SELECT inside a stored procedure, the result set is returned
>> directly to the client. You need to use the MySQL 4.1 (or above)
>> client-server protocol for this to work. This means that — for
>> instance — in PHP, you need to use the mysqli extension rather than
>> the old mysql extension. "
>
> This is an interesting strategy in that all your queries would turn into
> CALL statements.
>
> There are several reasons why I would NOT want to turn all my queries into
> stored procedures, though.  The main problem I have is that it is difficult
> to deploy stored procedures from DEV to PROD environments and have those
> deployments synchronized with the deployment of the web code.  SQL which is
> kept with the application is easily deployed when the application is
> deployed and the same goes for version control of the SQL if you are using
> something like Subversion to maintain change history.
>
> So, I suppose you CAN perform an UPDATE and run a SELECT from a stored
> procedure, but this strategy is not much better than doing both calls from
> the client and still does not act like the RETURNING feature I was hoping
> for.
>
> -- Dante

For the record I am not a big fan of stored procedures, particularly
because the svn/cvn issues. Also debugging is a bear.

However, what I am suggesting is not to run an update and then a
SELECT ... FROM ...(unless you are using triggers, or a virtual
columns when that is merged, etc) because you will already have all
the values passed in as variables you should be able to do something
like:
SELECT var1 AS 'colname', var2 AS 'col2';

(Note the lack of a FROM clause.)

Alternatively, you could set a user defined variable in insert
triggers and then reuse the variables later on.

Other than the above strategies I think you are probably out of luck.


-- 
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Does MySQL have RETURNING in the language?

[D. Dante Lorenso]

Rob Wultsch wrote:

On Wed, Oct 15, 2008 at 12:25 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote:

There's an awesome feature that was added to PostgreSQL a while back called
RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement
behave like a SELECT statement.  You can do something like this:

 INSERT INTO mytable (id, value)
 VALUES (1, 'something')
 RETURNING any_column_you_want;

This would be equivalent to running something like this in MySQL:

 INSERT INTO mytable (id, value)
 VALUES (1, 'something');

 SELECT any_column_you_want
 FROM mytable
 WHERE id = 1;

Here is another example with an UPDATE query:

 UPDATE mytable SET
   value = 'something'
 WHERE id = 1
 RETURNING id, other_number;

The nice thing about this is that every insert or update can return any
column you want (even multiple columns) without having to do the
INSERT/UPDATE then turn around and perform another SELECT query.

I want to use this because when I insert a value into a table, I don't
always want to get the primary key returned to me.  Sometimes I want another
column which may contain a candidate key and I'd like to avoid the
round-trip and additional logic incurred with running multiple queries.

Does RETURNING exist in any current release of MySQL or is it on the TODO
list even?  If it's not, how can I go about asking to have it put on there?

-- Dante

--
D. Dante Lorenso
[EMAIL PROTECTED]



You can do your insert through a stored procedure and then at the end
do a select of those values.

http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-22-4-14
"22.4.14:  Can MySQL 5.0 stored routines return result sets?

Stored procedures can, but stored functions cannot. If you perform an
ordinary SELECT inside a stored procedure, the result set is returned
directly to the client. You need to use the MySQL 4.1 (or above)
client-server protocol for this to work. This means that — for
instance — in PHP, you need to use the mysqli extension rather than
the old mysql extension. "


This is an interesting strategy in that all your queries would turn into 
CALL statements.


There are several reasons why I would NOT want to turn all my queries 
into stored procedures, though.  The main problem I have is that it is 
difficult to deploy stored procedures from DEV to PROD environments and 
have those deployments synchronized with the deployment of the web code. 
 SQL which is kept with the application is easily deployed when the 
application is deployed and the same goes for version control of the SQL 
if you are using something like Subversion to maintain change history.


So, I suppose you CAN perform an UPDATE and run a SELECT from a stored 
procedure, but this strategy is not much better than doing both calls 
from the client and still does not act like the RETURNING feature I was 
hoping for.


-- Dante

--
D. Dante Lorenso
[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Does MySQL have RETURNING in the language?

[Rob Wultsch]

On Wed, Oct 15, 2008 at 12:25 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote:
> There's an awesome feature that was added to PostgreSQL a while back called
> RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement
> behave like a SELECT statement.  You can do something like this:
>
>  INSERT INTO mytable (id, value)
>  VALUES (1, 'something')
>  RETURNING any_column_you_want;
>
> This would be equivalent to running something like this in MySQL:
>
>  INSERT INTO mytable (id, value)
>  VALUES (1, 'something');
>
>  SELECT any_column_you_want
>  FROM mytable
>  WHERE id = 1;
>
> Here is another example with an UPDATE query:
>
>  UPDATE mytable SET
>value = 'something'
>  WHERE id = 1
>  RETURNING id, other_number;
>
> The nice thing about this is that every insert or update can return any
> column you want (even multiple columns) without having to do the
> INSERT/UPDATE then turn around and perform another SELECT query.
>
> I want to use this because when I insert a value into a table, I don't
> always want to get the primary key returned to me.  Sometimes I want another
> column which may contain a candidate key and I'd like to avoid the
> round-trip and additional logic incurred with running multiple queries.
>
> Does RETURNING exist in any current release of MySQL or is it on the TODO
> list even?  If it's not, how can I go about asking to have it put on there?
>
> -- Dante
>
> --
> D. Dante Lorenso
> [EMAIL PROTECTED]


You can do your insert through a stored procedure and then at the end
do a select of those values.

http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-22-4-14
"22.4.14:  Can MySQL 5.0 stored routines return result sets?

Stored procedures can, but stored functions cannot. If you perform an
ordinary SELECT inside a stored procedure, the result set is returned
directly to the client. You need to use the MySQL 4.1 (or above)
client-server protocol for this to work. This means that — for
instance — in PHP, you need to use the mysqli extension rather than
the old mysql extension. "


-- 
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Does MySQL have RETURNING in the language?

[D. Dante Lorenso]

There's an awesome feature that was added to PostgreSQL a while back 
called RETURNING that allows you to make an INSERT, UPDATE, and DELETE 
statement behave like a SELECT statement.  You can do something like this:


  INSERT INTO mytable (id, value)
  VALUES (1, 'something')
  RETURNING any_column_you_want;

This would be equivalent to running something like this in MySQL:

  INSERT INTO mytable (id, value)
  VALUES (1, 'something');

  SELECT any_column_you_want
  FROM mytable
  WHERE id = 1;

Here is another example with an UPDATE query:

  UPDATE mytable SET
value = 'something'
  WHERE id = 1
  RETURNING id, other_number;

The nice thing about this is that every insert or update can return any 
column you want (even multiple columns) without having to do the 
INSERT/UPDATE then turn around and perform another SELECT query.


I want to use this because when I insert a value into a table, I don't 
always want to get the primary key returned to me.  Sometimes I want 
another column which may contain a candidate key and I'd like to avoid 
the round-trip and additional logic incurred with running multiple queries.


Does RETURNING exist in any current release of MySQL or is it on the 
TODO list even?  If it's not, how can I go about asking to have it put 
on there?


-- Dante

--
D. Dante Lorenso
[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MyISAM optimize

[Olexandr Melnyk]

Hello Olaf,

1) OPTIMIZE TABLE is the same as mysqlcheck with "--optimize" flag.

2) Both take care of large chunks of deleted data.

3) As mysqlcheck is just a frontend for OPTIMIZE TABLE command, it should be
replicated in either case.

2008/10/15, Olaf Stein <[EMAIL PROTECTED]>:
>
> Hi All,
>
> Just some simple questions I am somehow not able to find an answer to.
>
> - Does "optimize table tablename" do the same thing as mysqlcheck [options]
> db_name [tables] ?
>
> - Do they take care of large chunks of deleted data?
>
> - and I am assuming optimize table would replicate, mysqlcheck I would have
> to run on all slaves, correct?
>
> Thanks
> Olaf
>
> - Confidentiality Notice:
> The following mail message, including any attachments, is for the
> sole use of the intended recipient(s) and may contain confidential
> and privileged information. The recipient is responsible to
> maintain the confidentiality of this information and to use the
> information only for authorized purposes. If you are not the
> intended recipient (or authorized to receive information for the
> intended recipient), you are hereby notified that any review, use,
> disclosure, distribution, copying, printing, or action taken in
> reliance on the contents of this e-mail is strictly prohibited. If
> you have received this communication in error, please notify us
> immediately by reply e-mail and destroy all copies of the original
> message. Thank you.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
--
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/

Re: How to build sub-sequence ... AUTO_INCREMENT on a sub-key starting with a specific offset?

[Rob Wultsch]

I would do a muli key PK with a after insert trigger to that would
change widget_number 1 to 1000. Just my HO...

> I would use this combo as the primary key, but I hate doing joins with
> multiple primary keys, so I'll also keep the widget_id for the purpose of
> making joins easier.

Why? Both of these fields are ints, so the key length would rather small.

> I don't think the 'MAX' is optimized, though and maybe there is a better,
> more robust way to do this which is already built into MySQL that I don't
> know about.

MAX should be fast, assuming the field is indexed.

-- 
Rob Wultsch
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to build sub-sequence ... AUTO_INCREMENT on a sub-key starting with a specific offset?

[D. Dante Lorenso]

All,

I am developing a service in MySQL that models a service I've already 
built in PostgreSQL.  I'm trying to port over some of my ideas from that 
platform to MySQL.  Here's the setup:


Let's say I have 2 tables: 'account' and 'widget'.  Each of these tables 
have a primary key but the widget table references the account table 
with a foreign key on account_id:


--
CREATE TABLE `account` (
  `account_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(64) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `account_id` (`account_id`)
)
ENGINE=InnoDB AUTO_INCREMENT=1
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE TABLE `widget` (
  `widget_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(64) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `account_id` INTEGER(11) UNSIGNED NOT NULL,
  `widget_number` INTEGER(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`widget_id`),
  UNIQUE KEY `widget_id` (`widget_id`),
  UNIQUE KEY `widget_number` (`account_id`, `widget_number`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `widget_fk_account_id` FOREIGN KEY (`account_id`)
REFERENCES `account` (`account_id`)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE=InnoDB AUTO_INCREMENT=1
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
--

The problem is that I don't want customers to see the 'widget_id' column 
on the url like this:


http://.../app/widget/edit.php?widget_id=123456

Because if they see this, they can use this information to deduce how 
many widgets exist in my database.  Instead, I want to add another 
column to the widget table called 'widget_number' that contains an 
AUTO_INCREMENT column which is based on the 'account_id' in that table.


What this means is that every account contains a widget number 1000. 
And if you add another widget, you get widget number 1001, etc.  This 
way, the url will look like this:


http://.../app/widget/edit.php?widget_number=1000

And if I combine widget number 1000 with the account_id, I can uniquely 
identify an entry in the database.  In other words, I have a new primary 
key candidate of (account_id, widget_number).


I would use this combo as the primary key, but I hate doing joins with 
multiple primary keys, so I'll also keep the widget_id for the purpose 
of making joins easier.


My question is how can I get MySQL to automatically assign the 
widget_number values?  I want the first value for every account ID to be 
1000 and increment from there.  I almost need a trigger that does 
something like this:


  SELECT COALESCE(MAX(widget_number) + 1, 1000)
  INTO NEW.widget_number
  FROM widget
  WHERE account_id = NEW.account_id;

I don't think the 'MAX' is optimized, though and maybe there is a 
better, more robust way to do this which is already built into MySQL 
that I don't know about.


I am using InnoDB, so the solution needs to be transaction safe.  Help 
would be appreciated.


-- Dante

--
D. Dante Lorenso
[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MyISAM optimize

[Olaf Stein]

Hi All,

Just some simple questions I am somehow not able to find an answer to.

- Does "optimize table tablename" do the same thing as mysqlcheck [options]
db_name [tables] ?

- Do they take care of large chunks of deleted data?

- and I am assuming optimize table would replicate, mysqlcheck I would have
to run on all slaves, correct?

Thanks
Olaf

- Confidentiality Notice:
The following mail message, including any attachments, is for the
sole use of the intended recipient(s) and may contain confidential
and privileged information. The recipient is responsible to
maintain the confidentiality of this information and to use the
information only for authorized purposes. If you are not the
intended recipient (or authorized to receive information for the
intended recipient), you are hereby notified that any review, use,
disclosure, distribution, copying, printing, or action taken in
reliance on the contents of this e-mail is strictly prohibited. If
you have received this communication in error, please notify us
immediately by reply e-mail and destroy all copies of the original
message. Thank you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: time zone

[Mr. Shawn H. Corey]

On Wed, 2008-10-15 at 12:08 -0400, Olaf Stein wrote:
> You can use the convert_tz function for this
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function
> _convert-tz
> 
> On 10/15/08 12:03 PM, "Madan Thapa" <[EMAIL PROTECTED]> wrote:
> 
> > Hi,
> > 
> > Can  we make adjustments in mysql or php code to display time in php sites
> > in EST , although server runs on CDT ?
> > 
> > 
> > Thanks

I used a JavaScript function to get the browser to display the date and
time in its locale.  Not only is it in the locale TZ but in the
browser's language too.


-- 
Just my 0.0002 million dollars worth,
  Shawn

Linux is obsolete.
-- Andrew Tanenbaum


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: time zone

[Olaf Stein]

You can use the convert_tz function for this
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function
_convert-tz

On 10/15/08 12:03 PM, "Madan Thapa" <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> Can  we make adjustments in mysql or php code to display time in php sites
> in EST , although server runs on CDT ?
> 
> 
> Thanks

- Confidentiality Notice:
The following mail message, including any attachments, is for the
sole use of the intended recipient(s) and may contain confidential
and privileged information. The recipient is responsible to
maintain the confidentiality of this information and to use the
information only for authorized purposes. If you are not the
intended recipient (or authorized to receive information for the
intended recipient), you are hereby notified that any review, use,
disclosure, distribution, copying, printing, or action taken in
reliance on the contents of this e-mail is strictly prohibited. If
you have received this communication in error, please notify us
immediately by reply e-mail and destroy all copies of the original
message. Thank you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

time zone

[Madan Thapa]

Hi,

Can  we make adjustments in mysql or php code to display time in php sites
in EST , although server runs on CDT ?


Thanks

Joining subqueries

[Jerry Schwartz]

I tried to make a query that joins to subqueries:

 

SELECT discontinued.b

FROM

   (SELECT mrc_titles.title AS a

   FROM mrc_titles JOIN prod ON mrc_titles.title = prod.prod_title

JOIN pub ON prod.pub_id = pub.pub_id

   WHERE pub.pub_code = "MRC"

AND prod.prod_discont = 1) AS `discontinued`

 

 LEFT JOIN

   (SELECT mrc_titles.title AS b

   FROM mrc_titles JOIN prod ON mrc_titles.title = prod.prod_title

JOIN pub ON prod.pub_id = pub.pub_id

   WHERE pub.pub_code = "MRC"

AND prod.prod_discont = 0) AS `available`

   ON discontinued.a = available.b

WHERE available.b IS NULL

;

 

Basically I'm trying to find the `mrc_titles.title` records that only match
where `prod`.`prod_discont` = 1, excluding those that match
`prod`.`prod_discont` = 0.

 

I think the query makes sense to a human, but I get

 

ERROR 1137 (HY000): Can't reopen table: 'mrc_titles'

 

from MySQL 4.1.22-standard.

 

I didn't see anything about this limitation in the 4.x documentation
(although somehow it seems to ring a bell). What am I missing?

 

Regards,

 

Jerry Schwartz

The Infoshop by Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

 

860.674.8796 / FAX: 860.674.8341

 

  www.the-infoshop.com

  www.giiexpress.com

www.etudes-marche.com

 

Re: embedded client test scripts

[Joerg Bruehe]

Hi Alex, all!


Alex Katebi wrote:
> Any information on how to run the test scripts for the embedded client.

Call the test script, "mysql-test-run.pl", and pass it the option
   --embedded-server

It does not support replication or cluster, so you should add
   --skip-rpl --skip-ndbcluster


Regards,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  [EMAIL PROTECTED]
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trouble with large data in MySql

[Krishna Chandra Prajapati]

Hi,

1. Since requestid is  a primary key then why you are using distinct in your
query. It's not required.

2. Index needs to be created on logtime.

3. Explain will best describe where indexing is required.
4. In your query use group by a.logtime (date_format is not required)

Your system must have more rams and high processor (quad core).

This table has about 200Million records and it takes hours to come out. In
this table requestid is unique. What other indexes can I create to speed up
the response time of my query? What should I configure in mysql? Can you
help me with this?



I have 6GB RAM, 2 cores and 7200 RPM disk with 1TB size.


On Wed, Oct 15, 2008 at 4:21 PM, Karthik Pattabhiraman <
[EMAIL PROTECTED]> wrote:

>  Hi Krishna,
>
>I have one more question for you. My table schema is as follows
>
> create table RequestDO (
>
> country VARCHAR(256),
>
> device VARCHAR(256),
>
> devicemanufacturer VARCHAR(256),
>
> entryPage INTEGER,
>
> exitPage INTEGER,
>
> logicalPageName VARCHAR(3999),
>
> logtime TIMESTAMP,
>
> newrepeat VARCHAR(2),
>
> operator VARCHAR(256),
>
> pagename VARCHAR(3999),
>
> referer VARCHAR(256),
>
> requestid VARCHAR(256) [primary key],
>
> requesttype INTEGER,
>
> responseduration INTEGER,
>
> responsesize INTEGER,
>
> revenue INTEGER,
>
> sessionid VARCHAR(256),
>
> sitename VARCHAR(3999),
>
> source VARCHAR(3999),
>
> subscriberid VARCHAR(256),
>
> subscribermsisdn VARCHAR(256),
>
> subscribertype VARCHAR(2)
>
> ) engine=innodb;
>
> My current query is as follows:
>
>
>
> SELECT   DATE_FORMAT(A.LOGTIME, '%Y-%m-%d %H') AS LOGTIME,
>
> COUNT(DISTINCT A.REQUESTID) AS PAGE_VIEWS
>
> FROM RequestDO A
>
> WHERE  A.LOGTIME BETWEEN DATE_FORMAT('2008-09-15 00', '%Y-%m-%d %H') AND
>
> DATE_FORMAT('2008-09-15 23', '%Y-%m-%d %H')
>
> GROUP BY DATE_FORMAT(A.LOGTIME, '%Y-%m-%d %H')
>
>
>
> This table has about 200Million records and it takes hours to come out. In
> this table requestid is unique. What other indexes can I create to speed up
> the response time of my query? What should I configure in mysql? Can you
> help me with this?
>
>
>
> I have 6GB RAM, 2 cores and 7200 RPM disk with 1TB size.
>
>
>
> Thanks in advance
>
> Karthik
>
>
>
>
>
> *From:* Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED]
> *Sent:* Wednesday, October 15, 2008 3:43 PM
>
> *To:* Karthik Pattabhiraman
> *Cc:* mysql@lists.mysql.com
> *Subject:* Re: Trouble with large data in MySql
>
>
>
> Hi,
>
> It depends on your query using join and sort. Also, on how much ram you
> have. You can refer to huge_my.cnf
> You can find huge_my.cnf in the complied mysql-server binary.
> join 12 MB
> sort 8 MB
>
> On Wed, Oct 15, 2008 at 3:29 PM, Karthik Pattabhiraman <
> [EMAIL PROTECTED]> wrote:
>
> I have not specified any values for join_buffer_size and sort_buffer_size.
> What should be the optimal values for this?
>
>
>
> -Karthik
>
>
>
> *From:* Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED]
> *Sent:* Tuesday, October 14, 2008 10:03 AM
> *To:* Karthik Pattabhiraman
> *Cc:* mysql@lists.mysql.com
> *Subject:* Re: Trouble with large data in MySql
>
>
>
> Hi Karthik,
>
> You have many columns in a single table (although you can). But, if you
> break the below table into two tables ( Vertical partitioning). It will help
> you to run your query faster. You haven't shown join_buffer_size and
> sort_buffer_size.
>
> Secondly, check your all the queries with explain.
>
> On Wed, Oct 8, 2008 at 4:00 PM, Karthik Pattabhiraman <
> [EMAIL PROTECTED]> wrote:
>
> Hi,
>
>   We have 4 tables in which we have approximately 40 Million records
> per month. We are having trouble getting results from MySql as it takes
> about 4-5 hours to complete for each query. We are using this primarily
> for reporting purposes.
>
>
>
> My table schema is as follows
>
> SMAS Table:
>
>
>
> Column Name
>
> Type
>
> Key
>
> adnetwork
>
> adnetworkResponse
>
> campaignName
>
> clientRequest
>
> logkey
>
> loggingTime
>
> logmodule
>
> pageName
>
> propertyName
>
> requestId
>
> requestSystemId
>
> serverResponse
>
> sessionId
>
> siteName
>
> sucess
>
> systemId
>
> varchar(3999)
>
> text
>
> varchar(3999)
>
> varchar(3999)
>
> varchar(3999)
>
> timestamp
>
> varchar(3999)
>
> varchar(3999)
>
> varchar(3999)
>
> varchar(3999)
>
> varchar(3999)
>
> text
>
> varchar(256)
>
> varchar(3999)
>
> int(11)
>
> varchar(3999)
>
>
>
>
>
>
>
>
>
>
>
>  MUL
>
>
>
>
>
>
>
>
>
>
>
>
>
>  MUL
>
>
>
>
>
>
>
>
>
> Currently, SMAS table has 40Million records and our query takes 5 hours
> to execute.
>
>
>
> My my.cnf file is as follows and all tables are InnoDB.
>
>
>
> [mysqld]
>
> datadir=/mnt/data-store/mysql/data
>
> socket=/var/lib/mysql/mysql.sock
>
> user=mysql
>
> # Default to using old password format for compatibility with mysql 3.x
>
> # clients (those using the mysqlclient10 compatibility package).
>
> old_passwords=1
>
> log-error=/var/log/mysqld.log
>
> pid-file=/var/

RE: Trouble with large data in MySql

[Karthik Pattabhiraman]

Hi Krishna,

   I have one more question for you. My table schema is as follows

create table RequestDO (

country VARCHAR(256),

device VARCHAR(256),

devicemanufacturer VARCHAR(256),

entryPage INTEGER,

exitPage INTEGER,

logicalPageName VARCHAR(3999),

logtime TIMESTAMP,

newrepeat VARCHAR(2),

operator VARCHAR(256),

pagename VARCHAR(3999),

referer VARCHAR(256),

requestid VARCHAR(256) [primary key],

requesttype INTEGER,

responseduration INTEGER,

responsesize INTEGER,

revenue INTEGER,

sessionid VARCHAR(256),

sitename VARCHAR(3999),

source VARCHAR(3999),

subscriberid VARCHAR(256),

subscribermsisdn VARCHAR(256),

subscribertype VARCHAR(2)

) engine=innodb;

My current query is as follows:

 

SELECT   DATE_FORMAT(A.LOGTIME, '%Y-%m-%d %H') AS LOGTIME,  

COUNT(DISTINCT A.REQUESTID) AS PAGE_VIEWS  

FROM RequestDO A

WHERE  A.LOGTIME BETWEEN DATE_FORMAT('2008-09-15 00', '%Y-%m-%d %H') AND

DATE_FORMAT('2008-09-15 23', '%Y-%m-%d %H')

GROUP BY DATE_FORMAT(A.LOGTIME, '%Y-%m-%d %H')

 

This table has about 200Million records and it takes hours to come out.
In this table requestid is unique. What other indexes can I create to
speed up the response time of my query? What should I configure in
mysql? Can you help me with this?

 

I have 6GB RAM, 2 cores and 7200 RPM disk with 1TB size. 

 

Thanks in advance

Karthik

 

 

From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 15, 2008 3:43 PM
To: Karthik Pattabhiraman
Cc: mysql@lists.mysql.com
Subject: Re: Trouble with large data in MySql

 

Hi,

It depends on your query using join and sort. Also, on how much ram you
have. You can refer to huge_my.cnf
You can find huge_my.cnf in the complied mysql-server binary.
join 12 MB
sort 8 MB

On Wed, Oct 15, 2008 at 3:29 PM, Karthik Pattabhiraman
<[EMAIL PROTECTED]> wrote:

I have not specified any values for join_buffer_size and
sort_buffer_size. What should be the optimal values for this?

 

-Karthik

 

From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 14, 2008 10:03 AM
To: Karthik Pattabhiraman
Cc: mysql@lists.mysql.com
Subject: Re: Trouble with large data in MySql

 

Hi Karthik,

You have many columns in a single table (although you can). But, if you
break the below table into two tables ( Vertical partitioning). It will
help you to run your query faster. You haven't shown join_buffer_size
and sort_buffer_size. 

Secondly, check your all the queries with explain. 

On Wed, Oct 8, 2008 at 4:00 PM, Karthik Pattabhiraman
<[EMAIL PROTECTED]> wrote:

Hi,

  We have 4 tables in which we have approximately 40 Million records
per month. We are having trouble getting results from MySql as it takes
about 4-5 hours to complete for each query. We are using this primarily
for reporting purposes.



My table schema is as follows

SMAS Table:



Column Name

Type

Key

adnetwork

adnetworkResponse

campaignName

clientRequest

logkey

loggingTime

logmodule

pageName

propertyName

requestId

requestSystemId

serverResponse

sessionId

siteName

sucess

systemId

varchar(3999)

text

varchar(3999)

varchar(3999)

varchar(3999)

timestamp

varchar(3999)

varchar(3999)

varchar(3999)

varchar(3999)

varchar(3999)

text

varchar(256)

varchar(3999)

int(11)

varchar(3999)











 MUL













 MUL









Currently, SMAS table has 40Million records and our query takes 5 hours
to execute.



My my.cnf file is as follows and all tables are InnoDB.



[mysqld]

datadir=/mnt/data-store/mysql/data

socket=/var/lib/mysql/mysql.sock

user=mysql

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

innodb_data_home_dir=/mnt/data-store/mysql/data

innodb_data_file_path=ibdata1:15G:autoextend

innodb_buffer_pool_size=3G

max_connections=200

tmpdir=/mnt/data-store/tmp



[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid



My query is as follows on SMAS table

select campaignName, siteName, adnetwork,date_format(loggingTime
,'%d/%m/%Y') logDate, count(distinct requestid) adpages

from  SMAS where sucess   = 1  GROUP BY 1,2,3,4;



Any help will be highly appreciated.



-Karthik








-- 
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]




-- 
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]

Re: Trouble with large data in MySql

[Krishna Chandra Prajapati]

Hi,

It depends on your query using join and sort. Also, on how much ram you
have. You can refer to huge_my.cnf
You can find huge_my.cnf in the complied mysql-server binary.
join 12 MB
sort 8 MB

On Wed, Oct 15, 2008 at 3:29 PM, Karthik Pattabhiraman <
[EMAIL PROTECTED]> wrote:

>  I have not specified any values for join_buffer_size and
> sort_buffer_size. What should be the optimal values for this?
>
>
>
> -Karthik
>
>
>
> *From:* Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED]
> *Sent:* Tuesday, October 14, 2008 10:03 AM
> *To:* Karthik Pattabhiraman
> *Cc:* mysql@lists.mysql.com
> *Subject:* Re: Trouble with large data in MySql
>
>
>
> Hi Karthik,
>
> You have many columns in a single table (although you can). But, if you
> break the below table into two tables ( Vertical partitioning). It will help
> you to run your query faster. You haven't shown join_buffer_size and
> sort_buffer_size.
>
> Secondly, check your all the queries with explain.
>
> On Wed, Oct 8, 2008 at 4:00 PM, Karthik Pattabhiraman <
> [EMAIL PROTECTED]> wrote:
>
> Hi,
>
>   We have 4 tables in which we have approximately 40 Million records
> per month. We are having trouble getting results from MySql as it takes
> about 4-5 hours to complete for each query. We are using this primarily
> for reporting purposes.
>
>
>
> My table schema is as follows
>
> SMAS Table:
>
>
>
> Column Name
>
> Type
>
> Key
>
> adnetwork
>
> adnetworkResponse
>
> campaignName
>
> clientRequest
>
> logkey
>
> loggingTime
>
> logmodule
>
> pageName
>
> propertyName
>
> requestId
>
> requestSystemId
>
> serverResponse
>
> sessionId
>
> siteName
>
> sucess
>
> systemId
>
> varchar(3999)
>
> text
>
> varchar(3999)
>
> varchar(3999)
>
> varchar(3999)
>
> timestamp
>
> varchar(3999)
>
> varchar(3999)
>
> varchar(3999)
>
> varchar(3999)
>
> varchar(3999)
>
> text
>
> varchar(256)
>
> varchar(3999)
>
> int(11)
>
> varchar(3999)
>
>
>
>
>
>
>
>
>
>
>
>  MUL
>
>
>
>
>
>
>
>
>
>
>
>
>
>  MUL
>
>
>
>
>
>
>
>
>
> Currently, SMAS table has 40Million records and our query takes 5 hours
> to execute.
>
>
>
> My my.cnf file is as follows and all tables are InnoDB.
>
>
>
> [mysqld]
>
> datadir=/mnt/data-store/mysql/data
>
> socket=/var/lib/mysql/mysql.sock
>
> user=mysql
>
> # Default to using old password format for compatibility with mysql 3.x
>
> # clients (those using the mysqlclient10 compatibility package).
>
> old_passwords=1
>
> log-error=/var/log/mysqld.log
>
> pid-file=/var/run/mysqld/mysqld.pid
>
> innodb_data_home_dir=/mnt/data-store/mysql/data
>
> innodb_data_file_path=ibdata1:15G:autoextend
>
> innodb_buffer_pool_size=3G
>
> max_connections=200
>
> tmpdir=/mnt/data-store/tmp
>
>
>
> [mysqld_safe]
>
> log-error=/var/log/mysqld.log
>
> pid-file=/var/run/mysqld/mysqld.pid
>
>
>
> My query is as follows on SMAS table
>
> select campaignName, siteName, adnetwork,date_format(loggingTime
> ,'%d/%m/%Y') logDate, count(distinct requestid) adpages
>
> from  SMAS where sucess   = 1  GROUP BY 1,2,3,4;
>
>
>
> Any help will be highly appreciated.
>
>
>
> -Karthik
>
>
>
>
>
>
>
> --
> Krishna Chandra Prajapati
> MySQL DBA,
> Ed Ventures e-Learning Pvt.Ltd.
> 1-8-303/48/15, Sindhi Colony
> P.G.Road, Secunderabad.
> Pin Code: 53
> Office Number: 040-66489771
> Mob: 9912924044
> URL: ed-ventures-online.com
> Email-id: [EMAIL PROTECTED]
>



-- 
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]

RE: Trouble with large data in MySql

[Karthik Pattabhiraman]

I have not specified any values for join_buffer_size and
sort_buffer_size. What should be the optimal values for this?

 

-Karthik

 

From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 14, 2008 10:03 AM
To: Karthik Pattabhiraman
Cc: mysql@lists.mysql.com
Subject: Re: Trouble with large data in MySql

 

Hi Karthik,

You have many columns in a single table (although you can). But, if you
break the below table into two tables ( Vertical partitioning). It will
help you to run your query faster. You haven't shown join_buffer_size
and sort_buffer_size. 

Secondly, check your all the queries with explain. 

On Wed, Oct 8, 2008 at 4:00 PM, Karthik Pattabhiraman
<[EMAIL PROTECTED]> wrote:

Hi,

  We have 4 tables in which we have approximately 40 Million records
per month. We are having trouble getting results from MySql as it takes
about 4-5 hours to complete for each query. We are using this primarily
for reporting purposes.



My table schema is as follows

SMAS Table:



Column Name

Type

Key

adnetwork

adnetworkResponse

campaignName

clientRequest

logkey

loggingTime

logmodule

pageName

propertyName

requestId

requestSystemId

serverResponse

sessionId

siteName

sucess

systemId

varchar(3999)

text

varchar(3999)

varchar(3999)

varchar(3999)

timestamp

varchar(3999)

varchar(3999)

varchar(3999)

varchar(3999)

varchar(3999)

text

varchar(256)

varchar(3999)

int(11)

varchar(3999)











 MUL













 MUL









Currently, SMAS table has 40Million records and our query takes 5 hours
to execute.



My my.cnf file is as follows and all tables are InnoDB.



[mysqld]

datadir=/mnt/data-store/mysql/data

socket=/var/lib/mysql/mysql.sock

user=mysql

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

innodb_data_home_dir=/mnt/data-store/mysql/data

innodb_data_file_path=ibdata1:15G:autoextend

innodb_buffer_pool_size=3G

max_connections=200

tmpdir=/mnt/data-store/tmp



[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid



My query is as follows on SMAS table

select campaignName, siteName, adnetwork,date_format(loggingTime
,'%d/%m/%Y') logDate, count(distinct requestid) adpages

from  SMAS where sucess   = 1  GROUP BY 1,2,3,4;



Any help will be highly appreciated.



-Karthik









-- 
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]

Re: Problem with GROUP BY

[philip]

> Date: Tue, 14 Oct 2008 16:55:11 +0300
> From: Olexandr Melnyk <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED], mysql@lists.mysql.com
> Subject: Re: Problem with GROUP BY
> 
> http://jan.kneschke.de/projects/mysql/groupwise-max
> 
> 2008/10/14 Peter Brawley <[EMAIL PROTECTED]>
> 
> > Philip
> >
> >  mysql> SELECT number, MAX(event), name FROM info GROUP BY number;
> >>
> >
> > For discussion & examples see "Within-group aggregates" at
> > http://www.artfulsoftware.com/queries.php.

Thank you both very much for your replies. 

Of course the solution is 'obvious' now I know the answer but as a
relative newcomer to MySQL I had spent the best part of a day trying to
find it. 

TTFN,

   Philip Riebold, [EMAIL PROTECTED]   /"\
   Media Services\ /
   University College London  X  ASCII Ribbon Campaign
   Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail
   London, W1T 4JF
   +44 (0)20 7679 9259 (switchboard), 09259 (internal)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]