RE: Question about DELETE

] On Behalf Of Johan De Meersman Sent: Thursday, March 18, 2010 6:48 AM To: Ananda Kumar Cc: Price, Randall; [MySQL] Subject: Re: Question about DELETE Given that OP is talking about a single delete statement, I'm gonna be very surprised if he manages to squeeze an intermediate commit

RE: Question about DELETE

: Thursday, March 18, 2010 10:11 AM To: Price, Randall Cc: Johan De Meersman; Ananda Kumar; [MySQL] Subject: RE: Question about DELETE Hi Randall, If you're talking about processes that are taking that long, then running SHOW PROCESSLIST several times during the operation should give you a rough idea

RE: Question about DELETE

: Thursday, March 18, 2010 11:15 AM To: Price, Randall Cc: Ian Simpson; Johan De Meersman; [MySQL] Subject: Re: Question about DELETE delete will also cause the undo(before image) to be generated, in case u want to rollback. This will also add up to the delete completion time. After each mass delete

Question about DELETE

Hello, I have a simple question about deleting records from INNODB tables. I have a master table with a few child tables linked via Foreign Key constraints. Each table has several indexes as well. My question is: if I delete many records in a single delete statement (i.e., DELETE FROM

Very slow delete for Master / Child tables with millions of rows

I am experiencing very slow deletes when I delete a record from a master table and have cascading deletes on two detail tables. I have an application that looks for records in the master table that are older than X days and delete them. The cascasing deletes then handles deleting all the

Very slow delete for Master / Child tables with millions of rows

innodb_thread_concurrency=10 query_cache_type=1 long_query_time=2 log-slow-queries=Slow.log innodb_file_per_table innodb_lock_wait_timeout=500 From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: Thursday, March 11, 2010 11:02 AM To: Price, Randall Cc: mysql@lists.mysql.com Subject: Re: Very slow

RE: Displaying date/time

Here is a MySQL stored procedure that I have used to format the difference between two dates: CREATE definer=`ro...@`127.0.0.1` FUNCTION `sp_maint_PeriodLength`(dt1 DATETIME, dt2 DATETIME) RETURNS char(128) CHARSET latin1 BEGIN DECLARE yy, mm, d0, dd, hh, mi, ss, t1 BIGINT;

Best way to synchronize two database schemas

I have a two databases, one in a production environment (let's call it db_prod) and the other in a testing environments (Let's call it db_test). What is the best way to synchronize the database schemas? db_test has had a few indexes and constraints added to several tables and I need to

RE: CONCAT doesn't work with NULL?

Could you use something like this (untried): SELECT CONCAT(COALESCE(r.first_name, ''), ' ', COALESCE(r.last_name,''), '\n', COALESCE(r.organization, ''), '\n', COALESCE(r.title,''), '\n', COALESCE(a.address1, ''), '\n',

RE: select unique ?

Since both of these work, I was wondering which one would be faster. Here is an EXPLAIN on a similar test I did on one of my test tables. (NO index on field1, WinXP, MySQL 5.0.41-community-nt, SQLyog query window) SELECT COUNT(*) FROM tblClients (1660 row(s) returned) (0 ms taken)

RE: Syntax Error in Stored Procedure

Tom, I think the problem might be that you have to put all the DECLARE statements at the top before the other statements. It seems like I had a similar problem with this once. Hope this helps. Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech

RE: select statement with variable for table_reference?

] Phone: (540) 231-4396 -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 27, 2007 2:53 PM To: Ed Lazor; Price, Randall Cc: mysql@lists.mysql.com Subject: Re: select statement with variable for table_reference? Yes it is a good idea to store in a table

RE: select statement with variable for table_reference?

Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 6:02 PM To: Price, Randall; mysql@lists.mysql.com Subject: RE: select statement with variable for table_reference? I'm honestly

RE: select statement with variable for table_reference?

What about using PREPARED STATEMENTS in a stored procedure? Something like: CREATE PROCEDURE `GetInventory`( IN strTableName VARCHAR(50), ...) BEGIN SET @strSQL = CONCAT(SELECT * FROM , strTableName); ... ... PREPARE Statement FROM @strSQL; EXECUTE

MySqlException not trapped in try/catch block

Hello, I have a long running query that generates a MySqlException due to the query timing out. It takes about 55 seconds to run on our production server. The MySqlException is NOT being trapped in my try/catch block. Here is the error: MySql.Data.MySqlClient.MySqlException was unhandled

RE: --xml or --html output to file

Hello John, This works for me as well only I couldn't get the ~/test_file.html syntax to work so I changed it to C:\test_file.html and it worked. Thanks, Randall Price -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 08, 2007 6:20 PM To: [EMAIL

RE: Is it possible to either update or insert in a single query?

You could try a stored procedure that either inserts a new row or updates an existing row: CREATE PROCEUDRE InsertOrUpdateRecord(IN NewID INT, ... other params ... ) BEGIN IF NOT EXISTS (SELECT ID FROM myTable WHERE ID = NewID) THEN BEGIN INSERT INTO myTable () END; ELSE

RE: WHERE (NOT) EXISTS problem

This also works... SELECT name FROM people AS p JOIN people_city_map AS pcm ON (p.id = pcm.pid) WHERE pcm.cid = 1; Not sure if it is any faster (or better) than what Chris suggested but it seems to be faster on my machine. Randall Price Secure Enterprise Technology Initiatives Microsoft

RE: MySQL Date Issues

Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Price, Randall [mailto:[EMAIL PROTECTED] Sent: Friday, December 01, 2006 5:17 PM To: Jesse; MySQL List Subject: RE

RE: MySQL Date Issues

, 2006 10:44 AM To: Price, Randall Subject: Re: MySQL Date Issues This displays it, but it displays both the date time. Is there a format string that will display just the date part, and then just the time part? Thanks, Jesse - Original Message - From: Price, Randall [EMAIL PROTECTED

RE: MySQL Date Issues

Not sure this is your problem, but do you have the Allow zero datetime option on your connect string? For example, connectionString=Server=localhost; User ID=some_user; Password=some_password; Database=some_database;

RE: Book Recommendation

Here are a few websites you can check out: http://www.freewebmasterhelp.com/tutorials/phpmysql http://us2.php.net/mysql http://www.php-mysql-tutorial.com/ http://www.weberdev.com/ Hope these help. Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia

RE: Returning value after insert

You could use a stored procedure to do the INSERT and then return the value from SELECT statement. For example, DELIMITER $$; DROP PROCEDURE IF EXISTS `test`.`spINSERTandSELECT`$$ CREATE PROCEDURE `test`.`spINSERTandSELECT` (IN strFirstName VARCHAR(20),

RE: Count Fields of a Table

David, For the count of columns in a table: SELECT count(information_schema.columns.column_name) FROM information_schema.columns WHERE information_schema.columns.table_schema = 'database_name' ANDinformation_schema.columns.table_name = 'table_name' For the names of the

RE: SUM in WHERE

I tried it also with 5.0.24-community-nt and it still didn't work! Randall Price Microsoft Implementation Group Secure Enterprise Computing Initiatives Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original

RE: Query Speed

Hi Jesse, I am not 100% sure cause I have only been using MySQL for ~6 months but I do read this mailing list everyday and have learned a lot. I believe that InnoDB tables to not maintain a count(*) for the tables so it has to physically count the rows. I believe MyISAM tables do maintain that

RE: Qyery help - pass string to stored procedure for IN clause - FIXED!

-Original Message- From: Ing. Edwin Cruz [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 3:03 PM To: Price, Randall Subject: RE: Qyery help - pass string to stored procedure for IN clause What abaut this: CREATE PROCEDURE spGetNames (IN strNames VARCHAR(255)) BEGIN

Qyery help - pass string to stored procedure for IN clause

I have a SELECT query that looks similar to the following: SELECT FirstName, LastName FROM myTable WHERE LastName IN ('PRICE', 'SMITH'); What I want to do is create a stored procedure for this SELECT query similar to the following: CREATE

RE: using replace() to remove tab chars

Try the following: REPLACE(products_description, CHAR(9), ) Randall Price VT.SETI.IAD.MIG:Microsoft Implementation Group http://vtmig.vt.edu [EMAIL PROTECTED] -Original Message- From: Vince LaMonica [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 01, 2006 10:44 AM To:

RE: returning empty columns

Chris, See if this works for you: SELECT ' ' as 'Call' , concat(FName, ' ', LName) as Name , ' ' as 'Mar-6' , ' ' as 'Mar-13' , ' ' as 'Mar-20' , ' ' as 'Mar-27' FROM table ORDER BY LName, FName Notice that there is a single blank space being

How to pass a string expression into a stored procedure

I have a SELECT query that looks similar to the following: SELECT FirstName, LastName FROM myTable WHERE LastName IN ('PRICE', 'SMITH'); What I want to do is create a stored procedure for this SELECT query similar to the following: