optimize sql statement?

What is the best way to optimize an SQL statement?

- Run "EXPLAIN SELECT ..."
- Check the "Query profile" checkbox on the right before executing your query.

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

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!

Value Data: 0

Ups, there we have the cause. Just delete that value or set it to 100 please. Exit heidisql before doing that.

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

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.

:-(

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.

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

Thank You...

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.

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. "

Query profiling is disabled now on old servers, since r3584.