Stupid tricks – hiding value of column in select *

One of the most common questions is “how do I get select * from table, but without one of the column".

Short answer is of course – name your columns, instead of using *. Or use a view.

But I decided to take a look at the problem.

Continue reading Stupid tricks – hiding value of column in select *

Getting unique elements

Let's assume you have some simple database with “articles" – each article can be in many “categories". And now you want to get list of all articles in given set of categories.

Standard approach:

SELECT
    a.*
FROM
    articles AS a
    JOIN articles_in_categories AS aic ON a.id = aic.article_id
WHERE
    aic.category_id IN (14,62,70,53,138)

Will return duplicated article data if given article is in more than one from listed categories. How to remove redundant rows?

Continue reading Getting unique elements

Tips n’ Tricks – using “wrong” index

More than once I've seen situation when there is a table, with serial primary key, and rows contain also some kind of creation timestamp, which is usually monotonic, or close to monotonic.

Example of such case are for example comments or posts in forums – each get it's ID, but they also have creation timestamp. And it usually is so that higher ids were added later than the lower ids.

So, let's assume you have such table, and somebody asks you to make a report on data from last month. How?

Continue reading Tips n' Tricks – using “wrong" index

How to remove backups?

Question from title sounds weird to you? It's just a ‘rm backup_filename'? Well. I really wish it was so simple in some cases.

One of the servers I'm looking into, there is interesting situation:

  • quite busy database server (2k tps is the low point of the day)
  • very beefy hardware
  • daily backups, each sized at about 100GB
  • backups stored on ext3 filesystem with default options
  • before launching daily backup, script removes oldest backup (we keep 3 days of backups on this machine)

Continue reading How to remove backups?