text:
Marvel Assembles an Official Title for Third "Avengers" Movie
Comic Books, Film
Showing results 4214-4253 of 10083
M.O.D.O.K. joins the team in Ales Kot and Michael Walsh's "Secret Avengers," and CBR has a preview of the first issue!
Paul Cornell joins X-Position to discuss the massive status quo shift in Wolverine's world and what to expect for the coming year.
Mike Benson and Tan Eng Huat are set for "Deadly Hands of Kung Fu," a four-issue Marvel miniseries focused on Shang-Chi, master of Kung Fu.
"Captain America" writer Rick Remender tells CBR about the Cold War's impact on the series' current crop of villains and how his "Winter Soldier" mini fits in.
Marvel's latest trailer offers a new look at Mike Carey and Sal Larocca's "Battle of the Atom" spinoff OGN, "X-Men: No More Humans."
CBR has an
Charles Soule talks about his plans for the ongoing "She-Hulk," which include setting up Jen Walters new law practice and a run in with Doctor Doom.
Nathan Edmondson & Phil Noto's excellent "Black Widow" #3 is a "finely constructed, utterly engaging comic" with the added bonus of crocodiles.
Check out a new preview of Dennis Hopeless & Kev Walker's "Avengers Undercover" #1 as the "Avengers Arena" kids take a darker turn on March 12.
With Magneto getting his first monthly series decades after his debut, we turn our attention to five other classic characters worthy of ongoing attention.
ROBOT 6 finds something everyone can relate to in the story of a Muslim teenager from New Jersey who wants nothing more than to be like her hero Carol Danvers.
With the impending debut of Marvel's new volume of "New Warriors," ROBOT 6's Corey Blake reflects on what drew him to the original series.
ROBOT 6's Chris Arrant chats with "Wolverine" artist Ryan Stegman about his artistic influences, the long road to "Wolverine" and his career trajectory.
With a new "Wolverine" #1 on sale, CSBG examines Wolverine's first gimmick cover, 1991's "Wolverine" #50, to determine if it was good or just a gimmick.
The second half of the story behind the Hobgoblin's true identity is filled with mighty Marvel retcons, backtracking and revelations!
Did an openly gay Northstar nearly helm a solo series in the early '90s? How did Peter David get the last word in a battle with a Paramount executive?
CBR has a preview of the grand finale of "Iron Metropolitan" in Kieron Gillen and Joe Bennett's "Iron Man" #22!
Al Ewing and Lee Garbett's "Loki: Agent of Asgard" #1 "is proof that blatant fan service can also make for a brilliant comic."
Nathan Edmondson and Mitch Gerads continue to put Frank through the West Coast wringer in this preview for "The Punisher" #3.
The superstar writer talks with CBR News about "scraping back the accumulated barnacles" on Marvel's Moon Knight. Plus, exclusive art!
Read previews of Marvel's Feb. 12 releases including "All-New X-Factor" #3, "Deadpool" #23, "She-Hulk" #1, "Superior Spider-Man" #27.NOW and more!
Marvel has partnered with iVerse for the new "Marvel Global Comics" app, offering some of their most famous stories in 12 different languages.
Mark Waid & editor Ellie Pyle spoke to CBR about "Daredevil" #1.50, which celebrates the Man Without Fear's 50th anniversary by looking to his future.
Writer Jason Latour and artist Mahmud Asrar usher in a brand new school year in All-New Marvel NOW!'s "Wolverine and the X-Men" #1!
After leaving the Uncanny X-Men, Magneto will wage a one-man-war for mutantkind in Cullen Bunn & Gabriel Hernandez Walta's new "Magneto" ongoing.
G. Willow Wilson & Adrian Alphona's "Ms. Marvel" #1 "will captivate readers, paving a fantastic, fresh direction for the Marvel Universe."
This week, CBR presents exclusive previews of Marvel's all-new "X-Force" #1 and the A.I.M.-threatened "Avengers" #26!
Paul Cornell, Ryan Stegman and more discuss the recent All-New Marvel NOW! relaunch of "Wolverine" and plans for the series' future.
The story of how Roderick Kingsley became the one, true Hobgoblin is filled with twists, turns and surprises befitting a classic villain's back story.
Nathan Edmondson & Mitch Gerads' "The Punisher" #1 is "a worthwhile beginning of a new era for the character" in its All-New Marvel NOW! relaunch.
Kieron Gillen catches CBR up on "Iron Metropolitan," which pits Tony Stark against the Mandarin's sentient rings and upcoming "Iron Man Annual."
"Deadpool" scribe Gerry Duggan joins X-Position to discuss all things Deadpool, including the Infinite Comic and story structure. Plus, exclusive art!
Charles Soule discusses how thousands of new Inhumans will impact the Marvel U, sharing an exclusive look at some of Joe Mad's interiors.
The newest iteration of Marvel's "New Warriors" continues to take shape in this exclusive preview of Christopher Yost and Marcus To's second isse.
Nick Lowe has been named Senior Editor of Marvel's Spider-Man-related comics, shifting to the web-slinger from world of the X-Men.
The former "Office" temp will play Alistair Smythe in the Spidey sequel, a character recently seen in writer Dan Slott's Spider-Man run.
Discussing the ebbs and flow of surfing, art and music, Mike Allred is completely dialed into the concept of his and Dan Slott's "Silver Surfer."
It's the end of the year at the Jean Grey School, and Jason Aaron and an all-star roster of artists send the students off for the summer on Feb. 26!
Has the long-awaited republishing of Alan Moore's "Miracleman" been marred by edits to the digital version available via the Marvel Comics app?
Batman group editor Mike Marts will return to Marvel, joining the publisher in February as an executive editor.
Archives: All | Comic Books | TV/Film | Video Games | Web Comics


text:
The annual Closing the Health Gap expo was held at Washington Park Saturday. It was the first year the event was outside. Planners said it was a big success. The expo provided health information and screenings for children and adults. The expo has helped more than 40,000 since it began in 2003.
No email address was supplied by
To complete your registration on this site, please supply an address.
Please confirm or modify the email address to which you will have subscription offers sent.
For a more personalized experience, please supply the following optional information.

subset: commoncrawl


text:
Hey, I'm a potential new user of Linux. Got a Question.
And as a potential new user, I've been looking around and researching Linux for quite awhile - well actually for the last 2 days or so. I've found a listing of possible "distributors" through a website called linux.org.
To explain my needs, I am a player of World of Warcraft and an amateur artist. My computer is an AMD Athlon(tm) 64 Processor 3000+ 2.01 GHz, 2.50 GB of RAM, ASUS K8 Motherboard, and I use Windows XP Corporate Edition (Cracked) in a Maxter 152 GB Hard Drive, with additional IBM Deskstar 19.1 GB Hard Drive, and I also have a WD 232 GB External Hard Drive. I have around 109 GB of free space on the Maxter that I use for my WinXP OS on.
What I need to know is could you tell me or recommend what to get for a Linux OS on my machine, and how would I partition my Maxter Hard Drive, and how much space would it literally take?
best search engine
it's not that straight forward.
hi am am that new to linux
hi am am that new to linux and laptops that i dont even know if i am posting this is the right place but i am at the end of my teather with my acer aspire one net book got msn working and everything else is running fine i just cant download anything at i see a song i want or a video and click download and it will say format not recognised cant open or if it downloads then i just get a screen full of writing i dont know what to do ther than that i love my little net book think it is great
Trending Topics
Practical Task Scheduling Deployment
On Demand
One of the best things about the UNIX environment (aside from being stable and efficient) is the vast array of software tools available to help you do your job. Traditionally, a UNIX tool does only one thing, but does that one thing very well. For example, grep is very easy to use and can search vast amounts of data quickly. The find tool can find a particular file or files based on all kinds of criteria. It's pretty easy to string these tools together to build even more powerful tools, such as a tool that finds all of the .log files in the /home directory and searches each one for a particular entry. This erector-set mentality allows UNIX system administrators to seem to always have the right tool for the job.
Cron traditionally has been considered another such a tool for job scheduling, but is it enough? This webinar considers that very question. The first part builds on a previous Geek Guide, Beyond Cron, and briefly describes how to know when it might be time to consider upgrading your job scheduling infrastructure. The second part presents an actual planning and implementation framework.
Join Linux Journal's Mike Diehl and Pat Cameron of Help Systems.
Free to Linux Journal readers.View Now!
|The Firebird Project's Firebird Relational Database
|Jul 29, 2016
|Stunnel Security for Oracle
|Jul 28, 2016
|SUSE LLC's SUSE Manager
|Jul 21, 2016
|My +1 Sword of Productivity
|Jul 20, 2016
|Non-Linux FOSS: Caffeine!
|Jul 19, 2016
|Murat Yener and Onur Dundar's Expert Android Studio (Wrox)
|Jul 18, 2016
- Stunnel Security for Oracle
- The Firebird Project's Firebird Relational Database
- Murat Yener and Onur Dundar's Expert Android Studio (Wrox)
- SUSE LLC's SUSE Manager
- Managing Linux Using Puppet
- My +1 Sword of Productivity
- Non-Linux FOSS: Caffeine!
- Google's SwiftShader Released
- Doing for User Space What We Did for Kernel Space
- SuperTuxKart 0.9.2 Released
Geek Guides
With all the industry talk about the benefits of Linux on Power and all the performance advantages offered by its open architecture, you may be considering a move in that direction. If you are thinking about analytics, big data and cloud computing, you would be right to evaluate Power. The idea of using commodity x86 hardware and replacing it every three years is an outdated cost model. It doesn't consider the total cost of ownership, and it doesn't consider the advantage of real processing power, high-availability and multithreading like a demon.
This ebook takes a look at some of the practical applications of the Linux on Power platform and ways you might bring all the performance power of this open architecture to bear for your organization. There are no smoke and mirrors here—just hard, cold, empirical evidence provided by independent sources. I also consider some innovative ways Linux on Power will be used in the future.Get the Guide


text:
Bill Reid's
SCROLL DOWN For Upcoming Shows, Albums, and other GOOD STUFF!
Bill Reid BOOKINGS: email@example.com
Players, Friends, Guitars
and other fancy stuff
Uncle Will Schreiber
& Song LICENSING
to Bill Reid Songs
Coming soon:
Performance Schedule
January 21, 2016 (Thurs. 6-8PM) Blues Happy Hour @ Brass Hall, Marble Falls, Texas (Solo)
January 23, 2016 (Sat., 1:00PM) @ Luckenbach Blues Fest, Luckenbach, Texas (Bill Reid Trio)
January 28, 2016 (Thurs. 6-8PM) Blues Happy Hour @ Brass Hall, Marble Falls, Texas (Solo)
January 30, 2016 (Sat., 3:00PM) @ Fiesta 290, Fredericksburg, Texas (Bill Reid Trio)
February 13, 2016 (Sat., 3:00PM) @ Fiesta 290, Fredericksburg, Texas (Bill Reid Trio)
March 5, 2016 (Sat., 3:00PM) @ Fiesta 290, Fredericksburg, Texas (Bill Reid Trio)
March 12, 2016 (Sat., 11:00AM) @ Barton Creek Farmers' Market, Austin, Texas (Solo)
". . . beautifully performed and produced!" (WOUTER KELLERMAN, Grammy-winning flutist)
ORDER SIGNED ALBUMS directly from Fewer Sorrows Music ($15 + shipping, etc. = $20.00)
Order MP3 Downloads and complete albums from CDBaby (our favorite download site)
Order Complete Albums from My Texas Music (nice folks!)
Order MP3 Downloads and Complete Albums from Amazon
is a 501(c)(3) charitible organization (my brother is a Board member) that provides needed gear and equipment to U.S. soldiers at the "tip of the spear" in foreign deployments. Our combat troops are asked to do more and more, often with less and less support. You can help in several ways, (1) by letting deployed soldiers know about the website (www.supportasoldier.us) so they can request items (free), (2) by spreading the word and, of course, (3) by donating at the same website or to the address provided there. THANKS!
Rest in Peace, two loved members of the Nashville and Country music community
Romeo Sullivan, Hall-of-Fame DJ, Hattiesburg, MS, died November, 2013
Tommy "Tommy Boom" Wells, drummer/percussionist of Nashville, died November, 2013
More Stuff! Purchase the albums below, and individual MP3s, at iTunes, Amazon, CD Baby, My Texas Music, and other online outlets!
"A rich tapestry that brings every song alive."
Albums & MP3s
And don't forget our outstanding Honky Tonk cousin, Chris Reeves!
Cover design copyright (c) 2011, Chris Reeves and Fewer Sorrows Music
Buy the Chris Reeves CD HERE!
A small homage to Albert Bellson
Mr. Bellson was my first guitar teacher, in St. Paul, as I was getting a minor in music theory and classical guitar at the University of Minnesota. He was a virtuoso and a wonderful teacher of both classical guitar and his primary instrument, the classical mandolin. He tried mightily to get me to practice, but I would not.
Search out his biography. If you spell his name wrong (Albert Belson, Alberto Balzone, Alfonso Ballazone), you'll end up here and be able to use the correct spelling above.
Cowboy Doug Davis
Fewer Sorrows Music is a BMI(R) pubisher
THANKS for visiting Fewer Sorrows Music!
This site is updated often. Drop back from time to time to see if it's gotten any better.
All "Reid" content (music, lyrics, recordings, original photographs, album covers) is copyright (c) 1969-2015 by William H. Reid (BMI)
except "(I'm Leavin' You) One Town at a Time," copyright (c) 2000 by Elise P. and William H. Reid (BMI).
Album photography by Valerie Fremin and Stephanie Peterson, copyright (c) 2011-15 and 2010, respectively, by William H. Reid and Fewer Sorrows Music.
Bill Reid is a BMI songwriter and a member of The Recording Academy (NARAS) (www.Grammy.com).


text:
Text Size: - + | Print Page | Email Page
Hi, my name is Emily.
That's my family over there. I have a little brother named Josh and this is my dog... his name is Flanders.
Emily's Scrapbook
A few days after my surprise birthday party, Mommy and Daddy told me our family would have another surprise, a new baby. With my parent's help, I made a colorful scrapbook describing what happened while we waited for the baby to arrive. Click the picture to see my scrapbook.
My New Sister (or Brother)
If you're like me, I had a lot of questions about the new baby. Click a picture below to learn more with me.
Review Date:
Reviewed By: Irina Burd, MD, PhD, Maternal Fetal Medicine, Johns Hopkins University, Baltimore, MD. Review provided by VeriMed Healthcare Network.
The information provided herein should not be used during any medical emergency or for the diagnosis or treatment of any medical condition. A licensed medical professional should be consulted for diagnosis and treatment of any and all medical conditions. Links to other sites are provided for information only -- they do not constitute endorsements of those other sites. © 1997- A.D.A.M., Inc. Any duplication or distribution of the information contained herein is strictly prohibited.


text:
SUMMARY: If your sites images are not optimized, numerous opportunities to attract visitors are wasted. To be a source of targeted traffic, images first have to be seen by search engines. And, if your images are not designed correctly, you are missing prospects who would arrive from: searches that include image results, image-specific search engines, and third-party image-hosting sites.
Find out the key factors to getting the most out of your sites images, and ultimately, enticing as many visitors as possible to your site. Includes proof of optimization impact; advice on what prospects want to see.
Get the latest research on email, lead gen, and social media along with MarketingSherpa updates and promotions.
Website Optimization: How Brian Gavin Diamonds overcame 'mobilegeddon'
Read More »


text:
Clean Technologies and Environmental Policy
, Volume 12, Issue 4, pp 341-351
First online:
Life cycle impact assessment research developments and needs
- Jane C. BareAffiliated withSystems Analysis Branch, Sustainable Technology Division, National Risk Management Research Laboratory, Office of Research and Development, US Environmental Protection Agency Email author
Rent the article at a discountRent now
* Final gross prices may vary according to local VAT.Get Access
Life cycle impact assessment (LCIA) developments are explained along with key publications which record discussions which comprised ISO 14042 and SETAC document development, UNEP SETAC Life Cycle Initiative research, and research from public and private research institutions. It is recognized that the short list of impact categories has remained fairly constant, even after extensive discussions. The termination point of impact assessment modeling (e.g., inventory, midpoint, endpoint, damage, single score) has been discussed extensively, and the advantages and disadvantages of these different levels are well published. Early LCIAs were conducted independent of system location, but now site-specificity has been a research topic for many of the local and regional categories (e.g., acidification, eutrophication, and smog formation). In reality, even though many advances have been made in site-specific analysis, the life cycle assessment (LCA) case studies are often limited to their inventory data, and as a result, most LCAs are still site-generic even though the LCIA methodologies exist to allow for site-specific analysis. Pollutant-based impacts have received the most research effort and support in the past, but resource depletion categories (e.g., land use and water use) are now recognized as being highly complex, site-specific, data intensive, and important for contributing toward the sustainability of the planet. Efforts in these categories are still in the neophyte stages and are expected to have the greatest advances in the upcoming years.
KeywordsLife cycle assessment (LCA) Life cycle impact assessment (LCIA) Sustainability metrics Environmental standards Impact assessment
Reference tools
- Title
- Life cycle impact assessment research developments and needs
- Journal
Clean Technologies and Environmental Policy
Volume 12, Issue 4 , pp 341-351
- Cover Date
- 2010-08
- 10.1007/s10098-009-0265-9
- Print ISSN
- Online ISSN
- 1618-9558
- Publisher
- Springer-Verlag
- Additional Links
- Topics
- Keywords
- Life cycle assessment (LCA)
- Life cycle impact assessment (LCIA)
- Sustainability metrics
- Environmental standards
- Impact assessment
- Industry Sectors
- Authors
- Jane C. Bare (1)
- Author Affiliations
- 1. Systems Analysis Branch, Sustainable Technology Division, National Risk Management Research Laboratory, Office of Research and Development, US Environmental Protection Agency, 26 W MLK Dr, Cincinnati, OH, 45268, USA


text:
Your Account
Data Science Starter Kit
The tools you need to get started with data, from basic statistics to complex modeling and large-scale analytics.
Get all 9 titles for a 60% savings.
Photographic Visions
By 1x.com
This Wrox Blox gives developers a hands-on look at some of the commonly used AJAX Control Toolkit controls, focusing on the CollapsiblePanel, Accordion, Tabs, ModalPopup, TextboxWatermark, AutoComplete, and CascadingDropDown. Developers also gain the tools they need to effectively use and understand the other controls found in the Toolkit. The starting point is an introduction to ASP.NET AJAX discussing the ASP.NET AJAX Extensions 1.0 and AJAX in .NET 3.5, laying the groundwork for understanding how all the pieces of the ASP.NET AJAX Framework interoperate. The introduction focuses on controls in the Extensions such as the ScriptManager, which is required for most of the AJAX Toolkit Controls, leaving readers with an understanding of the client-side enhancements found in the Microsoft AJAX Library.
The next step is getting the ASP.NET AJAX Extensions installed and running properly as well as installing the AJAX Control Toolkit. This section also offers troubleshooting tips for common issues.
Finally, developers will venture into the world of the ASP.NET AJAX Control Toolkit. Under this section, each of the controls listed will focus on using the ASP.NET AJAX Control Toolkit with real-world tasks in mind, not just boilerplate examples. Common tasks include theming, comparison against the UpdatePanel, dynamically creating controls, venturing into client-side (JavaScript) source code, and recommended usages.
© 2016, O'Reilly Media, Inc.
(707) 827-7019(800) 889-8969
All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.


text:
...Do you feel powerless?...Afraid of other people?...Are you worried that your gut is too big and your penis is too small?Try Phyrarm--available over-the-counter!Yes, Phyrarm can make you feel strong and vibrant again, without all that pesky diet and exercise. It can even make you feel safe around strangers, without going to all the trouble of getting to know them. It may even save the lives of dozens of Americans each year!Ask your doctor if Phyrarm is right for you! Small … [Read more...]
Phyrarm May Be for You!
Thanksgiving Defiance.
Thanksgiving.Seems like a goodthing right? Seems kinda holy. Seems kinda churchy.Except, it's not a Christian holiday. It's not part of the liturgical calendar, and they don't really train us preachers in how to deal with it. Like Memorial Day or the Fourth of July, it's just one of those secular, civil holidays that many American church-goers "assume" is blessed and sanctioned by the Church.Now it's not like Thanksgiving is foreign to Christianity. We tend to think it's good to give … [Read more...]
Enough With the Progressive Christian Outrage Machine!
"Hope has two beautiful daughters - their names are anger and courage; anger at the way things are, and courage to see that they do not remain the way they are." St. AugustineBeing at dis-ease with injustice is why progressive Christianity seeks to be engaged in the world in order to transform it. "Accepting what is" may be a generic spiritual value, but moral quietude is not a Christian value.That said, progressive Christians would do well to be a LOT more discerning about what we … [Read more...]
"I'm no longer a Protestant. Sort of."
The following is a blog written by a first time guest contributor to The Holy Kiss. Part of my advocating for progressive Christianity is seeking to amplify the voice of young progressive Christians. Simone Ramacci is a young Italian Christian who is currently studying at a university in England. I hope you find his ponderings as insightful as I do. - Roger =======Today I did something I never expected I would: I went into a church and asked for a priest to take my confession. I spoke to … [Read more...]
Why they killed Jesus.
The Romans didn't kill Jesus because he performed miracles or healed people on any day of the week. He wasn't killed because he taught (or criticized) spiritual truths or religious practices. They executed him because of his subversive politics and his perceived threat to the stability of the Palestinian region of the Roman empire.They killed him for his work in organizing a labor movement of disgruntled Galilean fishermen who were sick and tired of being oppressed by unjust Roman taxation. … [Read more...]
Let's change the Lord's Prayer. (again)
Let's face it, the world has changed. That is, our understanding of it has changed - and how we perceive things - is reality for humans. For the first few centuries of Christianity, people believed in a cosmology that had a 3-tiered universe; i.e., one where Heaven was (literally) above us. Earth is where we dwell now. And below us, for unfortunate souls, a (literal) Hell.That view no longer makes sense to most people in the 21st century. We know that pretty much every point in the universe … [Read more...]


text:
I've just launched my new website modern-sql.com. It's very small right now—just six content pages—but it will grow over next month and years and might eventually become a book like Use-The-Inde-Luke.com did. So I though I'd better get you on board right now so you can grow as the site grows.
So what is modern SQL about? Yet another SQL reference? Absolutely not. There are definitively enough references out there. And they all suffer from the same issues:
They hardly cover more than entry-level SQL-92. Some tutorials don't even mention that they describe a proprietary dialect.
The examples just show the syntax but not how to solve real-world problems.
They don't document the availability of the features amongst different database products.
No question, the latter ones are a consequence of the first one. However, modern SQL is different:
modern SQL covers SQL:2011—the current release of the ISO SQL standard. But it adheres to the fact that many databases are developed against elder releases or only support a subset of the standard.
modern SQL shows how to use the most recent SQL features to solve real world problems. It also provides "conforming alternatives" as a way to solve the same problem using other (often elder) standard methods. If necessary and possible it will also show "proprietary alternatives."
Note that there is a hierarchy: the recommended (most idiomatic) approach is shown first, standard conforming alternatives next and proprietary alternatives last but only if inevitable.
modern SQL documents the availability of the features amongst six SQL databases (the example is about values):
Go here if you don't see an image above.
Click on a feature (e.g., "valid where select is valid") to see how the availability of the features has evolved.
There you can see that SQLite just recently started to accept values where select is valid. The timeline view also documents the last checked version for those databases that don't support a feature.
Inevitably, modern SQL will become both: an homage to the SQL standard and a rant about its poor adaptation. The availability documentation and the above mentioned alternative approaches (conforming or proprietary) are there to keep the "rant" a productive one. Look at the "select without from" use-case to get the idea.
This is just the first step of a long journey. I invite you to follow it via Twitter, Email or RSS.
"SQL has gone out of fashion lately—partly due to the NoSQL movement, but mostly because SQL is often still used like 20 years ago. As a matter of fact, the SQL standard continued to evolve during the past decades resulting in the current release of 2011. In this session, we will go through the most important additions since the widely known SQL-92, explain how they work and how PostgreSQL supports and extends them. We will cover common table expressions and window functions in detail and have a very short look at the temporal features of SQL:2011 and the related features of PostgreSQL."
This is the abstract for the talk I've given at FOSDEM in Brussels on Saturday. The PostgreSQL community was so kind to host this talk in their (way too small) devroom—thus the references to PostgreSQL. However, the talk is build upon standard SQL and covers features that are commonly available in DB2, Oracle, SQL Server and SQLite. MySQL does not yet support any of those features except OFFSET which is evil.
One last thing before going on to the slides: Use The Index, Luke has got a shop. Stickers, coasters, books, mugs. Have a look.
I've just completed IBM DB2 for Linux, Unix and Windows (LUW) coverage here on Use The Index, Luke as preparation for an upcoming training I'm giving. This blog post describes the major differences I've found compared to the other databases I'm covering (Oracle, SQL Server, PostgreSQL and MySQL).
Free & Easy
No Easy Explain
Emulating Partial Indexes is Possible
INCLUDE Clause Only for Unique Indexes
Almost No NULLS FIRST/LAST Support
SQL:2008 FETCH FIRST but not OFFSET
Decent Row-Value Predicates Support
Well, let's face it: it's IBM software. It has a pretty long history. You would probably not expect that it is easy to install and configure, but in fact: it is. At least DB2 LUW Express-C 10.5 (LUW is for Linux, Unix and Windows, Express-C is the free community edition). That might be another surprise: there is a free community edition. It's not open source, but it's free as in free beer.
The first problem I stumbled upon is that DB2 has no easy way to display an execution plan. No kidding. Here is what IBM says about it:
Explain a statement by prefixing it with explain plan for
explain plan for
This stores the execution plan in a set of tables in the database (you'll need to create these tables first). This is pretty much like in Oracle.
Display a stored explain plan using db2exfmt
This is a command line tool, not something you can fall from an SQL prompt. To run this tool you'll need shell access to a DB2 installation (e.g. on the server). That means, that you cannot use this tool over an regular database connection.
There is another command line tool (db2expln) that combines the two steps from above. Apart from the fact that this procedure is not exactly convenient, the output you get an ASCII art:
Access Plan:
Total Cost: 60528.3
Query Degree: 1
( 1)
( 2)
49534.9 10000
( 3) ( 4)
59833.6 687.72
67325 770
| |
1.00933e+06 10000
Please note that this is just an excerpt—the full output of db2exfmt has 400 lines. Quite a lot information that you'll hardly ever need. Even the information that you need all the time (the operations) is presented in a pretty unreadable way (IMHO). I'm particularly thankful that all the numbers you see above are not labeled—that's really the icing that renders this "tool" totally useless for the occasional user.
However, according to the IBM documentation there is another way to display an execution plan: "Write your own queries against the explain tables." And that's exactly what I did: I wrote a view called last_explained that does exactly what it's name suggest: it shows the execution plan of the last statement that was explained (in a non-useless formatting):
Explain Plan
ID | Operation | Rows | Cost
2 | HSJOIN | 49535 of 10000 | 60528
3 | TBSCAN SALES | 49535 of 1009326 ( 4.91%) | 59833
4 | TBSCAN EMPLOYEES | 10000 of 10000 (100.00%) | 687
Predicate Information
Explain plan by Markus Winand - NO WARRANTY
I'm pretty sure many DB2 users will say that this presentation of the execution plan is confusing. And that's OK. If you are used to the way IBM presents execution plans, just stick to what you are used to. However, I'm working with all kinds of databases and they all have a way to display the execution plan similar to the one shown above—for me this format is much more useful. Further, I've made a useful selection of data to display: the row count estimates and the predicate information.
You can get the source of the last_explained view from here or from the URL shown in the output above. I'm serious about the no warranty part. Yet I'd like to know about problems you have with the view.
Partial indexes are indexes not containing all table rows. They are useful in three cases:
To preserve space when the index is only useful for a very small fraction of the rows. Example: queue tables.
To establish a specific row order in presence of constant non-equality predicates. Example: WHERE x IN (1, 5, 9) ORDER BY y. An index like the following can be used to avoid a sort operation:
WHERE x IN (1, 5, 9) ORDER BY y
CREATE INDEX … ON … (y)
WHERE x IN (1, 5, 9)
To implement unique constraints on a subset of rows (e.g. only those WHERE active = 'Y').
WHERE active = 'Y'
However, DB2 doesn't support a where clause for indexes like shown above. But DB2 has many Oracle-compatibility features, one of them is EXCLUDE NULL KEYS: "Specifies that an index entry is not created when all parts of the index key contain the null value." This is actually the hard-wired behaviour in the Oracle database and it is commonly exploited to emulate partial indexes in the Oracle database.
Generally speaking, emulating partial indexes works by mapping all parts of the key (all indexed columns) to NULL for rows that should not end up in the index. As an example, let's emulate this partial index in the Oracle database (DB2 is next):
CREATE INDEX messages_todo
ON messages (receiver)
WHERE processed = 'N'
The solution presented in SQL Performance Explained uses a function to map the processed rows to NULL, otherwise the receiver value is passed through:
FUNCTION pi_processed(processed CHAR, receiver NUMBER)
IF processed IN ('N') THEN
RETURN receiver;
It's a deterministic function and can thus be used in an Oracle function-based index. This won't work with DB2, because DB2 doesn't allow user defined-functions in index definitions. However, let's first complete the Oracle example.
CREATE INDEX messages_todo
ON messages (pi_processed(processed, receiver));
This index has only rows WHERE processed IN ('N')—otherwise the function returns NULL which is not put in the index (there is no other column that could be non-NULL). Voilà: a partial index in the Oracle database.
WHERE processed IN ('N')
To use this index, just use the pi_processed function in the where clause:
SELECT message
FROM messages
WHERE pi_processed(processed, receiver) = ?
This is functionally equivalent to:
SELECT message
FROM messages
WHERE processed = 'N'
AND receiver = ?
So far, so ugly. If you go for this approach, you'd better need the partial index desperately.
To make this approach work in DB2 we need two components: (1) the EXCLUDE NULL KEYS clause (no-brainer); (2) a way to map processed rows to NULL without using a user-defined function so it can be used in a DB2 index.
Although the second one might seem to be hard, it is actually very simple: DB2 can do expression based indexing, just not on user-defined functions. The mapping we need can be accomplished with regular SQL expressions:
CASE WHEN processed = 'N' THEN receiver
This implements the very same mapping as the pi_processed function above. Remember that CASE expressions are first class citizens in SQL—they can be used in DB2 index definitions (on LUW just since 10.5):
CREATE INDEX messages_not_processed_pi
ON messages (CASE WHEN processed = 'N' THEN receiver
This index uses the CASE expression to map not to be indexed rows to NULL and the EXCLUDE NULL KEYS feature to prevent those row from being stored in the index. Voilà: a partial index in DB2 LUW 10.5.
To use the index, just use the CASE expression in the where clause and check the execution plan:
FROM messages
WHERE (CASE WHEN processed = 'N' THEN receiver
Explain Plan
ID | Operation | Rows | Cost
2 | TBSCAN MESSAGES | 900 of 999999 ( .09%) | 49686
Predicate Information
Oh, that's a big disappointment: the optimizer didn't take the index. It does a full table scan instead. What's wrong?
If you have a very close look at the execution plan above, which I created with my last_explained view, you might see something suspicious.
Look at the predicate information. What happened to the CASE expression that we used in the query? The DB2 optimizer was smart enough rewrite the expression as WHERE processed = 'N' AND receiver = ?. Isn't that great? Absolutely!…except that this smartness has just ruined my attempt to use the partial index. That's what I meant when I said that CASE expressions are first class citizens in SQL: the database has a pretty good understanding what they do and can transform them.
WHERE processed = 'N' AND receiver = ?
We need a way to apply our magic NULL-mapping but we can't use functions (can't be indexed) nor can we use CASE expressions, because they are optimized away. Dead-end? Au contraire: it's pretty easy to confuse an optimizer. All you need to do is to obfuscate the CASE expression so that the optimizer doesn't transform it anymore. Adding zero to a numeric column is always my first attempt in such cases:
CASE WHEN processed = 'N' THEN receiver + 0
The CASE expression is essentially the same, I've just added zero to the RECEIVER column, which is numeric. If I use this expression in the index and the query, I get this execution plan:
ID | Operation | Rows | Cost
2 | FETCH MESSAGES | 40000 of 40000 | 13071
3 | RIDSCN | 40000 of 40000 | 1665
4 | SORT (UNQIUE) | 40000 of 40000 | 1665
5 | IXSCAN MESSAGES_NOT_PROCESSED_PI | 40000 of 999999 | 1646
Predicate Information
The partial index is used as intended. The CASE expression appears unchanged in the predicate information section.
I haven't checked any other ways to emulate partial indexes in DB2 (e.g., using partitions like in more recent Oracle versions).
As always: just because you can do something doesn't mean you should. This approach is so ugly—even more ugly than the Oracle workaround—that you must desperately need a partial index to justify this maintenance nightmare. Further it will stop working whenever the optimizer becomes smart enough to optimize +0 away. However, then you just need put an even more ugly obfuscation in there.
With the INCLUDE clause you can add extra columns to an index for the sole purpose to allow in index-only scan when these columns are selected. I knew the INCLUDE clause before because SQL Server offers it too, but there are some differences:
In SQL Server INCLUDE columns are only added to the leaf nodes of the index—not in the root and branch nodes. This limits the impact on the B-tree's depth when adding many or long columns to an index. This also allows to bypass some limitations (number of columns, total index row length, allowed data types). That doesn't seem to be the case in DB2.
In DB2 the INCLUDE clause is only valid for unique indexes. It allows you to enforce the uniqueness of the key columns only—the INCLUDE columns are just not considered when checking for uniqueness. This is the same in SQL Server except that SQL Server supports INCLUDE columns on non-unique indexes too (to leverage the above-mentioned benefits).
The NULLS FIRST and NULLS LAST modifiers to the order by clause allow you to specify whether NULL values are considered as larger or smaller than non-NULL values during sorting. Strictly speaking, you must always specify the desired order when sorting nullable columns because the SQL standard doesn't specify a default. As you can see in the following chart, the default order of NULL is indeed different across various databases:
In this chart, you can also see that DB2 doesn't support NULLS FIRST or NULLS LAST—neither in the order by clause no in the index definition. However, note that this is a simplified statement. In fact, DB2 accepts NULLS FIRST and NULLS LAST when it is in line with the default NULLS order. In other words, ORDER BY col ASC NULLS FIRST is valid, but it doesn't change the result—NULLS FIRST is anyways the default. Same is true for ORDER BY col DESC NULLS LAST—accepted, but doesn't change anything. The other two combinations are not valid at all and yield a syntax error.
ORDER BY col ASC NULLS FIRST
ORDER BY col DESC NULLS LAST
DB2 supports the fetch first … rows only clause for a while now—kind-of impressive considering it was "just" added with the SQL:2008 standard. However, DB2 doesn't support the offset clause, which was introduced with the very same release of the SQL standard. Although it might look like an arbitrary omission, it is in fact a very wise move that I deeply respect. offset is the root of so much evil. In the next section, I'll explain how to live without offset.
Side node: If you have code using offset that you cannot change, you can still activate the MySQL compatibility vector that makes limit and offset available in DB2. Funny enough, combining fetch first with offset is then still not possible (that would be standard compliant).
SQL row-values are multiple scalar values grouped together by braces to form a single logical value. IN-lists are a common use-case:
WHERE (col_a, col_b) IN (SELECT col_a, col_b FROM…)
This is supported by pretty much every database. However, there is a second, hardly known use-case that has pretty poor support in today's SQL databases: key-set pagination or offset-less pagination. Keyset pagination uses a where clause that basically says "I've seen everything up till here, just give me the next rows". In the simplest case it looks like this:
WHERE time_stamp < ?
ORDER BY time_stamp DESC
Imagine you've already fetched a bunch of rows and need to get the next few ones. For that you'd use the time_stamp value of the last entry you've got for the bind value (?). The query then just return the rows from there on. But what if there are two rows with the very same time_stamp value? Then you need a tiebreaker: a second column—preferably a unique column—in the order by and where clauses that unambiguously marks the place till where you have the result. This is where row-value predicates come in:
WHERE (time_stamp, id) < (?, ?)
ORDER BY time_stamp DESC, id DESC
The order by clause is extended to make sure there is a well-defined order if there are equal time_stamp values. The where clause just selects what's after the row specified by the time_stamp and id pair. It couldn't be any simpler to express this selection criteria. Unfortunately, neither the Oracle database nor SQLite or SQL Server understand this syntax—even though it's in the SQL standard since 1992! However, it is possible to apply the same logic without row-value predicates—but that's rather inconvenient and easy to get wrong.
Even if a database understands the row-value predicate, it's not necessarily understanding these predicates good enough to make proper use of indexes that support the order by clause. This is where MySQL fails—although it applies the logic correctly and delivers the right result, it does not use an index for that and is thus rather slow. In the end, DB2 LUW (since 10.1) and PostgreSQL (since 8.4) are the only two databases that support row-value predicates in the way it should be.
The fact that DB2 LUW has everything you need for convenient keyset pagination is also the reason why there is absolutely no reason to complain about the missing offset functionality. In fact I think that offset should not have been added to the SQL standard and I'm happy to see a vendor that resisted the urge to add it because its became part of the standard. Sometimes the standard is wrong—just sometimes, not very often ;) I can't change the standard—all I can do is teaching how to do it right and start campaigns like #NoOffset.
If you like my way of explaining things, you'll love my book "SQL Performance Explained".
It has been quiet here at the Use The Index, Luke blog lately. But that's not because I've run out of topics to write about — in fact, my blog backlog seems to be ever growing — the recent silence is just because there are some more demanding projects happening at the moment.
First of all, Use the Index, Luke got a new mascot—not exactly breaking news. However, I'm currently preparing give-aways and merchandise products featuring the new mascot. Stay tuned.
Next, Use The Index, Luke gets translated to Japanese! The first two chapters have just been published. Insiders will remember that chapter 1 and 2 make up half of the book. The translation is done by Hayato Matsuura, Takuto Matsuu has a second look over it. As far as I can tell both are making a great job and I'd like to be the first to thank them! Please help spreading the word about this in the Japanese community.
Finally, I'm just adding DB2 as a first-class citizen to Use The Index, Luke because a client wanted to get my SQL performance training based on DB2 LUW Express-C (which is free, by the way). Like the Japanese translation, this work is not yet finished. However, the appendix on execution plans is already there. Again, please help spreading the word about this in the DB2 community.
That's it. I just wanted to give you a short update.
Did you know pagination with offset is very troublesome but easy to avoid?
offset instructs the databases skip the first N results of a query. However, the database must still fetch these rows from the disk and bring them in order before it can send the following ones.
This is not an implementation problem, it's the way offset is designed:
…the rows are first sorted according to the <order by clause> and then limited by dropping the number of rows specified in the <result offset clause> from the beginning…— SQL:2011, Part 2, §4.15.3 Derived tables
…the rows are first sorted according to the <order by clause> and then limited by dropping the number of rows specified in the <result offset clause> from the beginning…
In other words, big offsets impose a lot of work on the database—no matter whether SQL or NoSQL.
But the trouble with offset doesn't stop here: think about what happens if a new row is inserted between fetching two pages?
When using offset➌ to skip the previously fetched entries❶, you'll get duplicates in case there were new rows inserted between fetching two pages➋. There are other anomalies possible too, this is just the most common one.
This is not even a database problem, it is the way frameworks implement pagination: they just say which page number to fetch or how many rows to skip. With this information alone, no database can do any better.
Now imagine a world without these problems. As it turns out, living without offset is quite simple: just use a where clause that selects only data you haven't seen yet.
For that, we exploit the fact that we work on an ordered set—you do have an order by clause, ain't you? Once there is a definite sort order, we can use a simple filter to only select what follows the entry we have see last:
AND id < ?last_seen_id
ORDER BY id DESC
This is the basic recipe. It gets more interesting when sorting on multiple columns, but the idea is the same. This recipe is also applicable to many NoSQL systems.
This approach—called seek method or keyset pagination—solves the problem of drifting results as illustrated above and is even faster than offset. If you'd like to know what happens inside the database when using offset or keyset pagination, have a look at these slides (benchmarks, benchmarks!):
On slide 43 you can also see that keyset pagination has some limitations: most notably that you cannot directly navigate to arbitrary pages. However, this is not a problem when using infinite scrolling. Showing page number to click on is a poor navigation interface anyway—IMHO.
If you want to read more about how to properly implement keyset pagination in SQL, please read this article. Even if you are not involved with SQL, it's worth reading that article before starting to implement anything.
The main reason to prefer offset over keyset pagination is the lack of tool support. Most tools offer pagination based on offset, but don't offer any convenient way to use keyset pagination.
The hall of fame of frameworks that do support keyset pagination is rather short:
jOOQ — Java Object Oriented Querying. Docs.
Django Infinite Scroll Pagination.
Ruby order_query
blaze-persistence — a rich Criteria API for JPA providers (started in July 2014)
This is where I need your help. If you are maintaining a framework that is somehow involved with pagination, I ask you, I urge you, I beg you, to build in native support for keyset pagination too. If you have any questions about the details, I'm happy to help (forum, contact form, Twitter)!
Even if you are just using software that should support keyset pagination such as a content management system or webshop, let the maintainers know about it. You might just file a feature request (link to this page) or, if possible, supply a patch. Again, I'm happy to help out getting the details right.
Take WordPress as an example.
The problem with keyset pagination is not a technical one. The problem is just that it is hardly known in the field and has no tool support. If you like the idea of offset-less pagination, please help spreading the word. Tweet it, share it, mail it, you can even re-blog this post (CC-BY-NC-ND). Translations are also welcome, just contact me beforehand—I'll include a link to the translation on this page too!
Oh, and if you are blogging, you could also add a banner on your blog to make your readers aware of it. I've prepared a NoOffset banner gallery with some common banner formats. Just pick what suits you best.
NoOffset Banner Gallery
Article: Fetching the next page — offset vs. keyset pagination
In this guest post, Chris Saxon explains a very important topic using an analogy with chocolates: When does a database use an index and when is it better not using it. Although Chris explanation has the Oracle database in mind, the principles apply to other databases too.
A common question that comes up when people start tuning queries is "why doesn't this query use the index I expect?". There are a few myths surrounding when database optimizers will use an index. A common one I've heard is that an index will be used when accessing 5% or less of the rows in a table. This isn't the case however - the basic decision on whether or not to use an index comes down to its cost.
Before getting into the details, let's talk about chocolate! Imagine you have 100 packets of M&M's. You also have a document listing the colour of each M&M and the bag it's stored in. This is ordered by colour, so we have all the blue sweets first, then the brown, green and so on like so:
You've been asked to find all the red M&M's. There's a couple of basic ways you could approach this task:
Get your document listing the colour and location of each M&M. Go to the top of the "red" section. Lookup the location of the first red M&M, pick up the bag it states, and get the sweet. Go back to your document and repeat the process for the next red chocolate. Keep going back-and-forth between your document and the bags until you've reached the end of the red section.
Pick up a number of bags at a time (e.g. 10), empty their contents out, pick out the red chocolates and return the others (back to their original bag).
Which approach is quicker?
Intuitively the second approach appears to be faster. You only select each bag once and then do some filtering of the items inside. Whereas with the first approach you have done a lot of back-and-forth between the document and the bags. This means you have to look into each bag multiple times.
We can be a bit more rigorous than this though. Let's calculate how many operations we need to do to get all the red chocolates in each case.
When going between the document and the bags (method 1), each time you lookup the location of a new sweet and fetch that bag that's a new operation. You have 100 bags with around 55 sweets in each. This means you're doing roughly 920 (100 bags x 55 sweets / 6 colours) operations (plus some work to find the red section in your document). So the "cost" of using the document is around 920.
With the second approach you collect 10 bags in one step. This means you do ( 100 bags / 10 bags per operation = ) 10 operations (plus some filtering of the chocolates in them), giving a "cost" of 10.
Comparing these costs (920 vs. 10), method 2 is the clear winner.
Let's imagine another scenario. Mars have started doing a promotion where around 1 in 100 bags contain a silver M&M. If you get the silver sweet, you win a prize. You want to find the silver chocolate!
In this case, using method 1, you go to the document to find the location of the single sweet. Then you go to that bag and retrieve the sweet. One operation (well two, including going to the document to find location of the silver chocolate), so we have a cost of two.
With method 2, you still need to pick up every single bag (and do some filtering) just to find one sweet - the cost is fixed at 10. Clearly method 1 is far superior in this case.
When Oracle stores a record to the database, it is placed in a block. Just like there are many M&Ms in a bag, (normally) there are many rows in a block. When accessing a particular row, Oracle fetches the whole block and retrieves the requested row from within it. This is analogous to us picking up a bag of M&Ms and then picking a single chocolate out.
When doing an index-range scan, Oracle will search the index (the document) to find the first value matching your where clause. It then goes back-and-forth between the index and the table blocks, fetching the records from the location pointed to by the index. This is similar to method 1, where you continually switch between the document and the M&M bags.
As the number of rows accessed by an index increases, the database has to do more work. Therefore the cost of using an index increases in line with the number of records it is expected to fetch.
When doing a full table scan (FTS), Oracle will fetch several blocks at once in a multi-block read. The data fetched is then filtered so that only rows matching your where clause are returned (in this case the red M&Ms) – the rest are discarded. Just like in method 2.
The expected number of rows returned has little impact on the work a FTS does. Its basic cost is fixed by the size of the table and how many blocks you fetch at once.
When fetching a "high" percentage of the rows from a table, it becomes far more efficient to get several blocks at once and do some filtering than it is to visit a single block multiple times.
In our M&M example above, the "full-table scan" method fetches all 100 bags in 10 operations. Whereas with "index" approach requires a separate operation for each sweet. So an index is more efficient when it points to 10 M&Ms or less.
Mars puts around 55 M&M's in each bag, so as a percentage of the "table" that's just under ( 10 M&M's / (100 bags * 55 sweets) * 100 = ) 0.2%!
What if Mars releases some "giant" M&Ms with only 10 sweets in a bag? In this case there's fewer sweets in total, so the denominator in the equation above decreases. Our FTS approach is still fixed at a "cost" of 10 for the 100 bags. This means the point at which an index is better is when accessing approximately ( 10/1000*100 = ) 1% of the "rows". A higher percentage, but still small in real terms.
If they released "mini" M&Ms with 200 in a bag, the denominator would increase. This means that the index is more efficient when accessing a very small percentage of the table!
So as you increase the space required to store a row, an index becomes more effective than a FTS. The number of rows accessed by the index remains fixed. The number of blocks required to store the data increases however, making the FTS more expensive and leading to it having a higher cost.
There's a big assumption made in the above reasoning however. It's that there's no correlation between the order M&M's are listed in the document and which bag they are in. So, for example, the first red M&M (in the document) may be in bag 1, the second in bag 56, the third in bag 20, etc.
Let's make a different assumption – that the order of red chocolates in the document corresponds to the order they appear in the bags. So the first 9 red sweets are in bag 1, the next 9 in bag 2 etc. While you still have to visit all 100 bags, you can keep the last bag accessed in your hand, only switching bags every 9 or so sweets. This reduces the number of operations you do, making the index approach more efficient.
We can take this further still. What if Mars changes the bagging process so that only one colour appears in each bag?
Now, instead of having to visit every single bag to get all the red sweets, you only have to visit around ( 100 bags / 6 colours) 16 bags. If the sweets are also placed in the bags in the same order they are listed in the document (so M&M's 1-55 are all blue and in bag 1, bag 2 has the blue M&M's 56-100, and so on until bag 100, which has yellow M&M's 496-550) you get the benefits of not switching bags compounded with the effect of having fewer bags to fetch.
This principle – how closely the order of records in a table matches the order they're listed in a corresponding index – is referred to as the clustering factor. This figure is lower when the rows appear in the same physical order in the table as they do in the index (all sweets in a bag are the same colour) and higher when there's little or no correlation.
The closer the clustering factor is to the number of blocks in a table the more likely it is that the index will be used (it is assigned a lower cost). The closer it is to the number of rows in a table, the more likely it is a FTS will be chosen (the index access is given a higher cost).
To sum up, we can see the cost-based optimizer decides whether to use an index or FTS by:
Taking the number of blocks used to store the table and dividing this by the number of blocks read in a multi-block read to give the FTS cost.
For each index on the table available to the query:
Finding the percentage of the rows in the table it expects a query to return (the selectivity)
This is then used to determine the percentage of the index expected to be accessed
The selectivity is also multiplied by the clustering factor to estimate the number of table blocks it expects to access to fetch these rows via an index
Adding these numbers together to give the expected cost (of the index)
The cost of the FTS is then compared to each index inspected and the access method with the lowest cost used.
This is just an overview of how the (Oracle) cost-based optimizer works. If you want to see the formulas the optimizer uses have a read of Wolfgang Breitling's "Fallacies of the Cost Based Optimizer" paper or Jonathan Lewis' Cost-Based Oracle Fundamentals book. The blogs of Jonathan Lewis, Richard Foote and of course Markus' articles on this site also contain many posts going into this subject in more detail.
So, I've been to PgCon 2014 in Ottawa to give a short version of my SQL performance training (hint: special offer expires soon). However, I think I ended up learning more about SQLite than about PostgreSQL there. Here is how that happened and what I actually learned.
Richard Hipp, creator of SQLite was the keynote speaker at this years PgCon. In his keynote (slides, video) he has put the focus on three topics: how PostgreSQL influenced SQLite development ("SQLite was originally written from PostgreSQL 6.5 documentation" and the "What Would PostgreSQL Do?" (WWPD) way of finding out what the SQL standard tries to tell us). The second main topic was that SQLite should be seen as an application file format—an alternative to inventing own file formats or using ZIPped XMLs. The statement "SQLite is not a replacement for PostgreSQL. SQLite is a replacement for fopen()" nails that (slide 21). Finally, Richard put a lot of emphasis on that fact that SQLite takes care of your data (crash safe, ACID)—unlike many of the so-called NoSQL systems, which Richard refers to as "Postmodern Databases: absence of objective truth; Queries return opinions rather than facts". In his keynote, Richard has also shown that SQLite is pretty relaxed when it comes to data types. As a matter of fact, SQLite accepts strings like "Hello" for INT fields. Note that it still stores "Hello"—no data is lost. I think he mentioned that it is possible to enforce the types via CHECK constraints.
Here I have to mention that I've had an e-mail conversation with Richard about covering SQLite on Use The Index, Luke last year. When our ways crossed at the PgCon hallway I just wanted to let him know that this has not been forgotten (despite the minimal progress on my side). Guess what—he was still remembering our mail exchange and immediately suggested to go through those topics once more in PgCon's hacker lounge later. Said. Done. Here comes what I learned about SQLite.
First of all, SQLite uses the clustered index concept as known from SQL Server or MySQL/InnoDB. However, before version 3.8.2 (released 2013-12-06) you always had to use an INTEGER column as clustering key. If the primary key happened to be a non-integer, SQLite was creating an integer primary key implicitly and used this as the main clustering key. Querying by the real primary key (e.g. TEXT) required a secondary index lookup. This limitation was recently lifted by introducing WITHOUT ROWID tables.
When it comes to execution plans, SQLite has two different variants: the regular explain prefix returns the byte code that is actually executed. To get an execution plan similar to what we are used to, use explain query plan. SQLite has some hints (unlike PostgreSQL) to affect the query plan: indexed by and unlikely, likelihood.
We also discussed whether or not SQLite can cope with search conditions like (col1, col2) < (val1, val2)—it can't. Here Richard was questioning the motivation to have that, so I gave him an elevator-pitch version of my "Pagination Done The PostgreSQL Way" talk. I think he got "excited" about this concept to avoid offset at all and I'm curious to see if he can make it work in SQLite faster than I'm adding SQLite content to Use The Index, Luke :)
(col1, col2) < (val1, val2)
SQLite supports limit and offset (*sigh*) but the optimizer does currently not consider limit for optimization. Offering the SQL:2008 first fetch ... rows only syntax is not planned and might actually turn out to be hard because the parser is close to run out of 8-bit token codes (when I understood that right).
Joins are basically always executed as nested loops joins but SQLite might create an "automatic transient index" for the duration of the query. We also figured out that there seems to be a oddity with they way CROSS JOIN works in SQLite: it turn the optimizers table reordering off. Non-cross joins, on the other hand, don't enforce the presence of ON or USING so that you can still build a Cartesian product using the optimizers smartness to reorder the tables.
Last but not least I'd like to mention that SQLite supports partial indexes in the way it should be: just a WHERE clause at index creation. Partial indexes are available since version 3.8.0 (released 2013-08-26).
On the personal side, I'd describe Richard as very pragmatic and approachable. He joined twitter a few month ago and all he did there is helping people who asked questions about SQLite. Really, look at his time line. That says a lot.
I'm really happy that I had the opportunity to meet Richard at a PostgreSQL conference—as happy as I was to meet Joe "Smartie" Celko years ago…at a another PostgreSQL conference. Their excellent keynote speaker selection is just one more reason to recommend PostgreSQL conferences in general. Here are the upcoming ones just in case you got curious.
ps.: There was also a unconference where I discussed the topic of Bitmap Index Only Scan (slides & minutes).
This is my own and very loose translation of an article I wrote for the Austrian newspaper derStandard.at in October 2013. As this article was very well received and the SQL vs. NoSQL discussion is currently hot again, I though it might be a good time for a translation.
Back in 2013 The Register reported that Google sets its bets on SQL again. On the first sight this might look like a surprising move because it was of all things Google's publications about MapReduce and BigTable that gave the NoSQL movement a big boost in the first place. On a second sight it turns out that there is a trend to use SQL or similar languages to access data from NoSQL systems—and that's not even a new trend. However, it raises a question: What remains of NoSQL if we add SQL again? To answer this question, I need to start with a brief summary about the history of databases.
It was undisputed for decades: store enterprise data in a relational database and use the programming language SQL to process the stored data.
The theoretical foundation for this approach, the relational model, was laid down as early as 1970. The first commercial representative of this new database type became available in 1979: the Oracle Database in release 2. Todays appearance of relational databases was mainly coined in the 1980s. The most important milestones were the formalization of the ACID criteria (Atomicity, Consistency, Isolation, Durability), which avoid accidental data corruption, and the standardization of the query language SQL—first by ANSI, one year later by ISO. From this time on SQL and ACID compliance were desirable goals for database vendors.
At first glance there were no major improvements during the next decades. The result: SQL and the relational model are sometimes called old or even outdated technology. At a closer look SQL databases evolved into mature products during that time. This process took that long because SQL is a declarative language and was way ahead of the technical possibilities of the 1980s. "Declarative language" means that SQL developers don't need to specify the steps that lead to the desired result as with imperative programming, they just describe the desired result itself. The database finds the most efficient way to gather this result automatically—which is by no means trivial so that early implementations only mastered it for simple queries. Over these decades, however, the hardware got more powerful and the software more advanced so that modern databases deliver good results for complex queries too (see here if it doesn't work for you).
It is especially important to note that the capabilities of SQL are not limited to storing and fetching data. In fact, SQL was designed to make refining and transforming data easy. Without any doubt, that was an important factor that made SQL and the relational model the first choice when it comes to databases.
Despite the omnipresence of SQL, a new trend emerged during the past few years: NoSQL. This term alone struck the nerve of many developers and caused a rapid spread that ultimately turned into a religious war. The opponents were SQL as symbol for outdated, slow, and expensive technology on the one side against an inhomogeneous collection of new, highly-scalable, free software that is united by nothing more than the umbrella brand "NoSQL."
One possible explanation for the lost appreciation of SQL among developers is the increasing popularity of object-relational mapping tools (ORM) that generally tend to reduce SQL databases to pure storage media ("persistence layer"). The possibility to refine data using SQL is not encouraged but considerably hindered by these tools. The result of the excessive use is a step-by-step processing by the application. Under this circumstances SQL does indeed not deliver any additional value and it becomes understandable why so many developers sympathise with the term NoSQL.
But the problem is that the term NoSQL was not aimed against SQL in the first place. To make that clear the term was defined to mean "not only SQL" later on. Thus, NoSQL is about complementary alternatives. To be precise it is not even about alternatives to SQL but about alternatives to the relational model and ACID. In the meantime the CAP theorem revealed that the ACID criteria will inevitably reduce the availability of distributed databases. That means that traditional, ACID compliant, databases cannot benefit from the virtually unlimited resources available in cloud environments. This is what many NoSQL systems provide a solution for: instead of sticking to the very rigid ACID criteria to keep data 100% consistent all the time they accept temporary inconsistencies to increase the availability in a distributed environment. Simply put: in doubt they prefer to deliver wrong (old) data than no data. A more correct but less catchy term would therefore be NoACID.
Deploying such systems only makes sense for applications that don't need strict data consistency. These are quite often applications in the social media field that can still fulfil their purpose with old data and even accept the loss of some updates in case of service interruption. These are also applications that could possibly need the unlimited scalability offered by cloud infrastructure. If a central database is sufficient, however, the CAP theorem does not imply a compelling reason to abandon the safety of ACID. However, using NoSQL systems can still make sense in domains where SQL doesn't provide sufficient power to process the data. Interestingly, this problem is also very dominant in the social media field: although it is easy to express a social graph in a relational model it is rather cumbersome to analyse the edges using SQL.
Notwithstanding the above, SQL is still a very good tool to answer countless questions. This is also true for questions that could not be foreseen at the time of application design—a problem many NoSQL deployments face after the first few years. That's probably also the cause for the huge demand for powerful and generic query languages like SQL.
Another aspect that strengthens the trend back to SQL goes to the heart of NoSQL: without ACID it is very difficult to write reliable software. Google said that very clearly in their paper about the F1 database: without ACID "we find developers spend a significant fraction of their time building extremely complex and error-prone mechanisms to cope with eventual consistency and handle data that may be out of date." Apparently you only learn to appreciate ACID once you lost it.
If SQL and ACID become the flavor of the time again you may wonder what's left of the NoSQL movement? Did it really waste half a decade like Jack Clark speculated at The Register? One thing we can say for sure is that SQL and ACID conformity are still desirable goals for database vendors. Further we know that there is a trade-off between ACID and scalability in cloud environments.
Of course, the race for the Holy Grail of the ideal balance between ACID and scalability has already begun. The NoSQL systems entered the field from the corner of unlimited scalability and started adding tools to control data consistency such as causal consistency. Obviously there a newcomers that use the term NewSQL to jump on the bandwagon by developing completely new SQL databases that bring ACID and SQL from the beginning but use NoSQL ideas internally to improve scalability.
And what about the established database vendors? They want to make us believe they are doing NoSQL too and release products like the "Oracle NoSQL Database" or "Windows Azure Table Storage Service." The intention to create these products for the sole purpose to ride the hype is so striking that one must wonder why they treat neither NoSQL nor NewSQL as a serious threat to their business? When looking a the Oracle Database in it's latest release 12c, we can even see the opposite trend. Although the version suffix "c" is ought to express its cloud capability it doesn't change the fact that the killer feature of this release serves a completely different need: the easy and safe operation of multiple databases on a single server. That's the exact opposite of what many NoSQL systems aim for: running a giant database on a myriad of cheap commodity servers. Virtualization is a way bigger trend than scale-out.
Is it even remotely possible that the established database vendors underwent such a fundamental misjudgment? Or is it more like that NoSQL only serves a small market niche? How many companies really need to cope with data at the scale of Google, Facebook or Twitter? Incidentally three companies that grew up on the open source database MySQL. One might believe the success of NoSQL is also based on the fact that it solves a problem that everybody would love to have. In all reality this problem is only relevant to a very small but prominent community, which managed to get a lot of attention. After all, also judging on the basis that the big database vendors don't show a serious engagement, NoSQL is nothing more than a storm in a teacup.
If you like my way to explain things, you'll love SQL Performance Explained.
MongoDB seems to be as bad for NoSQL as MySQL is for SQL
Choosing NoSQL For The Right Reason
"Just because you're using Hibernate, doesn't mean you have to use it for everything." — Gavin King, creator of Hibernate
Dear MySQL,
Thank you for introducing me to SQL. It must have been 1998 when we first met I and fell in love with the simplicity of SQL immediately. Before that I've been using C structs all the time; I had to do my joins programmatically and also create and maintain my indexes manually. It was even hard to combine several search conditions via and and or. But then there was the shiny new world of SQL you were showing me…
Everything was easily. Just write a where clause, no matter how complex, you found the right rows. Joins were equally easy to write and you took all the effort to combine the data from several tables as I needed them. I also remember how easy it became to manage the schema. Instead of writing a program to copy my data from one C struct to another, I just say alter table now—in the meanwhile it even works online in many cases! I didn't take long until I used SQL for stuff I wouldn't have thought a database could do for me. So I was quickly embracing group by and co.
But I haven't spent a lot of time with you lately. It's not because I was too busy. I'm still practicing what you have shown me! And I've moved on. Now I'm using common table expressions to organize complex queries and I use window functions to calculate running totals or just do a ranking. I'm also using joins more efficiently because I know about hash and sort/merge joins. A while ago I was wondering why you didn't tell me about these things. But then I realized that you don't know them.
I know it was not always nice what I said about you recently. But now that Oracle announced your retirement to focus on Oracle NoSQL, I realized how right this comment on reddit was. Sure you are neither the most advanced nor the most widely deployed open source SQL database, but you introduced millions of people to SQL. There should be no tears when some of them move away because they want to see what's next. Isn't that the greatest compliment a teacher can get? You can be proud of what you have accomplished.
Thank you!
ps.: Even when fooling around, I can't resist to inform my fellow readers. Besides the claim that Oracle retires MySQL, everything is true. Initially I thought Oracle NoSQL is an Aprils fool's joke but it isn't.
In 2011, I've launched the "The 3-Minute Test: What do you know about SQL performance." It consists of five questions that follow a simple pattern: each question shows a query/index pair and asks if it demonstrates proper indexing or not. Till today, this test has become one of the most popular features on Use The Index, Luke and has been completed more than 28,000 times.
Just in case you got curious, please be aware that this article is a spoiler. You might want to do the quiz yourself before continuing.
Although the quiz was created for educational purposes, I was wondering if I could get some interesting figures out of these 28,000 results. And I think I could. However, there are several things to keep in mind when looking at these figures. First, the quiz uses the surprise factor to catch attention. That means, three questions show cases that look fine, but aren't. One question does it the other way around and shows an example that might look dangerous, but isn't. There is only one question where the correct answer is in line with the first impression. Another effect that might affect the significance of the results is that there was no representative selection of participants. Everybody can take the quiz. You can even do it multiple times and will probably get a better result the second time. Just keep in mind that the quiz was never intended to be used for scientific research upon the indexing knowledge in the field. Nevertheless, I think that the size of the dataset is still good enough to get an impression.
Below I'll show two different statistics for each question. First, the average rate at which this question was correctly answered. Second, how this figure varies for users of MySQL, Oracle, PostgreSQL and SQL Server databases. In other word, it says if e.g. MySQL users are more knowledgeable about indexing as PostgreSQL users. Spoiler: It's the other way around. The only reason I'm in the lucky position to have this data is that the test sometimes uses vendor specific syntax. For example, what is LIMIT in MySQL and PostgreSQL is TOP in SQL Server. Therefore, the participants have to select a database at the beginning so that the questions are shown in the native syntax of that product.
Is the following SQL good or bad practice from a performance perspective?Searching for all rows of the year 2012:CREATE INDEX tbl_idx ON tbl (date_column);
SELECT text, date_column
FROM tbl
WHERE TO_CHAR(date_column, 'YYYY') = '2012';
Is the following SQL good or bad practice from a performance perspective?
Searching for all rows of the year 2012:
CREATE INDEX tbl_idx ON tbl (date_column);
SELECT text, date_column
FROM tbl
WHERE TO_CHAR(date_column, 'YYYY') = '2012';
This is an example where the code uses functions specific to the Oracle and PostgreSQL databases. For MySQL, the question uses YEAR(date_column) and for SQL Server datepart(yyyy, date_column). Of course, I could have used EXTRACT(YEAR date_column), but I thought it is better to use the most common syntax.
datepart(yyyy, date_column)
EXTRACT(YEAR date_column)
The participants have these options:
Good practice — There is no major improvement possible.
Bad practice — There is a major improvement possible.
The correct answer is "bad practice" because the index on date_column cannot be used when searching on something derived from date_column. If you don't believe that, please have a look at the proof scripts or the explanations shown at the end of the test. They also contain links to the Use The Index, Luke pages explaining it in more detail.
However, if you didn't know how functions effectively "disable" indexes, you are not alone. Only about two-thirds gave the correct answer. And as for every multiple choice test, there is a certain probably to pick the correct answer by chance. In this cases it's a 50/50 chance — by no means negligible. I've marked this "guessing score" in the figure to emphasize that.
This is one of the most common problems I see in my everyday work. The same problem can also hit you with VARCHAR fields when using UPPER, TRIM or the like. Please keep in mind: whenever you are applying functions on columns in the where clause, an index on the column itself is no longer useful for this query.
Although this result is quite disappointing — I mean it's just 17% better than guessing — it is no surprise to me. What is a surprise for me is how the result differs amongst the users of different databases.
As a matter of fact, MySQL users just score 55% — almost as low as the "guessing score". PostgreSQL users, on the other hand, get a score of 83%.
An effect that might explain this result is that MySQL doesn't support function-based indexes while Oracle and PostgreSQL do. Function-based indexes allow you to index expressions like TO_CHAR(date_column, 'YYYY'). Although it is not the recommended solution for this case, the pure existence of this feature might make users of the Oracle and PostgreSQL database more aware of this problem. SQL Server offers a similar feature: although it cannot index expressions directly, you can create a so-called computed column on expressions, which in turn can be indexed.
TO_CHAR(date_column, 'YYYY')
Although support for function-based indexes might explain why MySQL users underperformed, it is still no excuse. The shown query/index pair is bad — no matter whether the database supports function-based indexes or not. And the major improvement is also possible without function-based indexes:
SELECT text, date_column
FROM tbl
WHERE date_column >= TO_DATE('2012-01-01', 'YYYY-MM-DD')
AND date_column < TO_DATE('2013-01-01', 'YYYY-MM-DD');
The index doesn't need to be changed. This solution is very flexible because it supports queries for different ranges too — e.g. by week or month. This is the recommended solution.
As a curious guy, I'd love to know how many of the people who correctly answered this question were thinking of the sub-optimal solution to use a function-based index. I'd rate this solution half-correct at best.
Is the following SQL good or bad practice from a performance perspective?To find the most recent row:CREATE INDEX tbl_idx ON tbl (a, date_column);
SELECT id, a, date_column
FROM tbl
WHERE a = ?
ORDER BY date_column DESC
To find the most recent row:
CREATE INDEX tbl_idx ON tbl (a, date_column);
SELECT id, a, date_column
FROM tbl
WHERE a = ?
ORDER BY date_column DESC
Note that the question mark is a placeholder, because I always encourage developers to use bind parameters.
As participant, you have these two options again:
This is the question that is supposed to look dangerous, but isn't. Generally, it seems like people believe order by must always sort the data. This index, however, eliminates the need to sort the data entirely so that the query is basically as fast as a unique index lookup. Please find a detailed explanation of this trick here.
The result is very close to the "guessing score" which I interpret as "people don't have a clue about it."
This result is particularly sad because I've seen people building caching tables, regularly refilled by a cron jobs, to avoid queries like this. Interestingly, the cron job tends to cause performance problems because it is running in rather short intervals to make sure the cache table has fresh data. However, the right index is often the better option in the first place.
Here I have to mention that the Oracle database needs the most special syntax for this trick. Up till version 12c released in 2013, the Oracle database did not offer a handy shortcut such as LIMIT or TOP. Instead, you have to use the ROWNUM pseudo-column like this:
SELECT id, date_column
FROM tbl
WHERE a = :a
ORDER BY date_column DESC
WHERE rownum <= 1;
The extra complexity of this query might have pushed Oracle users more heavily towards the wrong answer — actually below guessing score!
Another argument I'm getting in response to this question is that including the ID column in the index would allow an index-only scan. Although this is correct, I don't consider not doing so a "bad practice" because the query touches only one row anyway. An index-only scan could just avoid a single table access. Obviously there are cases were you need that improvement, but in the general case I'd consider it a premature optimization. But that's just my opinion. However, following this argument might give us an idea why PostgreSQL users got the best score (again): PostgreSQL did not have index-only scans until version 9.2, which was released in September 2012. As a result, PostgreSQL users could not fall into this trap of thinking an index-only scan can bring major improvements in this case. Undoubtedly, the term "major" is troublesome in this context.
Is the following SQL good or bad practice from a performance perspective?Two queries, searching by a common column:CREATE INDEX tbl_idx ON tbl (a, b);
SELECT id, a, b
FROM tbl
WHERE a = ?
AND b = ?;
SELECT id, a, b
FROM tbl
WHERE b = ?;
Two queries, searching by a common column:
CREATE INDEX tbl_idx ON tbl (a, b);
SELECT id, a, b
FROM tbl
WHERE a = ?
AND b = ?;
SELECT id, a, b
FROM tbl
WHERE b = ?;
The same options:
The answer is "bad practice," because the second query cannot use the index properly. Changing the index column order to (b, a) would, however, allow both queries to use this index in the most efficient way. You can find a full explanation here. Adding a second index on (b) would be a poor solution due to the overhead it adds for no reason. Unfortunately, I don't know how many would have done that.
(b, a)
The result is disappointing, but in line with my expectations — just 12.5% above guessing score.
This is also a problem I see almost every day. People just don't understand how multi-column indexes work.
All the per-database results are pretty close together. Maybe because there is no syntactic difference or well-known database features that could have a major influence the answer. Less known features like Oracle's SKIP SCAN could have a minor impact, of course. Generally, the index-only scan could have a influence too, but it pushes the participants to the "right" answer this time.
After all, this result might just say that users of some databases know more about indexing than others. Interestingly, PostgreSQL users get the best score for the third time.
Is the following SQL troublesome or bulletproof from a performance perspective?Searching within a string:CREATE INDEX tbl_idx ON tbl (text);
SELECT id, text
FROM tbl
WHERE text LIKE '%TERM%';
Is the following SQL troublesome or bulletproof from a performance perspective?
Searching within a string:
CREATE INDEX tbl_idx ON tbl (text);
SELECT id, text
FROM tbl
WHERE text LIKE '%TERM%';
I've phrased the options differently this time:
Bulletproof: It will always run fast.
Troublesome: There is high risk for performance problems.
And the correct answer is "troublesome" because the LIKE pattern uses a leading wild card. Otherwise, if it would use the pattern 'TERM%', it could use the index very efficiently. Have a look at this visual explanation for details.
The results of this question are promising. Here I feel safe to say most people know that LIKE is not for full-text search.
The segregated results are also within a very narrow corridor:
It is, however, strange that PostgreSQL users under performed at this question. A closer look at how the question is presented to PostgreSQL users might give an explanation:
CREATE INDEX tbl_idx ON tbl (text varchar_pattern_ops);
SELECT id, text
FROM tbl
WHERE text LIKE '%TERM%';
Note the addition to the index (varchar_pattern_ops). In PostgreSQL, this special operator class is required to make the index usable for postfix wild card searches (e.g. 'TERM%'). I added this because I aimed to find out if people know about the problem of leading wild cards in LIKE expressions. Without the operator class, there are two reasons why it doesn't work: (1) the leading wild card; (2) the missing operator class. I though that would be too obvious. Retrospectivley, I believe some participants interpreted this operator class as "magic that makes it work" and thus took the wrong answer.
Question five is a little bit tricky, because PostgreSQL did not support index-only scans when the quiz was created. For that reason, there are two variants of question five: one about index-only scans for users of MySQL, Oracle and SQL Server databases. And another question about index column order for PostgresSQL users. Both results are presented here, but the segregated data is limited for obvious reasons. We start with the question about index-only scans:
How will the change affect query performance?Current situation, selecting about hundred rows out of a million:CREATE INDEX tab_idx ON tbl (a, date_column);
SELECT date_column, count(*)
FROM tbl
WHERE a = 123
GROUP BY date_column;Changed query, selecting about ten rows out of a million:SELECT date_column, count(*)
FROM tbl
WHERE a = 123
AND b = 42
GROUP BY date_column;
How will the change affect query performance?
Current situation, selecting about hundred rows out of a million:
CREATE INDEX tab_idx ON tbl (a, date_column);
SELECT date_column, count(*)
FROM tbl
WHERE a = 123
GROUP BY date_column;
Changed query, selecting about ten rows out of a million:
SELECT date_column, count(*)
FROM tbl
WHERE a = 123
AND b = 42
GROUP BY date_column;
Note the added where clause in the second query.
This question is also special because it offers four options:
Query performance will roughly stay the same (+/- 10%)
Depends on the data.
The query will be much slower (impact >10%)
The query will be much faster (impact >10%)
When I created the quiz, I was well aware that 50/50 questions have a tendency to render the score meaningless. This question is a trade-off between keeping the questions easy to gasp and answer (questions 1–4) and giving a more accurate result.
To make it short, the correct answer is "the query will be much slower." The reason is that the original query could use an index-only scan — that is, the query could be answered only using data from the index without fetching any data from the actual table. The second query, however, needs to check column B too, which is not in the index. Consequently, the database must take some extra effort to fetch the candidate rows from the table to evaluate the expression on B That is, it must fetch at least 100 rows from the table — the number of rows returned by the first query. Due to the group by there are probably more rows to fetch. A quite considerable extra effort that will make the query much slower. A more exhaustive explanation is here.
With that number of options, the overall score drops significantly to about 39% or 14% above guessing score.
Still I think saying that about 39% of participants knew the right answers is wrong. They gave the right answer, but there was still a probability of 25% that they gave the right answer without knowing it.
The segregation by database is quite boring. Conspicuously boring.
However, with four options, it is also interesting to see how people actually answered.
That caught me by surprise. Both options "roughly the same" and "depends on the data" got about 25% — the guessing probably. Does this mean half of the participants were guessing? As it is the last question some participants might have picked a random option just to get through. Quite possible. However, the correct option "much slower" got 38.8% at the cost of the "much faster" option, which got just 10.9%.
My intention with this question was to trap people into the "much faster" option because fetching less data should be faster — except when breaking an index-only scan. The only hypothesis I have for this result is that people might have got the idea that the obvious answer isn't the correct one. That, however, would mean the 39% score doesn't prove anything about the knowledge of this phenomenon in the field.
Another effect that I expected to have an impact is that "it is always depending on the data." Of course there are edge cases where the performance impact might roughly stay the same — e.g., when all inspected rows are in the same table block. However, this is rather unlikely — just because there would be no point in adding the date_column for an index-only scan in the first place.
This question is only shown to PostgreSQL users.
Is the following SQL good or bad practice from a performance perspective?Searching for entries in state X, not older than 5 years.CREATE INDEX tbl_idx ON tbl (date_column, state);
SELECT id, date_column, state
FROM tbl
WHERE date_column >= CURRENT_DATE - INTERVAL '5' YEAR
AND state = 'X';
(365 rows)The data distribution is as follows:SELECT count(*)
FROM tbl
WHERE date_column >= CURRENT_DATE - INTERVAL '5' YEAR;
SELECT count(*)
FROM tbl
WHERE state = 'X';
Searching for entries in state X, not older than 5 years.
CREATE INDEX tbl_idx ON tbl (date_column, state);
SELECT id, date_column, state
FROM tbl
WHERE date_column >= CURRENT_DATE - INTERVAL '5' YEAR
AND state = 'X';
(365 rows)
The data distribution is as follows:
SELECT count(*)
FROM tbl
WHERE date_column >= CURRENT_DATE - INTERVAL '5' YEAR;
SELECT count(*)
FROM tbl
WHERE state = 'X';
There is an index with two columns and a query that filters on both of them. One filter uses an equals operator, the other a greater than or equal operator. When using each filter individually the query returns many more rows as when combining both filters.
The options are:
And the correct answer is "bad practice" because the column order in the index is the wrong way around. The general rule is that index columns can be used efficiently from the left hand side as long as they are used with equals operators. Further, one column can be used efficiently with a range operator. However, the first range operator effectively cuts off the index so that further columns cannot be used efficiently anymore. With efficiently I mean as an index access predicate. Please find a visualization here.
With the original index shown above, the query has to fetch 1826 entries from the index (those matching the date_column filter) and check each of them for the value of the state column. If we turn the index column order around, the database can use both filters efficiently (= as access predicate) and directly limit the index access to those 365 rows of interest.
And this is how people answered:
Wait a moment, that's below the guessing score! It's not just people don't know, they believe the wrong thing. However, I must admit that the term "major" is very problematic again. When I run this example, the speed-up I get is just 70%. Not even twice as fast.
Looking at each question individually is interesting, but doesn't tell us how many participants managed to answer all five questions correctly, for example. The following chart has that information.
Finally, I'd like to boil this chart down to a single figure: how many participants "passed" the test?
Considering that the test has only five questions, out of which four are 50/50 questions, I think it is fair to say three correct answers isn't enough to pass the test. Requiring five correct answers would quite obviously be asking for too much. Requiring four correct answers to "pass" the test is therefore the only sensible choice I see. Using this definition, only 38.2% passed the test. The chance to pass the test by guessing is still 12.5%.
If you like this article and want to learn about proper indexing, SQL Performance Explained is for you.
Raw results (.txt.gz, 1.1 MB, explanations included)
To verify my analysis and/or draw your own conclusions. Also includes data that I haven't used. Especially timestamps that might allow to filter for guessing (e.g. question answered in less than X seconds).
Indexes: The neglected performance all-rounder
Analysis why developers know very little about SQL indexing.
Expert, competent, or just a greenhorn? Test your SQL performance skills in just three minutes. ›
by Markus Winand.
Visit our sister site!
A lot has changed since SQL-92


text:
Itâs no secret that âUncanny X-Menâ is currently treading water before its 500th issue, which promises to bring the âsplit upâ team back together and get their new direction well and truly on track. In the meantime, Brubaker has shifted gear after "Messiah Complex" to tell a rather more sedate story with a lot of old school charm, as Emma and Cyclops investigate strange goings-on in San Francisco and Wolverine, Cyclops and Colossus continue their journey across Russia, where things have taken a more problematic turn.
Despite the suggestion that the series is in something of a holding pattern until #500, itâs probably the worst-kept secret in the X-verse that the team will be relocating to San Francisco in the near future, so thereâs almost certainly more going on here than meets the eye. Whether the Russia plot threads will tie into it is debatable, but with a writer as capable as Brubaker I wouldnât be surprised if they did - though equally, the idea of a standalone, road trip story for these three characters is good enough fun in its own right to justify its existence. Depicting down time is a true X-Men tradition, and Brubaker has really hit his stride with it.
The identity of the mutant causing the city to regress to the 60s is fairly vague -- Iâm a fairly big X-Men buff and I donât immediately recognize who the character is supposed to be, so in the absence of any other obvious candidates we have to fall back on the two most obvious reality/perception-warpers around - Scarlet Witch, and Lady Mastermind. The former is âAvengersâ property as far as the character goes, whereas the latter was heavily involved in "Messiah Complex," so Iâm fairly certain which itâll be. Even so, I canât be sure that Brubaker wonât pull the rug out from under the title somehow.
Speaking of Brubaker, this storyline is the first heâs done where I feel like Iâm really enjoying the situation. After being fairly underwhelmed by âRise and Fall of the Shiâarâ, Iâm finding a lot to like about his current approach of playing off the X-Men where the more traditional X-Men characters have taken center stage again. Itâs been a long time since they got any traditional superheroics to do, but with the school gone, Brubaker can really focus on that side of the concept again.
Choiâs artwork is a welcome change from the recent, grittier styles that âUncanny X-Menâ has seen, and the bright, fun colors fit the tone of the book entirely -- the idea of a city regressing to the 60s is a fairly camp concept in itself, and Choiâs artwork nicely fits that tone, with masses of attention on the small details. Heâs also good for the grander strokes, though -- the opening sequence where Wolverine, Nightcrawler and Colossus fight some giant robots is a great piece of action-adventure, brilliantly choreographed and nicely executed. âDivided We Standâ does feel like something of a stop-gap solution, but if this is what we get as âfillerâ, I canât wait to see how good the bookâs going to be in the future.


text:
Raymond "Randy" Freeman
Raymond "Randy" Freeman Bio-Sketch President – S&PP Consulting Raymond "Randy" Freeman is Principal Engineer and founder of Safety & Property Protection Consulting of Houston, Texas. He has more than 30 years of industrial experience in reliability, risk analysis, safety, process design, control system design, and risk management in the chemical industry. His work has involved the specification, design and management of highly hazardous process systems. He received his Bachelor of Science degree, his Master of Science degree, and his Doctor of Philosophy degree from the University of Missouri – Rolla, all in Chemical Engineering. Dr. Freeman is a Fellow of the American Institute of Chemical Engineers and was elected to the University of Missouri Academy of Chemical Engineering in 1998. He is an Emeritus Member of the Center for Chemical Process Safety and was elected a CCPS Fellow in 2007. He is a registered professional engineer in Missouri and Texas.


text:
Life Notes From The Prayer Warrior
It's been quite a year for the Prayer Warrior.
In May he alerted his followers to a medical problem that sounded like it involved bleeding eyes; in July he injured his chest on a propane tank and not long afterward reported six cracks in five ribs; later that month, he claimed to have been the victim of identity theft; in early August, threats against his life required prayers for a "protective hedge" around his house; in late August, he made a vague reference to his blood counts being down; and today he sends word that he's heading back to the doctor for another blood test.
Please pray for me as I go for another blood test today. First of all, that my numbers remain down, and second, that a vein will be easily found. It is usually quite an ordeal to find a vein, with multiple tries!!
Your Pastor,
Any doctors or medical students out there want to take a stab at a diagnosis?