Category: MySQL

Database modeling and implementation in MySQL

Generating graphs from MySQL table data

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 TIMESTAMP, DATETIME and 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

Database optimization techniques you can actually use

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

Fetching specific rows from a group with MySQL

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):

SELECT product_images.src,products.name 
FROM product_images 
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 in MySQL / PHP

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

Selecting closest values in MySQL

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