Situation:
I have a silly database table (not mine) storing CMIS Facility week numbers and their starting dates. For those of you unfamiliar with this system, the reason week numbers are different to normal people's week numbers is because these are academic week numbers. So I can't use the built-in functions.

The current structure looks similar to this:

ID        SetID            WeekNumber             StartDate
--------- ---------------- ---------------------- ----------------------------
1         2011/2012        1                      2011-07-18 00:00:00
...
52        2011/2012        52                     2012-07-09 00:00:00

Joe you're an idiot!
You might say to me why not run the CMIS Facility application and add a new set, it will put these dates in automatically.

Herein lies the problem
The reason I'm doing this is for another system which decided to "cleverly" use the exports from CMIS Facility so that all the weeks correspond to the rest of the academic data. Unfortunately the developer wrote a system he felt would last the rest of his PhD degree, it's a shame he started in his last year. He used functions to mktime and simulate the dates. A function goes in with a normal calendar date and returns an academic week number and the week commencing date.

Problem?
The 1st of January 2010 was a Friday. The 1st of January 2011 was a Saturday. The developer felt that as long as you adjust the script each year you could make the system last another year. Shame he also forgot the academic year ends halfway in a normal people's calendar, so you actually have to adjust this twice a year.

The Why
So I find myself writing increasingly complex SQL scripts and it's at the stage where we need to optimize the queries because some scripts are noticeably slow (as observed by the customer...) and then others not.

The What
I'm going to run these benchmark tests against a system that is both up and running via the front-end and back-end. It's MediaWiki CMS used by Wikipedia.org and the like. I like queries against this database because it involves linking a lot of tables and outputting... just articles and their titles.

I have another table holding the audit trail of content approvers on the system. Approvers can e-sign an article (approving it) by clicking on a button. We want to bring back the articles that aren't listed in the audit table (articles yet to be approved).

• Windows XP (ODBC Data Source Administrator)
• MS Business Intelligence Development Studio 2008
• Test/Sample MySQL Database to connect to (server and username + password).
• Admin Access to the reporting server if you plan on deploying the report to it.

• We want to report on a MySQL database
• MySQL login information is a user who exists as a MySQL user and a server user.
• Reporting server is remote as well as the MySQL database.
• BIDS is on the client machine.

Basically, I've started using so much more SQL in our new Business Intelligence projects that I've been revising all my scripts to see what SQL I can optimize.

One of my systems is a MediaWiki CMS which is used for the official Bournemouth University Knowledge Base. The skin itself is the index page loaded for any page in the Wiki system. It logs the IP address (VisitorIP), the URL (VisitorURL) entered via the browser useragent (VisitorUAgent), the User ID (VisitorID, 0 if not logged in) and of course the Timestamp (DateTimeStamp).

Ok is it just me who does everything slowly and badly until someone comes along and says why are you doing it like that?  My justification is that the job has to be done no matter what.

If you've ever sat there with phpMyAdmin or a MySQL Administration Tool (like Navicat or SQLYog), and still there at night editing each field so that it displays correctly... well that's usually me.

Found it a bit boring but here's a short bit of code to speed it up which I now use:

update table_name set field_name=REPLACE(field_name,'string_to_find','string_to_replace');

Source: http://www.mediacollege.com/computer/database/mysql/find-replace.html

Suppose you have a column in your table that you use as a counter (storing the value of the counter - eg. times an article has been displayed).

Basically what I used to do is something similar to the following:

1. SELECT counter_field_value FROM table1 WHERE column1='this_article'
2. Add 1 to counter_field_value
3. UPDATE table1 SET counter_field_value=<new_counter_field_value> WHERE column1='this_article'

Combined with a PHP script this could be a few lines for something really small.

The quick trick to this is to do it all in one query:

UPDATE table1 SET counter_field_value=counter_field_value+1 WHERE column1='this_article'

A data type reference table. If you're designing a database then you don't need me to tell you what this is.

My personal opinion is to always try to use the minimal type and length of the value required. For example, a comment of 500 words should only be TEXT (~64Kb) rather than LONGTEXT (~4Gb).

 I've been looking into this for a friend and going through forums to investigate this error.  After you've checked your database connection details, I find what FisherC said below is the most probable cause for the error.

FisherC says:

I have the same problem, but I'm not sure for the same reasons.  I installed the standard 1.5 installation package. I have uninstalled and reinstalled once just to make sure everything was OK. Basically after I use my website (using the default Joomla welcome template with NO modifications whatsoever) I start to get the "cannot connect to MYSQL." I contacted my webhost, and the problem is the number of MYSQL database queries. It goes up to about 200,000 in the first few minutes of use and then after that every page load is a few hundred to few thousand queries (per page). The webserver limits me to 50,000 queries an hour and I am blowing past that quickly with just 1 person (me!) on the site so they just refuse the connnection after a while.  Obviously something is not right. I am searching the forums for an answer and if I don't find one I'll repost. I just thought I'd share this with you in case this your problem too.

Source: Joomla Forum: http://forum.joomla.org/viewtopic.php?f=433&t=198257

My Solution

I have over 20 joomla websites on my virtual server and I haven't had this problem.  

1. I setup a website with about 20 3rd-party components installed, 15 modules and about 15 plugins (excluding the core modules/plugins) and managed to recreate this error.  
2. I disabled half of these but still got the error.  
3. Cleared the expired cache but still got the error.
4. I then uninstalled half of these (including Kunena and Agora forums), specifically database intensive plugins and the error happened less...
5. I then went through the menus and installation screen to get rid of anything that was not visibly used on the website.  The website was working fine and I was able resume my click-happy habits.

It's a given that in my test, there was only 1 user (me).  There are reports though that some people set up websites with NO 3rd-party items whatsoever... For those of you with this issue, I'd double-check your website host allows Joomla and if they don't, look for either an upgrade or a different host.

If your website has been LIVE for a while and it's returning this error despite having little installed, then check your logs to see if anyone has been trying to hack your website.  There are a number of components I have found that even with captcha features, still get hacked with viagra emails and what have you (eg. JoomlaBook).  If you don't want to go through the logs, try setting up a test joomla site with a different database and user and see if that behaves the same way.

Virtual Hosts

I used to have an account with 1&1 (oneandone.co.uk) and I would strongly urge Joomla developers to avoid them like the plague.  They have been less than helpful and when I only had one Joomla site with them, they still would block me thinking I was trying to hack into their servers because of the number of queries Joomla does and the number of times I refresh the page and upload files.  Why they think that developers should not be able to send a 1000 queries a minute or why a developer has to refresh the page, make a change, upload, etc.  well it's beyond a joke and no one should waste their time with such companies.