Re: Automated SQL checker?

2017-04-13 Thread SSC_perl 
> On Apr 12, 2017, at 3:38 PM, Ronan McGlue  wrote:
> 
> Enable the slow log on the DB.

Thanks Ronan.  That sounds like it would be beneficial.  I take it 
you’re referring to a setting on the server, though.  If that’s the case, I 
don’t think I can do that as I’m on shared hosting (unless I’m misunderstanding 
you).  When I get some free time (hah!) maybe I’ll set up MySQL on my laptop.  
Unfortunately, installing DBI on OS X can be a nightmare, so I don’t know 
if/when that will happen.

Thanks again,
Frank
https://www.surfshopcart.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Automated SQL checker?

2017-04-12 Thread Ronan McGlue 

Hi Frank,

Enable the slow log on the DB. Any queries which are above the threshold 
variable ( |long_query_time| 
 
) will be logged .


Default is 10s, so I assume you'd want to drop this down to 1-2 ( or 
less ) for a shopcart type response time?


You can then use mysqldumpslow command to aggregate this file after a 
period of  time to identify queries which are greater than long_query_time.


This is typically the first step in analyzing inefficient queries ( or 
more accurately, queries which are candidates for improvement ) in a 
running DB. eg


*|mysqldumpslow -s c -t 5 |*

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

Regards


Ronan McGlue

MySQL Support



On 13/04/2017 01:06, SSC_perl wrote:

On Apr 11, 2017, at 1:26 PM, Michael Munger  wrote:

Use MySQL workbench.

Thanks, Michael.  I played with it some already and it looks like it 
will give me a lot to work with.

Will it also let me know if field types are wrong for the given 
information type, or is that asking for too much?

Frank

https://www.surfshopcart.com


--
Regards

Ronan McGlue
MySQL Support
Oracle Australia

Re: Automated SQL checker?

2017-04-12 Thread SSC_perl 
> On Apr 11, 2017, at 1:26 PM, Michael Munger  
> wrote:
> 
> Use MySQL workbench.

Thanks, Michael.  I played with it some already and it looks like it 
will give me a lot to work with.

Will it also let me know if field types are wrong for the given 
information type, or is that asking for too much?

Frank

https://www.surfshopcart.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Automated SQL checker?

2017-04-11 Thread Michael Munger 
Use MySQL workbench.

Add query to the editor, execute, check results.

Then, use the "Execution plan" feature to see how things are executing and look 
for bad things (Cartesian products, stupid loops, etc...)

See also: Query stats.


Michael Munger, dCAP, MCPS, MCNPS, MBSS
High Powered Help, Inc.
Microsoft Certified Professional
Microsoft Certified Small Business Specialist
Digium Certified Asterisk Professional
mich...@highpoweredhelp.com


-Original Message-
From: SSC_perl [mailto:p...@surfshopcart.com] 
Sent: Tuesday, April 11, 2017 4:04 PM
To: mysql mailing list 
Subject: Automated SQL checker?

Is there a way, perhaps with a script or a service, that one can check 
MySQL code to see about making it more efficient?  I maintain an open source 
shopping cart written in Perl and it’s been awhile since the SQL has been 
worked on, so I want to see if it could use some updating.

Thanks,
Frank

http://www.surfshopcart.com
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql