' . . ' can't insert into a date field,but update can

Hi, table table1 int1 int auto_increament , date date not null default '-00-00' 1) insert into table1 (date) values('. . ') // shows Incorrect date value: '. . ' for column date' at row 1 // '. . ' ==> space(4)+"."+space(2)+"."+space(2) 2) update

Re: Question about mysql procedures

On Fri, 11 Apr 2008 14:52:30 -0700 "Rob Wultsch" <[EMAIL PROTECTED]> wrote: > On Fri, Apr 11, 2008 at 2:15 PM, Joshua D. Drake > <[EMAIL PROTECTED]> wrote: > > Hello, > > > > Can MySQL functions/stored procedures access database data? > > > > Joshua D. Drake > > Yes. Is there something in parti

Re: Some way/tool to do this with database scripts backups

hello let me explain me more think a table how this case cliente is customer/client in english Field Type Null Key Default Extra --- --- -- -- idCliente varchar(11) NO PRI

Re: Question about mysql procedures

At 2:15 PM -0700 4/11/08, Joshua D. Drake wrote: Hello, Can MySQL functions/stored procedures access database data? Yes, with some limitations. You will want to read this section to see whether what you want to do is restricted: http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.htm

Re: Question about mysql procedures

On Fri, Apr 11, 2008 at 2:15 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > Hello, > > Can MySQL functions/stored procedures access database data? > > Joshua D. Drake Yes. Is there something in particular you are looking to do? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL Gener

Question about mysql procedures

Hello, Can MySQL functions/stored procedures access database data? Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to

Re: SQL query question for GROUP BY

On Fri, Apr 11, 2008 at 1:01 PM, Victor Danilchenko <[EMAIL PROTECTED]> wrote: > Oooh, this looks evil. It seems like such a simple thing. I guess > creating max(log_date) as a field, and then joining on it, is a solution -- > but my actual query (not the abridged version) is already half a

Re: SQL query question for GROUP BY

Oooh, this looks evil. It seems like such a simple thing. I guess creating max(log_date) as a field, and then joining on it, is a solution -- but my actual query (not the abridged version) is already half a page long. I think at this point, unless someone else suggests a better solution, th

Re: SQL query question for GROUP BY

On Fri, Apr 11, 2008 at 11:46 AM, Victor Danilchenko <[EMAIL PROTECTED]> wrote: > GROUP BY seems like an obvious choice; 'GROUP BY username', to be > exact. However, this seems to produce not the last row's values, but ones > from a random row in the group. Under most databases your query i

����������� ����������

Now and then, shadow defined by necromancer find lice on dissident inside.

SQL query question for GROUP BY

Hi all, I trying to run a query where, after doing a UNION on two different SELECTs, I need to sort the result by username and log_date fields, and then grab the last entry for each username ('last' as determined by the ordering of the log_date field, which is a datetime). GROUP BY

Re: Why is this delete so slow? ( 90 seconds per 100 records)

Ryan Stille wrote: Thanks for the help, I rewrote it as a subselect and it deleted all 10K records in two seconds. DELETE subTable FROM subTable LEFT OUTER JOIN parentTable ON subTable.parentID = parentTable.ID WHERE parentTable.ID IS NULL -Ryan Whoops, I meant that I rewrote it as a JOIN.

Re: Why is this delete so slow? ( 90 seconds per 100 records)

Thanks for the help, I rewrote it as a subselect and it deleted all 10K records in two seconds. DELETE subTable FROM subTable LEFT OUTER JOIN parentTable ON subTable.parentID = parentTable.ID WHERE parentTable.ID IS NULL -Ryan -- MySQL General Mailing List For list archives: http://lists.mysq

Re: MySQl 5.0 optimization problem

First of all, my bad -- I forgot to mention that I use MyISAM. mysql> show table status from example like 'leads'\G *** 1. row *** Name: leads Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 1

RE: Why is this delete so slow? ( 90 seconds per 100 records)

-Original Message- From: Ryan Stille [mailto:[EMAIL PROTECTED] Subject: Re: Why is this delete so slow? ( 90 seconds per 100 records) Also the table seems to be locked while running this delete... thats not going to be very good when I need to run it on production. Is there a way to have

Re: Two MySql servers, but very different performances for a SELECT JOIN

2008/4/11 <[EMAIL PROTECTED]>: > Hi, > > I have 2 MySql server instances. One which is 5.0.27/Debian, another > 5.0.32/Solaris. > > Both instances have the same data in the database. > > And I'm doing a select: > SELECT media.* FROM media,country,content WHERE country.id='Germany' AND > coun

Re: Why is this delete so slow? ( 90 seconds per 100 records)

On Fri, Apr 11, 2008 at 8:49 AM, Ryan Stille <[EMAIL PROTECTED]> wrote: > I'm trying to delete some orphaned records from a table that has about 150K > records. Here is my delete: > > |CREATE TEMPORARY TABLE deleteids AS ( > SELECT subTable.ID from subTable > LEFT OUTER JOIN parentTable ON su

Re: Why is this delete so slow? ( 90 seconds per 100 records)

Also the table seems to be locked while running this delete... thats not going to be very good when I need to run it on production. Is there a way to have it not be locked during this delete? I'm thinking of creating a script to delete in 10 row increments until they are all gone. Thinking

Re: MySQl 5.0 optimization problem

On Fri, Apr 11, 2008 at 6:47 AM, Ben Clewett <[EMAIL PROTECTED]> wrote: > A COUNT() forces the system to read every row in order to count them... That is not strictly the case. A count(field) can use an index scan rather than a sequential scan, which may or may not be faster. Also some count(field)

Re: MySQl 5.0 optimization problem

Hi, Can you please post your query? I also need to know your table type as different settings effect different table types? You are right that a SELECT COUNT(*) WHERE field = 'value' should hit the index, but does depend on your query. You might also try EXPLAIN before your query, which wi

Why is this delete so slow? ( 90 seconds per 100 records)

I'm trying to delete some orphaned records from a table that has about 150K records. Here is my delete: |CREATE TEMPORARY TABLE deleteids AS ( SELECT subTable.ID from subTable LEFT OUTER JOIN parentTable ON subTable.ID = parentTable.ID WHERE parentTable.ID IS NULL ); DELETE FROM subTable WH

Re: MySQl 5.0 optimization problem

On Fri, April 11, 2008 06:47, Ben Clewett wrote: > Are you using MyIsam or InnoDB? Or something else? > > In either case the speed to get a COUNT() is largely down to the speed > if your disks and size of disk caching. A COUNT() forces the system to > read every row in order to count them, and an

Re: MySQl 5.0 optimization problem

Are you using MyIsam or InnoDB? Or something else? In either case the speed to get a COUNT() is largely down to the speed if your disks and size of disk caching. A COUNT() forces the system to read every row in order to count them, and any large table is probably larger than your caches. I

MySQl 5.0 optimization problem

Hi, I am trying to optimize our DB server. We have one table which has 1.3M entries, and the keys are GUIDs (so the key space is large). However, I have it all indexed. The performance was iffy, though, so I increased memory allocation, and the searches on the indexed fields seem to b

Re: Query runs very sloooow

A late followup on this, so I top post to keep the history intact. The composite primary key was the problem. Or rather, the missing individual indexes for tag_id and ad_id. We also changed to INNER JOINs instead, but that didn't affect the performance. Thanks for all suggestions! On We

RE: Data transfer

HI Option 1 If you are using PHP, you can do this very simply using CRON task. Make a field for to update either your first operation is successful (eg. Update the field value to 1 ). Run a CRON job in particular interval if the field updated as 1 then call your second operation query and make

Query Confusion!

I am confused ( nothing new there), what I thought was a simple search is proving not to be so, Can anyone tell me why this query for the word 'plus': mysql> SELECT * -> FROM booktitles -> WHERE MATCH (category , publisher , bookTitle , author) -> AGAINST (CONVERT( _utf8'plus'USING latin1 ) -> I

RE: Data transfer

HI Option 1 If you are using PHP, you can do this very simply using CRON task. Make a field for to update either your first operation is successful (eg. Update the field value to 1 ). Run a CRON job in particular interval if the field updated as 1 then call your second operation query and make t

Re: Data transfer

does your development server have only one database or multiple database. regards anandkl On 4/11/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > Hi, > > What ever queries are executed on 5 mysql server with multiple database > (more than one database on each mysql server). I have t

Two MySql servers, but very different performances for a SELECT JOIN

Hi, I have 2 MySql server instances. One which is 5.0.27/Debian, another 5.0.32/Solaris. Both instances have the same data in the database. And I'm doing a select: SELECT media.* FROM media,country,content WHERE country.id='Germany' AND country.detail_tid=content.tid AND content.id=media.content

Data transfer

Hi, What ever queries are executed on 5 mysql server with multiple database (more than one database on each mysql server). I have to reflect all the changes on 1 mysql server (developement server for developers) Initially, I thought to take take the queries from bin-log and execute on development