Graphs and charts are a useful visual way to view historical data – they make it easier to detect trends and get a big-picture view of data. All we need is timestamped data – table rows that are stamped with a specific date/time format, that can be used to group rows into time periods.
Time stamped data
In order to aggregate table data by time periods / ranges, we need a date/time column in the table we want to analyze. Appropriate types for such a column include
DATE, but we can also use string / numeric types for grouping data together if they contain some sort of date/time information – though those will be much less flexible than native date/time types.
I usually opt to go with the
TIMESTAMP format, for a couple of reasons – Continue reading Generating graphs from MySQL table data
I just saw an article on Smashing Magazine titled “Speeding up your website’s database“. I love Smashing’s contribution to the webdev community, but their articles are getting longer and more basic at the same time.
I understand the need for simplicity because of the wide audience of Smashing Magazine, but I’d wish they’d give something more than the absolute basics you could find in almost any other site out there. I also didn’t like some of the methods mentioned there for profiling (or the code itself), so I here is my starter guide to optimizing database performance.
Continue reading Database optimization techniques you can actually use
In a normalized database, we store separate entities in separate tables and define relationships between them, those being – one-to-one, one-to-many and many-to-many. We often want to fetch data which is split over two or more related tables, which is dead simple when fetching the extra data from the ‘one’ side of a relationship (ie, from a “parent” table):
INNER JOIN products ON product_images.parent_id=products.id
Literally: Get the name of the parent product for each row in the products_images table.
Going the other direction is not as trivial. If we just want all the referenced rows in the child table, the query seen before would do the trick. But if we wanted a specific row to be found – for example, the latest inserted image, it becomes somewhat more complicated.
Continue reading Fetching specific rows from a group with MySQL
Multiple row operations are in common use in a normalized application databases as one database entity is often linked to multiple sub-entities (for example a user and his tags). By row operations I’m referring to write queries, namely UPDATE and INSERT queries (DELETE is less interesting so I’ll leave it out for now).
Too often I’ve seen such queries ran in long loops one at a time, which is very bad for performance (as I will show here) and sometimes equally bad for integrity (if the process is interrupted). So what are the alternatives?
Continue reading Multiple row operations in MySQL / PHP
Sometimes the need arises to select several values in the vicinity of a certain value, preferably ordered by proximity. The values might be dates, zip-codes or any other meaningfully ordered values that can be represented as numerical values. How can we pull this off in MySQL?
Continue reading Selecting closest values in MySQL