John Cox

Blogs · @wyome

Tagged database · everything

Web Development Blog

Optimize Table, Speed up your MySQL Database | Web Development Blog

Database table overhead from transactions slows down web applications like WordPress, and this tutorial provides a PHP script using MySQLi to automatically optimize all tables across all databases by identifying and defragmenting tables with unused space. The script can be scheduled to run daily via cron job and configured to email reports of optimized tables and any errors, with the credentials stored securely above the public directory. The approach works for both VPS/dedicated servers and shared hosting environments, though shared hosting users may need their provider to create a database user with appropriate permissions.

Permalink performancedatabasetutorial

dev.mysql.com

MySQL :: NoSQL to MySQL with Memcached

This is a legal disclaimer from Oracle stating that any product development plans or future release information on their site are non-binding statements subject to change at Oracle's sole discretion. The company explicitly disclaims liability for this information and forbids its incorporation into contracts, making clear that such statements should not influence purchasing decisions.

Permalink databasetutorialnosql

Gist

Some slight modifications to the codeigniter profiler to dynamically display session vars, and to add EXPLAIN to all queries. — Gist

This is a modified CodeIgniter profiler class that adds two key debugging features: it displays session variables in a dedicated table during profiling output, and it automatically runs MySQL EXPLAIN queries on all SELECT statements to show query execution plans with details like table access types, key usage, and row estimates.

Permalink developmenttoolsdatabasetutorial

MySQL Diary

Optimizing the MySQL IN() Comparison Operations Which Include the Indexed Field – MySQL Diary

When a MySQL IN() clause references a field that's part of the query's index, the database evaluates the full result set for each IN value before merging and limiting results, which is expensive on large tables. Replacing the IN clause with a UNION ALL of separate queries—each with its own LIMIT—forces MySQL to limit results at each step before merging, dramatically improving performance. The post demonstrates this with a concrete example where `WHERE b=4 AND c IN (1,3,5,7,9)` becomes five separate queries joined with UNION ALL, each limited to 100 rows before the final merge and sort.

Permalink performancedatabasetutorial

news.ycombinator.com

(500) http://news.ycombinator.com/item?id=709713

This conversation discusses the trade-offs between nested set models and materialized path approaches for storing hierarchical data in relational databases. Commenters argue that materialized paths—storing the full path to each node as a denormalized column—offer simpler implementation and cheaper insert/delete operations than nested sets, though they require updating all descendant paths when moving subtrees and may have performance limitations with very large datasets. The discussion also touches on alternative approaches like PostgreSQL's native ltree type and adjacency lists, with general consensus that different tree storage methods suit different use cases and that no single approach solves all hierarchical data problems optimally.

Permalink programmingdatabasetutorialsql

dev.mysql.com

www.geonames.org

27.org

Untitled (http://27.org/isocountrylist/)

This page describes a reusable SQL database table of ISO 3166 country codes and names that the author created to avoid repeatedly building the same reference data from scratch for international user registration forms. The author provides the downloadable SQL file, explains how to import it into MySQL, and demonstrates with PHP code how to use it to generate drop-down select lists while maintaining data integrity through foreign keys. The page also documents updates over several years, including additions of 3-letter codes, properly capitalized country names for display, and a similar table for U.S. states and postal abbreviations.

Permalink databasetutorial

Percona

Using Flexviews – part one, introduction to materialized views - MySQL Performance Blog

Flexviews is a tool that enables incrementally refreshable materialized views in MySQL—pre-computed, stored query results that update only the changed rows rather than recalculating from scratch. Unlike regular views (which recompute on every access) or complete materialized view refreshes (which rebuild entirely), incremental refresh examines only rows that changed since the last refresh, making it orders of magnitude faster for large datasets while maintaining transactional consistency across multiple views. The tool works through three mechanisms: capturing what changed in the database, computing the differences those changes produce, and applying those differences to update the stored results.

Permalink performancedatabasetutorialsql

Smashing Magazine

Speeding Up Your Website’s Database - Smashing Magazine

While website speed improvements typically focus on file sizes and server settings, database query optimization is equally critical—a homepage might load in 1.5 seconds while slow database queries bog down search or product pages. The article provides practical techniques for identifying and fixing database bottlenecks, starting with simple methods like adding indexes to frequently queried columns (which can speed up queries 100+ times) and progressing to more complex solutions like restructuring queries with JOINs, caching results, and optimizing table storage. These approaches help developers diagnose whether sluggish page performance stems from poor database design or slow queries rather than network or server issues.

Permalink performancedatabasetutorial

www.sitepoint.com

What’s The Best Date Format? » SitePoint

ISO 8601 format in UTC (2011-03-21T00:18:56Z) is the optimal standard for storing and comparing dates programmatically because it's sortable, unambiguous, compatible with JavaScript's Date constructor, and works across systems without timezone complications. Rather than storing multiple timezone versions, all timestamps should be recorded in UTC on the server and converted to local time only when displaying to users, maintaining a single reference point for comparisons while preserving information needed for internationalization. The article provides practical implementation examples in PHP using `gmdate('Y-m-dTH:i:sZ')` and JavaScript methods for generating and formatting timestamps consistently across client-server applications.

Permalink programmingdatabasetutorial

Tuts+ Marketplace

www.skyontech.com

Tumblr

seldo.com

In defence of SQL | Seldo.Com Blog

This piece argues that while SQL is awkwardly designed for tasks like aggregation and filtering (which weren't part of its original relational algebra purpose), it remains invaluable because it enables ad-hoc queries on data without requiring advance knowledge of all questions you'll ask. The author contends that the real insight of the NoSQL movement wasn't rejecting SQL itself, but recognizing that specialized data stores (key-value, document stores, etc.) should match specific use cases—yet recommends keeping relational databases alongside these systems for their flexibility, and predicts a common query language will eventually emerge to bridge the fragmented NoSQL ecosystem as SQL once unified relational databases.

Permalink programmingperformancedatabasesql

Percona

Caching could be the last thing you want to do - MySQL Performance Blog

A popular PHP ecommerce platform required caching to achieve basic performance (10 pages/second), exemplifying a common architectural mistake where developers reflexively add caching to mask underlying inefficiencies rather than fixing them first. The author argues caching should be a last resort because it creates unequal user experiences (some users hit cache misses repeatedly), obscures real performance bottlenecks, and introduces complex management problems like cache stampeding. Before implementing caching, developers should exhaust simpler optimizations: understanding query execution plans, eliminating SELECT * queries that fetch unnecessary columns, and verifying that every query on a page is actually necessary.

Permalink performanceadvicedatabase

net.tutsplus.com

thehobt.blogspot.com

Percona

Intro to OLAP - MySQL Performance Blog

This post introduces business intelligence (BI) and OLAP (online analytical processing) tools, explaining how they analyze data to provide insights into business performance through multidimensional analysis that breaks down measures like sales totals across dimensions such as time, geography, and product. It contrasts MOLAP systems, which use specialized pre-computed data stores for speed but require lengthy processing times, with ROLAP systems like Mondrian that query relational databases structured as star schemas, trading some performance for flexibility and ease of use. The post establishes that this series will focus on open-source ROLAP tools using MySQL, covering how they generate queries and handle aggregation to improve performance on large datasets.

Permalink performancebusinessdatabasetutorial