John Cox

Blogs · @wyome

Tagged sql · everything

www.kdnuggets.com

Top SQL Queries for Data Scientists

This article surveys nine essential SQL concepts for data scientists—querying and filtering, handling NULLs, type conversion, aggregation, date manipulation, text processing, ranking, window functions, and subqueries/CTEs—with practical examples of each. It then demonstrates how these concepts combine in real business scenarios, specifically finding best-selling products by month and calculating moving averages, to show that SQL is indispensable for data extraction, manipulation, and reporting even though it's often overshadowed by Python in discussions about data science. The piece emphasizes that SQL proficiency alongside Python and R forms the core toolkit for practical data science work.

Permalink programmingtutorialsql

PopSQL

PopSQL - Modern, collaborative SQL editor for your team

PopSQL is a collaborative SQL development platform that centralizes data queries, documentation, and visualization in a single workspace to reduce inefficiencies like scattered knowledge and repetitive Slack questions. It emphasizes real-time team collaboration, interactive visual dashboards, easy query sharing, and integration with existing data infrastructure while prioritizing security and ease of use for both technical and non-technical users. The tool is designed to help data teams work more efficiently by providing a shared environment that democratizes data access and frees up analysts to focus on strategic work rather than answering basic data questions.

Permalink productivitycollaborationtoolsdatabase

www.foxhound.systems

Speeding up SQL queries by orders of magnitude using UNION – Foxhound Systems

When SQL queries join multiple table branches with OR conditions—a "diamond-shaped" schema—query optimizers often struggle to create efficient plans, causing dramatic performance degradation. Using UNION ALL to split such queries into separate simple queries, each optimized independently, then combining the results can improve performance by orders of magnitude; the author demonstrates this with a real example where a 3,264ms query becomes 112ms when rewritten this way.

Permalink performanceindexingdatabasesql

tech.pro

explain.depesz.com

New explain | explain.depesz.com

This tool transforms PostgreSQL's EXPLAIN ANALYZE output into a more human-readable format, making it easier to identify performance bottlenecks and understand query execution plans at a glance. Users can paste their raw query plans along with optional query context and comments, then share them or keep them private, with an option to obfuscate sensitive information before saving.

Permalink performancetoolsdatabasesql

www.xarg.org

Statistical functions in MySQL • Code is poetry

Computing descriptive statistics in MySQL is more efficient than moving data to an application layer, since the database can filter and aggregate millions of rows before returning small results, and MySQL 8.4 provides native aggregates and window functions sufficient for most analyses without server extensions. The piece walks through concrete SQL patterns for calculating means (arithmetic, weighted, harmonic, geometric), medians, percentiles, distributions, variance, skewness, correlation, and rolling statistics, emphasizing that each statistic requires careful definition—distinguishing population from sample forms, choosing the right average type for the domain, and filtering data consistently before aggregating to avoid bias from joins or missing values. A final checklist covers correctness and performance: filtering early, preventing join multiplication, auditing nulls, using deterministic ordering, naming the estimator explicitly, and recognizing when to stop at the database boundary and hand compact results to a dedicated statistics tool.

Permalink databasetutorialsql

SQL Injection Knowledge Base

James Cohen

What makes a good MySQL index? Part 2: Cardinality « James Cohen

This article explains how cardinality—the uniqueness of values in an indexed column—affects MySQL query performance, using a sample user table to demonstrate why indexes with higher cardinality are generally more selective and efficient. It shows that MySQL's query optimizer strategically chooses whether to use an index based on whether it will actually reduce the number of rows to scan, sometimes opting for a full table scan when an index would be inefficient. The article also notes that low cardinality isn't inherently problematic if the data distribution is skewed enough that queries still filter down to a small subset of rows.

Permalink performancedatabasetutorialsql

www.devshed.com

Turning the Tables - MySQL

This tutorial demonstrates how to enable and create InnoDB tables in MySQL, which are necessary for transaction support since the default MyISAM format doesn't support transactions. It shows how to verify InnoDB support using the "have_innodb" variable, create new tables with the TYPE=InnoDB clause, and convert existing MyISAM tables to InnoDB format using ALTER TABLE commands. The piece provides practical SQL examples for setting up a sample database with users, groups, and mailboxes tables as a foundation for learning transaction implementation.

Permalink developmentdatabasetutorialsql

use-the-index-luke.com

SQL Indexing Tutorial | Use The Index, Luke!

This is a comprehensive free web guide to SQL indexing fundamentals across major database systems (MySQL, Oracle, PostgreSQL, SQL Server, DB2), teaching developers the practical mechanics of how indexes work—from B-tree structures to multi-column indexes to join operations—rather than abstract administration concepts. The guide covers both what makes indexes effective (exact lookups, range searches, covering indexes) and common mistakes that slow them down (functions in WHERE clauses, type mismatches, over-indexing), with vendor-specific testing notes provided throughout.

Permalink performancedevelopmentdatabasetutorial

evanmiller.org

How Not To Sort By Average Rating

Most websites sort user-rated content using flawed methods that either ignore the proportion of positive ratings or fail to account for statistical uncertainty in small samples. The Wilson score confidence interval, derived from 1927 statistical theory, provides the correct approach by calculating the lower bound of a 95% confidence interval for the true positive rating proportion, effectively balancing high positive percentages with sufficient sample size so that items with many ratings don't automatically rank above items with few ratings but higher quality scores. The piece provides the mathematical formula, code implementations in multiple languages, and practical SQL examples to sort content correctly.

Permalink tutorialsql

Percona

Optimize Your SQL With Percona’s Online Query Advisor! - MySQL Performance Blog

The piece announces a new online SQL query analyzer tool that identifies bugs and problems in MySQL queries, similar to how lint tools work for programming languages. Users can paste queries into the tool to receive feedback on potential issues, and they can share analyzed queries publicly with others. The tool integrates with existing Percona accounts and complements their other offerings like the MySQL Configuration Wizard.

Permalink performancetoolssql

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

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 Coding

While Google's inclusion of page speed in search rankings has focused attention on file sizes and server settings, the actual assembly of pages on the server—particularly database queries in PHP or ASP—often receives overlooked scrutiny. This article provides practical methods to identify and eliminate database bottlenecks, from simple techniques like indexing frequently queried columns and using JOINs to combine multiple queries, to more advanced approaches like application-level caching and query optimization. The piece includes diagnostic tools, code examples for timing database performance, and explains when common optimization strategies like table optimization and MySQL caching actually help versus when they're ineffective.

Permalink performancesql

Tuts+ Marketplace

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

net.tutsplus.com

thehobt.blogspot.com