Quickly Generate A Random Number Between 1 and 9

Quickly Generate A Random Number Between 1 and 9

By Strictly-Software

As you should know by now the RAND() function in SQL has the downside that it will return the same value if the same seed is provided to it.

E.G if I call this code multiple times I get the same number for each result:

SELECT RAND(1) AS [RandomNumber]

This always returns 0.713591993212924 for me.

Therefore when people use RAND() they often end up coming up with various ways to generate a unique seed value as well.

One of the quickest ways I have found to return a random number is by using the NEWID() function which generates a random GUID.

If I wanted to return a random record from a recordset I would use the NEWID() function in my ORDER BY statement. 

I use this a lot in my Jobboard code when I want to return a random banner from a banner group like so:

SELECT TOP 1 BannerPK, BannerURL, RedirectURL
FROM   BANNERS
ORDER BY NEWID()

However if I wanted to return a single number between 1 and 9 I could use the following code.

This basically converts the GUID to a VARBINARY value before converting it to a VARCHAR and then taking off the the first character with a LEFT statement.

I have found that this gives a larger proportion of 1's than any other number but it is useful if you want a quick way in SQL to generate a single number (or more if you extend the numbers returned with a string function) e.g LEFT(val,2) or use a MID or RIGHT. Then this code could do the trick.

DECLARE @RANDNO INT
SELECT @RANDOMNO = CAST(LEFT(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS VARCHAR),1) AS INT)

A WordPress Performance Killer - Automatic OPTIMIZE or REPAIR on big Databases

A WordPress Performance Killer - Automatic OPTIMIZE or REPAIR on big Databases

By Strictly-Software

I used to use the WordPress plugin WP-DBManager. I still do for the automatic backups (when they work), I have found on one site the scheduling just doesn't work so I have to do it by hand. No difference between that site and another that works but hey that's WordPress!

However one thing I found, especially once I reached 20k+ articles is that my scheduled OPTIMIZE / REPAIR jobs would totally kill my sites every Friday for an hour or so.

It took me a while to find the cause but when I used the Server Monitor in Navicat and saw that both my Databases were doing a REPAIR by SORT at the same time, I soon realised it was my scheduled job in WP-DBManager to REPAIR / OPTMIZE every Friday that was the culprit.

With MyISAM tables (the default storage engine for WordPress), an OPTIMIZE or REPAIR job will take the whole table out of action as the process is carried out. The table is locked as the sorting and repairing is done which means no other users or processes can access it.

Unlike MS SQL which lets you De-Frag a tables index whilst connections to it carry on MyISAM just locks up until the job is complete.

Symptoms

• Your website is hanging for ages, or timing out. If you are using Cloudflare you might be lucky enough to see cached pages either that a 503 service unavailable.
• Running a TOP command will show MySQL process consuming 90-100% of your CPU.
• Checking your Server Monitor in Navicat shows a SORT going on with a long execution time. The more pages and posts the longer it will take.

Risks

• If you have an scheduled job to REPAIR / OPTIMIZE your tables, either through a plugin like WP-DBManager then you could suffer this problem.
• The type and size of table matters. If it is using MyISAM it will be locked until the job is over. 
• The bigger the table e.g WP-Posts the longer it will take. 
• The more records the longer it will take. If the table is used a lot then it will be crushed until the job is over.

Solutions

• I turned off the automatic OPTIMIZE and REPAIR jobs on WP-DBManager.
• If I have scheduled downtime then I will make use of it whilst the werbserver is offline to carry out DB maintanance.
• If you are clever enough (and I don't know if MySQL supports SYNONYMS like MS SQL) but you could create copies of your existing tables with new names, let your system carry on using the old tables whilst your OPTIMIZE / REPAIR the new ones. Once finished toggle the SYNONYM back to the newly formatted tables. A SYNONYM would be perfect for this but you could write SQL to create a table, insert records, repair and then re-name. Maybe a plugin could do this (ideas for developers? I would be willing to write one for ££)
• I know use my own Strictly System Check plugin instead to handle automatic REPAIRS / OPTIMIZE as it will only carry them out if the system is already suffering. My plugin checks the server load, the time it took to load a page, the number of connections and so on. If the plugin determines that the system is having trouble you can set it to OPTIMIZE or REPAIR (after a check to see if one is needed). This way you are only fixing something that needs it rather than causing havoc for no need.

It is something to be on the lookout for. If your site suddenly acts slow with high MySQL CPU use then a scheduled REPAIR might be the cause.

If this happens it is best to turn off APACHE, let the REPAIR do it's job and then turn the webserver back on.

Introducing a new version of Strictly AutoTags 2.9.1

Introducing a new version of Strictly AutoTags 2.9.1

As you should know by now I don't really like open source development.

When you have put literally hundreds of hours into a plugin such as I have with Strictly AutoTags it is a bit dismaying when you visit the Wordpress site and see that you have 180,000+ downloads (not millions but more than a few thousand), pretty good ratings, good reviews on SEO sites and other places Google returns from a search on the plugins name. Yet hardly anyone has bothered donating me any money. Just imagine if everyone of them had donated me a single £1 for my hard work?

Believe it or not it takes a lot of my spare time to keep a plugin like this maintained and working.

Having to keep it up to date with changes Wordpress make to their core code as well as adding new features that often involve complex regular expressions and logic to get right.

Is £1 a lot to ask from someone who finds a plugin such as Strictly AutoTags useful?

I mean I find it very useful on my own sites.

Not only does it automatically add relevant tags to news stories but it does so without having to rely on 3rd party API's that contain lookup databases of possible tags.

If a news story about me came out tomorrow and I was mentioned a few times in the article the plugin would pick my name up and add it to the sites list of tags without having to wait for someone else to decide I am important enough to be worthy of such a tag.

I have a couple of systems that automatically import news feeds into the site and then tweet out the contents to various Twitter accounts using another one of my plugins Strictly TweetBot.

It analyses the content of the article and can change the format of the tweet or the account it sends it to depending on what it finds. Having Strictly AutoTags sit in the middle of this process not only ensures I don't have to manually edit each article to find relevant tags but it also provides them to Strictly TweetBot be used as #HashTags when I tweet about the news story.

Also being able to set the plugin up to discover certain words and then tag the article with a totally different word is very useful for news based sites.

If I have a news story about Edward Snowden or the NSA Prism program I want to tag the article with the word "Privacy" or "Whistle-blower". The paid for version of the plugin allows such logic.

So because of the lack of enthusiastic donations I have split my code in two and kept a paid for only version of my plugin for those people who actually find tools like this useful.

I don't really like the idea of having to keep two lots of code up to date but if a £1 donation is too much from everyone of the 150,000 people who have used it then I have to sell the better version to get some sort of recompense. Otherwise I might as well stop putting the code online and just use it on my own sites.



Latest Features of 2.9.1 Include:

• The ability to remove basic formatting from the post. Ideal for auto-bloggers who import from feeds containing , , , tags that could cause formatting issues on your site.
• Better regular expressions, more checks and the ability to bulk re-tag and link with all key attributes (alt,title,src,href,data-blah) including shortcodes [youtube url] saved before re-formatting to keep the original HTML in tact.
• New functions to find tricky words like al-Nusra Front, World War II or 2,000 Guineas.
• Bulk update to remove all HTML the plugin adds.
• Option to turn the plugin into Clean Edit Mode so you can edit individual articles to remove any HTML you don't like.
• New functions to turn text like http://www.strictly-software.com and www.strictly-software.com into clickable links e.g strictly-software.com.
• Set a minimum length of characters for a tag to be used.
• Set the maximum number of words allowed in a tag.
• Set equivalent words for tags e.g search for Edward Snowden, NSA or PRISM and use the tag Internet Privacy.
• Bold and deeplink common used tags to create great SEO for Googlebot and BING to crawl.
• Set the minimum number of tags that must be stored against articles before they can be used in any deep linking.
• Set the maximum number of tags in an article to deeplink.
• Set the maximum number of tags to place against an article.
• Create a list of case sensitive and case insensitive "noise" words to ignore and never use as tags.
• Use the new finished_doing_tagging hook which is available for other plugin authors to use so that actions only occur after tagging is done on a post. For example my other plugin Strictly Tweetbot uses this hook so that tags are available to be used as #hashtags in tweets.
• All content already inside special HTML tags title, src, href, alt and new data-blah or data-blah-blah attributes and Wordpress shortcodes e.g [youtube url] are stored in memory to prevent bolding or deeplinking within other tags or attributes.

You can buy the latest edition of the plugin OR download the latest free version from my own webpage at www.strictly-software.com