optimize sql statement?
| User, date | Message |
|---|---|
|
Written by djlerman
3 years ago Category: Running SQL scripts 10 posts since Wed, 30 Jun 10 |
What is the best way to optimize an SQL statement? |
|
Written by ansgar
3 years ago 3950 posts since Fri, 07 Apr 06 |
- Run "EXPLAIN SELECT ..." - Check the "Query profile" checkbox on the right before executing your query. |
|
Written by djlerman
3 years ago 10 posts since Wed, 30 Jun 10 |
There is nothing to the right of the query. I see what should be there in the screen shots on the heidisql.com site but it is not showing up on my machine. I have HeidiSQL v.5.1.0.3575 |
|
Written by ansgar
3 years ago 3950 posts since Fri, 07 Apr 06 |
You don't see the query helpers box with snippets, query profile etc.? Could you please check in regedit.exe what this value says: HKEY_CURRENT_USER\Software\HeidiSQL\queryhelperswidth ? Thanks! |
|
Written by djlerman
3 years ago 10 posts since Wed, 30 Jun 10 |
Value Data: 0 |
|
Written by ansgar
3 years ago 3950 posts since Fri, 07 Apr 06 |
Ups, there we have the cause. Just delete that value or set it to 100 please. Exit heidisql before doing that. |
|
Written by djlerman
3 years ago 10 posts since Wed, 30 Jun 10 |
Changed it to -- Value Date: 100 I did a simple select, checked the box next to Query Profile and when I executed the query. Can not run any selects while the check box is checked. The following error report is the result. date/time : 2010-10-29, 10:59:07, 890ms computer name : AMD-DEVELOPMENT user name : Donovan <admin> registered owner : DL / n/a operating system : Windows XP Service Pack 3 build 2600 system language : English system up time : 2 days 1 hour program up time : 16 seconds processor : AMD Athlon(tm) 64 Processor 3500+ physical memory : 237/958 MB (free/total) free disk space : (C:) 130.56 GB display mode : 1280x1024, 32 bit process id : $95c allocated memory : 14.23 MB executable : heidisql.exe exec. date/time : 2010-10-28 10:02 version : 5.1.0.3575 compiled with : Delphi 2010 madExcept version : 3.0k callstack crc : $0bb566d0, $2c97773e, $2c97773e exception number : 1 exception class : EDatabaseError exception message : SQL Error (1193): Unknown system variable 'profiling'. main thread ($840): 0075b9f2 heidisql.exe mysql_connection 682 +18 TMySQLConnection.Query 006c0706 heidisql.exe Main 2208 +43 TMainForm.actExecuteQueryExecute 00499e77 heidisql.exe Classes TBasicAction.Execute 00555e39 heidisql.exe ActnList TContainedAction.Execute 00556bf4 heidisql.exe ActnList TCustomAction.Execute 00499d3b heidisql.exe Classes TBasicActionLink.Execute 00526cbc heidisql.exe Menus TMenuItem.Click 00528707 heidisql.exe Menus DoClick 005287f3 heidisql.exe Menus TMenu.IsShortCut 0054178c heidisql.exe Controls TWinControl.IsMenuKey 00541801 heidisql.exe Controls TWinControl.CNKeyDown 0053ab60 heidisql.exe Controls TControl.WndProc 0053f428 heidisql.exe Controls TWinControl.WndProc 005de934 heidisql.exe SynEdit 6197 +22 TCustomSynEdit.WndProc 0053eac8 heidisql.exe Controls TWinControl.MainWndProc 0049a524 heidisql.exe Classes StdWndProc 7e4292de USER32.dll SendMessageW 0056af44 heidisql.exe Forms TApplication.IsKeyMsg 0056b205 heidisql.exe Forms TApplication.ProcessMessage 0056b26e heidisql.exe Forms TApplication.HandleMessage 0056b599 heidisql.exe Forms TApplication.Run 007a6742 heidisql.exe heidisql 66 +15 initialization |
|
Written by ansgar
3 years ago 3950 posts since Fri, 07 Apr 06 |
Unknown system variable 'profiling' Ok, so you're on some pre 5.0 server and cannot use profiling I'm afraid. However, you should make yourself familar with the EXPLAIN SELECT ... result, so you can analyze your query. |
|
Written by djlerman
3 years ago 10 posts since Wed, 30 Jun 10 |
:-( Do you have any suggestions on how to understand 'EXPLAIN SELECT' I've looked over a few things online and nothing is very clear as to how to use the results to optimize a query. |
|
Written by ansgar
3 years ago 3950 posts since Fri, 07 Apr 06 |
Read this: http://dev.mysql.com/doc/refman/5.0/en/explain-output.html For a quick start, read this: - SELECT is faster when "rows" value is low - SELECT is faster when "key" shows that some key is used |
|
Written by djlerman
3 years ago 10 posts since Wed, 30 Jun 10 |
Thank You... |
|
Written by Yuri RS
3 years ago 1 posts since Sun, 07 Nov 10 |
I use MySQL version 5.0.18-nt-log and have the same problem reported by djlerman. Maybe you could check de MySQL version before running profiling. --- Update: The new profiler became available in the 5.0.37 version of MySQL. |
|
Written by djlerman
3 years ago 10 posts since Wed, 30 Jun 10 |
Per Anse: Its the version of MySQL that my company has installed. "Ok, so you're on some pre 5.0 server and cannot use profiling I'm afraid. However, you should make yourself familar with the EXPLAIN SELECT ... result, so you can analyze your query. " |
|
Written by ansgar
3 years ago 3950 posts since Fri, 07 Apr 06 |
Query profiling is disabled now on old servers, since r3584. |
|
Please login to leave a reply, or register at first. |