MySQL Query Performance Improvements

SoftTester uses a program written in VB.Net to perform all of the transactions. At the moment.t this runs during the night. In the last few months is undergone some big changes moving from MS Access offline with My SQL, to completely with MySQL.

Obviously when I did this conversion I had to do it in stages and leave tweaks till last after things settled down.

The next thing I need to do is query tweaks to improve the performance of the program and the MySQL process on the web server. Then I can do more with the program and hopefully convince my ISP that the program is fast enough to run during the day OR as he prefers in realtime.

Following a conversation with my ISP about the queries the program uses, I thought i'd share the I'd share the bigger picture.

Try to avoid text searches, use number / indexes fields.
When you have to use text searches try to include another clause to cut down that amount of records that the database has to look at to provide results.

This is a problem for me as I search on pad url as it is unique. I don't have any other data which is unique.

Next, instead of having big update queires which update fields which are already up-to-date, store the data in variables or some sort of structure and produce a smaller update query.

Then I have in my main table what I call a locking flag. This allows me to gain the key value after the record is added. Instead I can do a Max on key field plus one, then I have the record key id. Oh of course I use auto num.

Its a start but I'm hopeful these will make a big difference

by JM

Writing Help files for software products

For help, I've stuck with the CHM format. It does what I need and is easy to integrate with a .Net application. I must admit though I haven't spent time investigating alternatives and I've just gone with what I know about.

I used to find the process of developing a CHM a bit laborious. I'd use Microsoft FrontPage to generate HTML pages and then use the Microsoft Help Compiler to turn them into a CHM. There were always index files etc. to edit in Notepad. The process wasn't really diffcult, just a bit lengthy. I tend to put pictures into my help files too and maintaining help pictures with overlayed text and pointers in something like Paint .Net is a bit tedious.

Nowadays I use a package called Help & Manual (www.helpandmanual.com). This is a superb, well-rounded package that allows you to write WYSIWYG help in a Microsoft Word like way. Help & Manual also invokes the Help Compiler for you behind the scenes and there are no independent text files with weird formats to edit.

Help & Manual also includes its own image editor that allows you to easily write text and pointers/ arrows in different layers. When screenshots change, all you need do is update the background image and the text and pointers can be reused. This saves me hours and hours of time.

The other thing I like about Help & Manual is that you can generate output in different formats just by making a simple selection from a dropdown.

For example you can generate a PDF document of your help file. This can then be placed on your website for download or made available to users in printed form.

The other output format I use Help & Manual to generate is HTML help. I upload this to my website and it adds about 90 pages to my site - all full of good keywords relating to my applications. Google is also meant to prefer bigger sites so the extra pages might help me look bigger and more information heavy.

Within Help & Manual you can specify common headers for different output formats. For the HTML help I make sure each page links back to the homepage of my site. I also add links in the footer of each help page so that if the help pages are landing pages, a visitor can find their way to the main part of my site.

by ML

New name for IdeasPad

I wouldn't worry too much about coming up with a name that people search for - you can always write text and titles on your website that steer people to your product anyway.

I had a think of some names and looked for products with those naes on Google.  I came across some possible competing products and websites. I'm note sure if you come across these before but the sites look good and might give you some ideas:

• MyNotes - www dot mynotes dot com


• NoteKeeper - www dot mynoteskeeper dot com


• MyNotesCenter - www dot mynotescenter dot com


• MyJotter - www dot myjotter dot com

by ML

Counting Numbers to Keep People Interested

You mentioned counting downloads a few days ago. I think this is a very good way of making people come back. I'd consider showing a number of top lists - on different pages, e.g.

On the front page - top 20 downloads this day or week.

On category pages - top 10 or 20 in the category.

On programs - the number this week, number last week. Is it popular, isn't it popular ...

and so on...

Maybe it's me but I love keeping an eye on download stats.

by ML

Can More Searchworthy Categories be Added?

I'm still thinking about possible improvements to SoftTester - I'm finding it hard to switch context! One thing that strikes me is that the categories are all quite PAD file category oriented - which is fine and there's no need to change. You've also got keyword based pages - which is also fine.

But ... if you look at the kind of hits you get they are often around certain types of program. By researching keywords I can also see that people look a lot for specific kinds of software to download. These kinds of software don't line up with the PAD file categories.

Now we've introduced the idea of Related Programs based on the top 10 programs using similar keywords, how about adding new top categories? These categories should be linked from all pages as per the existing categories BUT the new category page should contain a list of programs in the new category, i.e. they shouldn't be nested pages.

The point about the new categories is to get page titles that match what a lot of people are looking for.

I'd be tempted to list these new categories independently on the front page to make them stand out, e.g. in a panel saying Popular Picks or some such. This panel could then be on every page in SoftTester.

From research, here's what I think an initial set of new categories could be:

free dvd software
free photo editing software
free antivirus software
free screensavers

but there might be more to add in the future.

The page titles would then be along the lines of:

download free dvd software
download free photo editing software
download free antivirus software
download free screensavers

... and the h1 and h2 would follow the lines of the existing category pages.

Remember that the new category pages should probably just list programs and not contain nested subcategories. You may need to limit the list to 50 programs in a category or break the list into multiple pages.

The page titles should make the new pages be in with a chance of getting a fair few hits - if you're lucky.

Thoughts?

by ML

Making Google's job easier with Unique Page Titles

On SoftTester you should make the top page titles unique. The home page has not got it's nice, new "Free Software Downloads ..." title but the other pages like Link To Us have also picked up the title. You need to change the title for Link To Us, Contact Us etc. to be relevant to the page. Same goes for the description meta tag, h1 and h2 tags too.

by ML

Follow me in nofollowing ...

Next thought on optimising Softtester is as follows ....

Are there any top level pages it simply isn't worth having as landing pages? At first glance, I can think of:

1. Link to us


2. Authors


3. Privacy Policy


4. Contact Us


5. Installation Help

They all suck up a bit of PR and don't hand it back to that many other pages, e.g. the category pages. Perhaps it's worth considering making links to these pages from everywhere else in Softtester rel="nofollow". Alternatively you could use the robots.txt to prevent the pages being indexed.

by ML

Making the most of "Free Software Downloads"

On Softtester, we've changed the title of the home page to begin with "Free Software Downloads" but to make the most of this new keyword we need to make links to the homepage with the link text set to "Free Software Downloads".

So what I suggest is that you put a link in the footer of each page to www.softtester.com with the link text set to "Free Software Downloads". This will reinforce the new homepage title.

An alternative would be to change the Home link already on each page to "Free Software Downloads Home" but this is a bit of a mouthful. What I did on SliQTools was put the usual Home link up the top where the user sees it and tuck the more SEO worthy link down in the footer.

by ML

SoftTester SERPS Improvements Starting Point

If you want to improve the performance of SoftTester in search results we need to do a couple of things first:-

1. Look at the competition


2. Check where we are today

Starting simply ... let's look at the big boys:

www.shareware.com

Homepage title: Search for shareware programs and free software downloads

www.download.com

Homepage title: Free software downloads and software review

and www.softtester.com ...

Homepage title:SoftTester: Download freeware and shareware software

Does this tell us anything? Well, I did a survey at work a few weeks ago to see if people knew what the terms shareware and freeware meant. Strangely, a fair percentage simply didn't. However, everyone understands the term ....

free software downloads

which of course also includes "free software". The big sites both include this term.

Is it worth changing the softtester homepage title to see if it makes any improvement? It can always be changed back at a later stage. At the moment the title is really saying to Googlebot that the most important keyword is "SoftTester" since this is at the start of the title. Perhaps the title could be:

Free Software Downloads | Free Trial Software | Shareware Programs | Freeware Programs

I checked the positions SoftTester is at on Google today using the following link/ tool:

http://www.seoserp.com/web_tools/google_top_1000_serps_checker.asp

This isn't a scientific test though since the positions will probably move around a lot. Anyway the positions for various keywords are:

US Google.com

shareware - 525
freeware - 775
free software downloads - not in top 1000

UK Google.co.uk

shareware - 288
freeware - 83
free software downloads - not in top 1000

Any thoughts?

by ML

Distributing fixes and upgrades

This post if a follow-up/ response to The Lost Benefits of Handling Serious Errors post.

Whenever a problem is corrected in a software product, I assess whether a new release is necessary.

If a release is necessary, how many people will benefit? When I'm talking about benefit here, I really mean "How many people will come across the error and be inconvenienced enough to report the problem to me?"

There's no easy way to make this assessment. Things I consider are:-

1. How long was the software package in circulation before the error was spotted?
2. How serious is the error?
3. How close is the next planned upgrade?

If a package has been in the field/ in use for quite a while, I am likely to only make a new release to the customer reporting the problem. Too many releases can be off-putting for people.

One thing I do is "push" releases. I email all registered customers to tell them a new release is available for download. I don't wait for them to check for updates.

by ML

Ideas for Security Website

The ideas for the site sound plausible. Give it a go and see how it looks.

Make sure you include enough text for the search engines to pick up and also use good alt text on the cartoon images.

by ML

New ideas for my MS Access Security Site.

OK, these are my latest ideas.

I'm thinking about having (as you suggest) maybe two columns on the page, on the left with a cartoon of a guy surrounded by books, with a manager stood waiting for him to finish. The guy looks stressed, maybe furious typing at the computer. The text would read you'll need to read X which will take you 30 minutes. You'll have to learn read the ms access manual.

Then on the left lots of white space, with a button to buy the program which a cartoon of happy workers and manager relaxing.

I guess I don't want to get too technical, that was a comment from my old site. But I guess I will have to say something otherwise I'll not be able to show how difficult it is and why my program is worth it.

by JM

Future of this blog - First Thoughts

I agree the blog is becoming a useful resource.

I think the idea of allowing other authors to advertise releases is also a good idea.

My initial preference would be to accept articles for posting in the New Software Releases category. We then review the posts before making the post ourselves.

By default I think the only links we allow in the posts should be to the product's page on softtester.

However, I'd also like it if in some way authors were encouraged to link to the blog in exchange for something, e.g. the possibility of a link from their release post back to their site as well as to a softtester page.

Making linking back into Softtester pages from release posts will help ranking/ SERPS of Softtester product pages and the authors themselves. Linking back to the blog helps the blog.

by ML

Ideas for Website Copy about Security

If you are short of ideas on approaches to writing a page that sells a product look at competitor sites. Search on Google for competitor sites using your chosen keywords and see how they approach the task of selling their products.

From a quick scan, there don't seem to be many competing products for sale but there are a lot of sites listing rather complicated manual processes. Perhaps one way to write part of the page would be to summarise the somewhat complicated manual process and then say - why bother, just use our software and make your life easier.

by ML

Ideas for an MS Access Security Selling page w/ keyword heavy domain?

Since I've discovered I've made a mistake with my first attempt at trying to create a selling page for my keyword heavy domain, I'm finding difficult to come up with a story / selling content for the page.

Obviously convincing people that they need to have security with a domain name which use security isn't going to convince people.  So what could I use instead...

An idea has been proposed that its better than spending time on those long MS Security documents, learning how to do it yourself.

Maybe the story doesn't have to have a single point, could be a combination of saved costs, saved man power and increased competitiveness. But how to put this into a story. Hmmm...

I do like the idea of having some cartoon pictures in there though.

Erm....

Makes it easy.

Hmmm difficult....

by JM

Response to Mistakes in SEO

The task of optimising a website to get good rankings in search results is not easy or quick. There is no single magic trick that will guarantee frequent visitors to a site and convert large percentages of visitors into customers. You shouldn't worry too much about making mistakes but instead think of refinements - probably on a continuous basis.

There are at least 4 factors in selling a product online:-

1. Having a product that has a market in the first place.


2. Determining what people search for (on Google, Yahoo etc.) when looking for such a product.


3. Making sure your website is a good match for the most frequent search terms - plus long tail terms.


4. Making your website convince customers to buy your product

None of the points are particularly easy to solve and you won't be able to "fix" any of them in a single hit. You shouldn't rely on one, main search term reinforced by a domain name.

Long-tail search terms are very important. For example, on my main website the search terms are invoicing software, invoice software and billing software. I've done research and found that these are the main search terms in the US and UK for my type of product. However, these search terms account for only 15% of my visitors. The rest of my visitors come from long tail terms that may only be mentioned once in the whole website. I don't always deliberately put long tails into the text, I just write text, e.g. in the Support page or the Release History page that simply gives a good variety of words and phrases.

Due to the way search engines work, the people who find your website will already be interested in your product, or at least in the problem your product helps with. My feeling is that your website should then describe the benefits your specific product offers, i.e. why using your product will make handling security easier, quicker and cheaper for users. Be specific, e.g. if there is a lengthy manual process for enabling security then describe how your product is a better alternative.

The other thing to remember is that Google Pagerank isn't everything. Pagerank doesn't guarantee a page gets visitors. Monitor your web stats and see if any changes produce a rise in the number of visitors. Make refinements and see what effect they have.

The other thing to consider is whether you should rely entirely on search engines to get customers. Are there alternative methods of advertising your software? Some sites directly sell software to programmers or have programmers as target customers, e.g. www.codeproject.com or www.componentsource.com. Could you use sites like these in some way to advertise your software?

by ML