Hi, I thought I’d just make a small addition to my previous post on variables, as it seems to be very popular with visitors to the site. In my previous entry I spoke mainly of the use of session variables. These are set in the scope of your session using Mysql – once your connection is terminated the variable goes out of scope. Before I go onto global variables, I’d like to show the best use I’ve seen recently of a session variable. It’s used as an alternative to the rownum facility, which is available in Oracle but not in Mysql.
Here’s an example of it in use:

Code:

The above code avoids the need to write an initial line that assigns a value to the variable prior to running the select statement ie SET @rownum := 0;

Now on to Global variables, which are very useful for people trying to improve performance of their Mysql database. These exist across connections, and are often set on the configuration file my.cnf You can normally see the status of these variables by using the SHOW STATUS command. For instance, the value against the variable name Select_full_join will show the number of full joins that Mysql has executed to perform client queries. If you see a high value here it indicates a need for better indexing of the corresponding tables. Another example would be Max_used_connections – this can provide you with a benchmark to help decide the maximum number of connections your server should support, or help with traffic analysis.
More on Mysql global variables worth monitoring can be seen here:

techrepublic blog

Remember if you’re looking for a specific variable via SHOW STATUS, you can always use a where clause for example:

Code:

Well, as I said, it’s just a short entry this time. Hope it’s been of use.

Back soon

Thanks,
Mark

Original post blogged on b2evolution.