RE: Question about DELETE

.com] Sent: 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 m

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

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 in

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 table

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

512M innodb_log_file_size=170M 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@lis

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 chi

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; DECL

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 generate

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 I

RE: select statement with variable for table_reference?

4 rows in set (0.27 sec) Query OK, 0 rows affected (0.28 sec) Hope this helps. Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-

RE: select statement with variable for table_reference?

ogy 1700 Pratt Drive 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_refe

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 Statem

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 M

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 PROTEC

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 Im

RE: MySQL Date Issues

se [mailto:[EMAIL PROTECTED] Sent: Monday, December 04, 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

RE: MySQL Date Issues

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: MySQL Date Issues Not sure this is your problem, but do you hav

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; Pooli

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 col

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 Mess

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 c

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

got it working so thanks very much. Randall Price VT.SETI.IAD.MIG:Microsoft Implementation Group http://vtmig.vt.edu [EMAIL PROTECTED] (540) 231-4396 -Original Message- From: Ing. Edwin Cruz [mailto:[EMAIL PROTECTED] Sent: Thursday, March 02, 2006 3:03 PM To: Price, Randall Subject:

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 PROCEDURE

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: mysql@lists.

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: C

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 ret