Whenever SQL profiling comes in mind, focus goes to two below sections:
- GUI tools like – MONyog, Neor Profile SQL
- set profiling =1; Execute Query; show profiles; show profile;
I am really confuse between these two about SQL profiling.
- Which one is correct or it is something other than these two?
- SQL profiling in MySQL different than SQL profiling in common RDBMS?
- How SQL profiling is useful and why we do we use it?
Best Answer
The whole point about profiling is to ascertain where your system is spending time. This can often give developers some clues as to how to optimise their queries (by adding indexes for example).
The 600 pound gorilla in this area is Oracle's Wait Interface (OWI) which is one of the most sophisticated monitoring and profiling tools available to database professionals. MySQL has made strides in the last couple of years with the Perfomance Schema (commonly written P_S).
Again, the P_S allows the DBA/Developer to see what files are being accessed - and other information, i.e. where the system is spending time - knowing this can be very helpful for tuning or hardware purchasing decisions.
From here, MySQL's SHOW PROFILE command is now being deprecated in favour of the P_S.
I don't know the Neor Profile SQL 2 tool, but it looks interesting, although not Open Source :-(. There's also MySQL Workbench or Percona's tools. It's important to remember that none of these tools is a "silver bullet" - they just use the same information that's available in the P_S - they might present it nicely, but you're better off learning the P_S than a particular tool.
As for MySQL being different to Oracle (and to PostgreSQL, or Microsoft SQL Server, IBM DB2 or Firebird), then yes, there are differences between them that arise from the product's history but they all try and give some information to DBAs and Developers as to how the server is performing your query. Some more than others - Oracle's is very well developed, MySQL getting better (unsure of status of others). For the principles check out either Garcia-Molina or Lahdenmaki and Leach. You can then go to books on the particular server you are interested in/have to work with.