Common Queries Tree Common MySQL Queries Basic aggregation Last updated 05 Jan 2013 Aggregate across columns Last updated 09 Sep 2009 Aggregates across multiple joins Given a parent table and two child tables, a query which sums values in both child tables, grouping on a parent table column, returns sums that are exactly twice as large as they should be. CREATE TABLE packageCredit ( packageCreditID INT, packageCreditItemID INT, Last updated 22 Feb 2013 Aggregates excluding leaders You have a table of grouped ranks ... Last updated 21 May 2009 Aggregates of specified size Find the values of a table column c1 for which there are a specified number of listed values in another column c2. All X for which all Y are Z You have an election database with tables for candidates, parties and districts. CREATE TABLE districts ( district char(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO districts VALUES ('Essex'),('Malton'),('Riverdale'),('Guelph'),('Halton'); Avoiding repeat aggregation Cascading aggregates
Softwares/Libraries for Full-text Search A lot of applications have a requirement to search the full-text of some content they have for some words it might contain. This kind of functionality is often referred to as full-text search. For example, a blogging software might need to provide a search functionality that searches the blog posts for the user entered query terms. It is not possible to use the regular database indexes (usually B-Trees or Hashmaps) for this purpose because they require that you provide the full value of the column you are searching in; in essence they do an equality search. In the blogging software example, the user would then have to type in the entire blog post verbatim in order to find it; even if you could imagine the most patient of users, if s/he already knows the entire post by-heart, why would s/he be looking for it anyway?! SQL does offer the LIKE operator which allows a query to look for words occuring anywhere within the field. SELECT * FROM posts WHERE body LIKE '%database%' Pros: Cons: Solr
» How to check if an email address exists without sending an email? We have all been doing email address validation for a very long time to make sure that the email is correctly formatted. This is to avoid users entering wrongly formatted email address but still they can accidentally give us a wrong email address. Example of a correctly formatted email address but still wrong: mailbox.does.not.exist@reddit.com [VALID format but does not exist] Above case specifically happens when you take important customer email on phone and you type in the wrong email. The solution A quick & simple check below can be implemented in most programming language including PHP, Python etc. To check if user entered email mailbox.does.not.exist@reddit.com really exists go through the following in command prompt. First - Find mail exchanger of reddit.com COMMAND: nslookup – q=mx reddit.com RESPONSE: reddit.com MX preference = 10, mail exchanger = mail.reddit.com mail.reddit.com internet address = 208.96.53.70 Second - Connect to mail server mail.reddit.com 2) Usage example - DOWNLOAD
MySQL Cookbook, Second Edition Welcome to the Web site for MySQL Cookbook, Second Edition. The book covers techniques for solving many different SQL problems, and how to write your own MySQL programs using Perl, Ruby, PHP, Python, and Java. It is written in problem-and-solution format to make it easy for you to find the answers to your questions. It contains SQL-only recipes, command line scripts, and web scripts (using Apache for Perl, Ruby, PHP, and Python; and Tomcat for Java and JSP/JSTL). The second edition brings the recipes up to date for MySQL 5.0/5.1. Read this page in Romanian translation (by Web Geek Science). Inquiries may be sent to mysql-cookbook@kitebird.com. Hibernate - JBoss Community Creating Database-driven Websites using PHP and MySQL Introduction What is this tutorial about? Any Pre-req's? Introduction If you would like to collect data via the web, simplify the maintenance of your website, or perhaps enrich the user experience of your site, you might need more than just a web site consisting of static pages. Using a database in conjunction with your web server, you can store similarly structured information (such as customer data, survey responses or contact information) in one place and automatically produce numerous web pages on-the-fly such as an employee directory or a web-based service request application. You can easily reuse certain data in different places (e.g. have a listing of all employees along with separate home pages for each employee) while keeping the maintenance effort low. What is this tutorial about? This tutorial explains the basic concepts behind database-driven websites, often also referred to as web applications. Any Pre-req's? A birds-eye view on web applications The Web Browser's Job
SQL Syntax The SQL query strings for Windows Installer are restricted to the following formats. SQL Grammar The optional parameters are shown enclosed in brackets [ ]. When several choices are listed, the optional parameters are separated by a vertical bar. A {constant} is either a string or an integer. The LOCALIZABLE option sets a column attribute that indicates the column needs to be localized. A {column} is a columnar reference to a value in a field of a table. A WHERE {operation-list} clause is optional and is a grouping of operations to be used to filter the selection. {column} = {column}{column} = | <> | > | < | >= | <= {constant}{column} = | <> | > | < | >= | <= {marker}{column} is null{column} is not null For string values, only the = or <> operations are possible. Individual operations can be grouped by AND or OR operators. The ORDER BY clause is optional and causes an initial delay during sorting. The maximum number of expressions in a WHERE clause of a SQL query is limited to 32.
Clustrix Builds the Webscale Holy Grail: A Database That Scales This is big stuff. Indeed, Paul Mikesell — CEO of Clustrix and the former co-founder of storage system success story Isilon — said the goal is to use its appliance to solve a growing problem for companies managing large amounts of data, such as big travel, e-commerce and social websites. As the web grows more social, companies are trying to keep track of more pieces of data about users and their relationships to other users. This creates complicated and large databases that can slow down access to user information, and thus the end user experience. We’ve written about myriad attempts to solve these data scalability problems, attempts that have spawned appliance startups and whole branches of code designed to help sites scale their data, from Hadoop to Cassandra to Twitter’s Gizzard. Mikesell said the product could replace the need for caching appliances such as those offered by Schooner or Northscale, but could also work in conjunction with them.
Mamboserver.com - Home MySQL 5.5.4 is Very Exciting « Jeremy Zawodny's blog MySQL 5.5.4 is Very Exciting Yesterday at the MySQL Conference, I spent time in a few sessions discussing the performance enhancements in the MySQL 5.5.3 and 5.5.4 milestone releases. What I saw made me very, very happy. It seems that in the MySQL 5.5.4 release, several performance bottlenecks that really affected scalability beyond 4 cores have been either eliminated or seriously mitigated. Multiple Rollback Segments mean the 1,024 concurrent transaction limit goes away and concurrent transactions will have less mutex (lock) contention.Removing MySQL’s LOCK_open mutex (lock) and replacing it with a Metadata Locking (MDL) Framework eliminates a very large bottleneck when going beyond 4 CPU cores in even read-only benchmarks.InnoDB Recovery is WAY FASTER in this release. There is a full list of changes to MySQL 5.5 available on-line. The benchmarks presented that compared MySQL 5.5.4 with 5.1 show substantial improvements in a variety of workloads. Like this: Like Loading...
Directed Edge - Blog - On Building a Stupidly Fast Graph Database It’s pretty clear to computer science geeks that Directed Edge is supposed to be doing groovy things with graphs. In fact our recommendation engine, and some of the things that are unique about our approach to recommendations, are built on our super-fast graph database. When we went live yesterday with the latest version of our recommendations web services, another, much bigger thing happened behind the scenes for us: we cut over to the new version of our graph database. Every time that Directed Edge gets mentioned in nerdier circles we get a lot of questions about this fabled graph-engine, so we thought we’d indulge our techie friends with some background info. When we first decided to build the Directed Edge engine, we’d built some in-memory and RDF-store based prototypes to start hashing out the recommendations algorithms, but the RDF stores couldn’t keep up performance-wise and staying memory-based obviously wasn’t an option for persistent data. So, on to geekery. Column Locking
Introducing Amazon RDS - The Amazon Relational Database Service We are always looking for ways to make it faster, simpler, and more fun to develop applications of all types. Every hour that you don't spend fiddling with hardware, tracing cables, installing operating systems or managing databases is an hour that you can spend on the unique and value-added aspects of your application. Today I'd like to tell you about our newest service, the Amazon Relational Database Service, or Amazon RDS for short. Now in beta, RDS makes it easier for you to set up, operate, and scale a relational database in the cloud. You get direct database access without worrying about infrastructure provisioning, software maintenance, or common database management tasks. Using the RDS APIs or the command-line tools, you can access the full capabilities of a complete, self-contained MySQL 5.1 database instance in a matter of minutes. Much of what you already know about building applications with MySQL will still apply. Amazon RDS is really easy to use. -- Jeff;
Building Scalable Databases: Pros and Cons of Various Database Sharding Schemes Database sharding is the process of splitting up a database across multiple machines to improve the scalability of an application. The justification for database sharding is that after a certain scale point it is cheaper and more feasible to scale a site horizontally by adding more machines than to grow it vertically by adding beefier servers. Why Shard or Partition your Database? Let's take Facebook.com as an example. At some point during the development of Facebook, they reached the physical capacity of their database server. Now that we have an understanding of when and why one would shard a database, the next step is to consider how one would actually partition the data into individual shards. How Sharding Changes your Application In a well designed application, the primary change sharding adds to the core application code is that instead of code such as the actual connection information about the database to connect to depends on the data we are trying to store or access.