MySQL database housekeeping

As part of regular ongoing database housekeeping, you should repair and optimise your MySQL tables on a regular basis.

To repair one or more tables:

repair table table1, table2, table3, table4, table5;

To optimise one or more tables:

optimize table table1, table2, table3, table4, table5;

Depending on how dynamic your database is, you should probably aim to do both of the above at least once a month. And always repair your tables before your optimise them.

MySQL query cache to the rescue

Dynamic database driven websites are great. You just add your content and the website software grabs that content from the database, applies all the necessary formatting and displays it nicely and consistently to the user.

Until you wind up with thousands of “articles” and a very popular website, as one user complained in a recent forum topic. Googling for “+php +memory_limit” produces over 400,000 results, which shows just how widespread this problem really is.

Read more >>

Finding duplicate records with SQL

Although I don’t spend a lot of time doing any type of relational database administration, occasionally I get asked to find duplicate records in tables. Here’s the easiest way to do it, assuming your database supports subqueries:

select f1, f2, f3 from t1 where f1 in (
   select f1 from t1 group by f1 having count(f1) > 1
)
order by f1;

I’ve surmised that you’re trying to find duplicates on the “f1″ field. Substitute “t1″ with your actual table name and “f1″, “f2″ and “f3″ with your actual field names and include or omit as many field names you want to output in the first part of the statement.

SQL string formatting in VBScript

If you’re a software developer, string concatenation is something you do regularly, irrespective of the programming language you use. While languages such as C, C++, Perl and PHP all support the very useful sprintf() function, the VB/VBScript developers have traditionally had to resort to using a combination of single and double quotes, escape characters, text, line continuation characters and ampersands (&) to get the job done.

Read more >>

Lightweight ADO class

When putting together dynamic web applications in an ASP environment, developers use ADO recordsets for the overwhelming majority of their database interfacing. Although most web applications will involve data entry forms, where the user will insert, update and delete database records, the biggest performance issues are in presenting recordset output to the client browser.

Read more >>