Starting MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause.
In MySQL 5.0 support for views was added.

These features are quite related to each other but how do they compare in terms of performance ?

Derived Tables in MySQL 5.0 seems to have different implementation from views, even though I would expect code base to be merged as it is quite the same task in terms of query optimization.

Derived Tables are still handled by materializing them in the temporary table, furthermore temporary table with no indexes (so you really do not want to join two derived tables for example).

One more thing to watch for is the fact derived table is going to be materialized even to execute EXPLAIN statement. So if you have done mistake in select in from clause, ie forgotten join condition you might have EXPLAIN running forever.

Views on other hand …

If you will try to compile nginx in Redhat Linux, you can get following error even if you have pcre-devel package installed:

Configuration summary + threads are not used + PCRE library is not found .... .... ./configure: error: the HTTP rewrite module requires the PCRE library. You can either disable the module by using --without-http_rewrite_module option, or install the PCRE library into the system, or build the PCRE library statically from the source with nginx by using --with-pcre= option.

If you are experiencing this small problem, you should simply use following parameter of configure script:

# ./configure --with-cc-opt="-I /usr/include/pcre"

And voila!

+ using system PCRE library

Now it can see and will use system PCRE library.

While the MySQL Server source trees are maintained using the BitKeeper revision control system, several other MySQL projects (Connectors, GUI-Tools and the Manual) use Subversion instead.

To make it easier for external developers in getting familiar with the code base of the respective project, we now installed the FishEye SVN repository browser, which provides a very nice interface to the hosted repositories and boasts an impressive number of additional features like searching, diffing and RSS feeds. …

What is it about the folks on Planet MySQL having twin brothers?

Roland Bouman
Sheeri Kritzer
Jay Pipes
Zach Urlocker

That’s 4 of the top 25 posters to Planet MySQL. Anyone else want to reveal having a twin? Anyone on here have a twin sister? If you’re a twin and aren’t on the Planet, note that here too……

database, mysql, positive technology

My desktop at work is a Windows machine. Why? Because it gives me what I need — shell access to servers so I can do real work on the machines, a text editor, an e-mail client and a web browser. That’s really all I need to do my job. Sure, I could put in for a Macintosh or install a Unix variant. But if it gives me what I want, why would I spend all that work changing things around, just to ultimately get the same requirements — shell, web browser, text editor, e-mail client….????

I love MySQL, it’s a great database. But in order to meet its tenets, it has sacrificed features. When database religious wars …

This article is part of “Looking For Optimal Solution” series, devoted to testing various Ruby On Rails deployment schemes and doing some simple benchmarks on these schemes. General idea of testing is to find subset of most optimal RoR deployment schemes for different situations.

This small article is about Rails+Mongrel setup and its performance. List of other tested deployment schemes, description of testing methodology and, of course, all benchmark results you can find on “Ruby On Rails Benchmark Summary and Findings” page.

(more…)

Ilan Rabinovitch let me know that the SCALE team is getting started on version 5x of the SoCal Linux Expo.

In past years, SCALE has been a great community event - the ratio of promoters to real Linux enthusiasts is low and the attendees are friendly. Also, like most other Linux conferences, attendees have a strong interest in many other FLOSS community issues and technologies, like BSD, Firefox, Apache, PHP, MySQL, Free Software licensing and so on. Hopefully I can attend this year (and can wear both my eZ hat and my Mozilla hat for the event).

The event will happen from February 10-11 and will be held at the Westin Los Angeles Airport hotel.

Get more details at: …

From Webworkshops...

Scaling MySQL

• 
  
• Indexing
• Joins are bad (normalised data is for sissies)
• Example: searching
• Hardware (memory, CPU, IO, disc cache, RAID, direct attached)
• Replication (simple, master / master, trees)
• Specialist slave
• Partitioning
  

But that doesn't mean I wear a skirt.

Tonight I took a trip to the Meetup Organizer Meeting. The group is for Meetup Organizers to sit and talk about what they are doing to promote their groups.
Since the PR VP from Meetup was attending, and offering free pizza, I thought I would attend.

I went not expecting much. At best I was hoping to run into other people in Seattle who run technology Meetups, and at worst I thought I would be sitting around a table with five other group organizers listening to someone complain about how no one ever attended their meetings. I had already contacted someone at Meetup.com to see if there would be anyone there who worked on the site, and had found out that this would be non-geek sort of event. (The site is powered by the MySQL database server, and I always enjoy hearing how people make use of MySQL).

What I did not expect to find was a room filled with 50 people who were passionate about their groups. The crowd was a …

I get stuck on the dumbest shit ever.

I have master-master MySQL 5.0 servers. I have a nightly snapshot of the "slave" database, and I need to finish hacking in an automagic method of storing binlogs along with the snapshot. I, for the life of me, cannot decide how to do this. I've been inching for hours.

Do I want to start up 10 tiny mysql instances with our entire schema mirrored using the "blackhole" engine? How would I figure out what position in the binlog correlates with the snapshot? (not too hard I guess). How the hell would I keep that all in sync as developers randomly add/remove tables?

From what *angle* do I grab the damn files? How does my normal binlog rotator manage to communicate with the binlog slurper what files it's already slurped? YAML? A special database on each database? A file in the snapshot directory? Why do I care? :P Why have I written so many of these scripts for so many …