MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed Forums > Databases > MySQL Help
Reload this Page

What is the best way to test queires that may crash the database server?


Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old July 5th, 2012, 05:37 PM
zxcvbnm's Avatar
zxcvbnm zxcvbnm is online now
A Change of Season
Click here for more information.
  
Join Date: Mar 2004
Posts: 1,589 zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level) 
Time spent in forums: 2 Weeks 4 Days 19 h 36 m 10 sec
Reputation Power: 70
What is the best way to test queires that may crash the database server?
When dealing with multi-million row tables, there are times where I have to write queries that could return wrong results (due to wrong JOIN or bad sql practice).

What would be the best way to test such queries before running them on the server? I see sometimes a wrong query can jam the whole database server. Even "EXPLAIN query" can jam the server.

Does it make a difference to run the queries from out of phpmyadmin? For example in Putty?

Anyways, tips would be very appreciated.
__________________
Devshed people, please fix the spell check:

System is temporarily busy. Please try again in a few seconds.
Reply With Quote
  #2  
Old July 5th, 2012, 05:46 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,357 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 4 h 40 m 42 sec
Reputation Power: 4140
Quote:
Originally Posted by zxcvbnm
What would be the best way to test such queries before running them on the server?
test them on your desktop computer, where you have a test database with the exact same structure as your live server, but fewer rows
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3  
Old July 5th, 2012, 08:16 PM
zxcvbnm's Avatar
zxcvbnm zxcvbnm is online now
A Change of Season
Click here for more information.
  
Join Date: Mar 2004
Posts: 1,589 zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level) 
Time spent in forums: 2 Weeks 4 Days 19 h 36 m 10 sec
Reputation Power: 70
Quote:
Originally Posted by r937
test them on your desktop computer, where you have a test database with the exact same structure as your live server, but fewer rows


Does it make difference if it is via phpmyadmin or command line?
Reply With Quote
  #4  
Old July 5th, 2012, 09:01 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,357 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 4 h 40 m 42 sec
Reputation Power: 4140
Quote:
Originally Posted by zxcvbnm
Does it make difference if it is via phpmyadmin or command line?
nope

are you by any chance on a mac?
Reply With Quote
  #5  
Old July 5th, 2012, 10:27 PM
zxcvbnm's Avatar
zxcvbnm zxcvbnm is online now
A Change of Season
Click here for more information.
  
Join Date: Mar 2004
Posts: 1,589 zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level) 
Time spent in forums: 2 Weeks 4 Days 19 h 36 m 10 sec
Reputation Power: 70
Quote:
Originally Posted by r937
nope

are you by any chance on a mac?


I have an imac and a mac book pro as well.

Why?
Last edited by zxcvbnm : July 5th, 2012 at 10:32 PM.
Reply With Quote
  #6  
Old July 6th, 2012, 04:34 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,357 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 4 h 40 m 42 sec
Reputation Power: 4140
because you're a musician and you're using phpmyadmin

if you were on a pc, i'd recommend you switch to heidisql
Reply With Quote
  #7  
Old July 6th, 2012, 07:15 PM
zxcvbnm's Avatar
zxcvbnm zxcvbnm is online now
A Change of Season
Click here for more information.
  
Join Date: Mar 2004
Posts: 1,589 zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level)zxcvbnm User rank is Second Lieutenant (5000 - 10000 Reputation Level) 
Time spent in forums: 2 Weeks 4 Days 19 h 36 m 10 sec
Reputation Power: 70
Quote:
Originally Posted by r937
because you're a musician and you're using phpmyadmin

if you were on a pc, i'd recommend you switch to heidisql


Logic studio and Reason (music things) are on Mac, phpmyadmin is here on this pc (Windows 7).

I check heidisql.

Thanks
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MySQL Help > What is the best way to test queires that may crash the database server?

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap