drupal

Dec 07 2008

CCK & Views, My First Experiences

and probably my last one ..

I mentioned a couple of weeks ago that I was looking into a way of converting my static page with published papers, articles and presentations in a more dynamic page where I could create RSS feeds from the updates and feed them into another site for everybody to use.

Some people suggested that I'd have a look at CCK & Views for this. While up till now when creating an app I had usually written my own Drupal module with my own database schema and manually written SQL Queries. This indeed looked like the perfect opportunity to dig into the CCK and Views thingie.

Now I must admit that I`m not really fond of "generated queries"

I've had nightmares before when having to debug 2Mb Hibernate queries that could be reduced to a single select value from table where query but Hibernate used a zillion join on tables I didn't need.
So I was a bit afraid of what Views would do ..

So here's what happened, I created 2 pages .. presentations. and publications. Personally I'd go for 1 table each with some smart indexes and then the only thing you want is SELECT * FROM table ORDER BY date

Now if I'd create a special content type for this I probably get some more benefits.
And I could get the data from the tables with a rather simple query like this :

  1. SELECT node.nid,
  2. content_type_presentation.field_datum_value,
  3. content_type_presentation.field_location_value,
  4. content_type_presentation.field_country_value ,
  5. content_type_presentation.field_event_link_title,
  6. content_type_presentation.field_event_link_url
  7. FRON content_type_presentation, node
  8. WHERE node.type="presentation" AND node.nid = content_type_presentation.nid
  9. ORDER BY content_type_presentation.field_datum_value desc;

However when using Views , this is what is being created.

  1. SELECT DISTINCT(node.nid) AS nid,
  2. node_data_field_country.field_datum_value AS node_data_field_country_field_datum_value,
  3. node_data_field_country.nid AS node_data_field_country_nid,
  4. node.type AS node_type,
  5. node_data_field_country.field_event_link_url AS node_data_field_country_field_event_link_url,
  6. node_data_field_country.field_event_link_title AS node_data_field_country_field_event_link_title,
  7. node_data_field_country.field_event_link_attributes AS node_data_field_country_field_event_link_attributes,
  8. node_data_field_country.field_location_value AS node_data_field_country_field_location_value,
  9. node_data_field_country.field_country_value AS node_data_field_country_field_country_value,
  10. node_data_field_link.field_link_url AS node_data_field_link_field_link_url,
  11. node_data_field_link.field_link_title AS node_data_field_link_field_link_title,
  12. node_data_field_link.field_link_attributes AS node_data_field_link_field_link_attributes,
  13. node_data_field_link.nid AS node_data_field_link_nid
  14. FROM node node
  15. LEFT JOIN content_type_presentation node_data_field_country ON node.vid = node_data_field_country.vid
  16. LEFT JOIN content_field_link node_data_field_link ON node.vid = node_data_field_link.vid
  17. WHERE UPPER(node_data_field_country.field_country_value) != UPPER('')
  18. ORDER BY node_data_field_country_field_datum_value DESC

So there's some joins, a zillion more fields that I don't really need being fetched from my database.
The explain also tells me some interesting things.. like the Using temporary AND Using Filesort

  1. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  2. +----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------------------------+------+----------------------------------------------+
  3. | 1 | SIMPLE | node_data_field_country | ALL | PRIMARY | NULL | NULL | NULL | 33 | Using where; Using temporary; Using filesort |
  4. | 1 | SIMPLE | node | eq_ref |vid | vid | 4 |buytaert_blog.node_data_field_country.vid | 1 | |
  5. | 1 | SIMPLE | node_data_field_link | eq_ref | PRIMARY | PRIMARY | 4 | buytaert_blog.node.vid | 1 | |
  6. +----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------------------------+------+----------------------------------------------+

Now my simpler query uses the same amount of rows to return the result but doesn't build a temporary table.

  1. +----+-------------+---------------------------+------+-----------------------+------+---------+---------------------------------------------+------+----------------+
  2. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  3. +----+-------------+---------------------------+------+-----------------------+------+---------+---------------------------------------------+------+----------------+
  4. | 1 | SIMPLE | content_type_presentation | ALL | nid | NULL | NULL | NULL | 33 | Using filesort |
  5. | 1 | SIMPLE | node | ref | PRIMARY,node_type,nid | nid | 4 | buytaert_blog.content_type_presentation.nid | 1 | Using where |
  6. +----+-------------+---------------------------+------+-----------------------+------+---------+---------------------------------------------+------+----------------+

In this small example there isn't much difference in performance to be noticed, but with bigger tables you will see other results. However I still prefer my personal query approach.

The other thing that bugs me is the excessive use of aliases. The MySQL internal experts can shed a light on the performance impact of using them, but if you are running queries remotely you at least have an increase in bandwidth usage. Not a problem now, but who knows later when you are big and famous :)

Now Drupal seems to do it pretty ok .. it's aliasing table.field to table_field which still makes sense. Not unlike some other tools that alias table.field to dynamically_generated_unreproducable_unreadable_key_word_with_no_relevant_meaning , try debugging that for a change. And yes I see valuable use for resultset aliasing, but I also se it is often overused.

But the short summary ... given my background I prefer writing the module and crafting my own queries over having Views create
a query for me.

Dec 06 2008

Wordpress is Slow !

because of a fine DNS problem

So it can obviously happen to other platforms as well :)

Dec 01 2008

Tab Dump , Drupal Performance

Have to close some tabs .. what more can I say ..

2 interresting articles about Drupal and MySQL performance tuning

here and here

Enjoy

Nov 18 2008

openQRM.com

Last weekend I blogged about openQRM 4.2 being released.

This morning Matt finally let me know the long waiting fresh and new Drupal based openQRM.com is live !

Feed Added!

Nov 14 2008

Migrate, MultiSite and Restyle

Done

Some of my lower traffic sites were still on an old Drupal 5, others were at 6.4 and lots of modules were outdated. So I set out to migrate all the sites to a multisite setup. I now have a set of shared modules , shared themes and some specific ones .. just as it is meant to be. And I only have 1 central Drupal instance to upgrade. Next step is figuring out how to trigger the database upgrades from 1 central place.

Well 2 , ny personal stuff and our corporate sites, that's the idea. Personal site is on plain old Drupal 6.6 and for the corporate one we are thinking to go with Acquia Drupal, but we're not sure yet

I've added a new theme, however I can imagine most of you never saw the old one as most of you read this feed in an RSS readers, yet some people nagged me about my lack of style, and as over a decade ago I even won web design contests , something had to be done :)

I moved some blocks left and right and the performance of the site has improved drastically now, mainly slow technorati loading widgets in the left column often blocked loading, that's all gone now.

Also , as some of the commenters on earlier posts complained the main page has no CSS as it isn't in the CMS. So my next step is trying to find out a way that I can replace the now manually maintained files build from different includes into a more dynamical setup where I can also have feeds for my Publications and Presentations.

I`m wondering if there is any microformat or so out there for Presentations and Publications that I could use so other people could use that information in other sites.

After that there's just one site left to migrate.. but that migration won't be for next month as there is some custom modules involved that need to be ported.

Well.. off to some more content then :)

Nov 02 2008

Everything is a Funky DNS Problem

Tom dropped me a mail today letting me know that all the links to my RSS feed were broken.
I tested, and tested again and failed to reproduce the problem.

The only things I had changed recently where the administrator menu module and Pathauto module, apart from that I had not made any changes. So I tried uninstalling the modules to see if that helped, it didn't. There was no difference between the 2 setups.

When I replied to Tom that I couldn't reproduce the problemm he told me he only had the problem on http://krisbuytaert.be/ and not on http://www.krisbuytaert.be/

So I verified the apache vhost config and it turned out that the problem, as usual, was a frigging DNS problem.
Both Apache vhost config files had a different setup, so depending on how you arrived at this site you got a different config.

Annoying .. but I guess I owe Tom a beer for figuring this out and pointing me to my own DNS problem.

Oct 27 2008

T-Dose 2008 is over

It was fun, the good part about T-Dose is that is small enough to actually be able to speak with everybody you want to .. well almost .. there were still some people around I wanted to talk to but I didn't get the chance to . Specially Ber Kessels and Roy Scholten who filled in the gaps in the Drupal track. After my own talk I had to run to the other track so I could answer the tricky questions in our other talk about Open Source Monitoring Tools. And I never really made it back to the Drupal room. So Ber, Roy , next time you run into me I`ll buy you a Beer !

Anyway Pics are up (so Geert now finally has pictures of himself on stage)

Social event pic is also up ..

Slides (Drupal/ Virtualization) were already up

Somehow I had problems seeing al the sessions I wanted to see this year. lots of interesting things happening at the same time and therefore forcing me to choose for specific sessions. JP and Jeroen already announced they will be there again next year .. I just hope to have a better planned Drupal track then ...

T-Dose kind of concluded my current scheduled list of talks , I`m looking for new interesting conference venues to visit .. specially in southern Europe .. so if anybody has ideas :)

Sep 24 2008

The new comment spam :(

The past couple of weeks my blog is being annoyed by different comment spams, the content of the comment seems perfectly relevant, often even correctly tackling the topic, or sometimes even almost insightful follow up questions.

However the link to the author site is totally bogus.

If talking about drupal and high availability , a question on GFS or OCFS2 is obviously on topic. However I don't want my blog to link to lot of irrelevant sites.

On the other hand I can't tell Mollom the content of the article is crap. I do want to tell Mollom the "author" of the comment is crap. I even might want to have the comment visible but not link back to the author.

Anyone got good ideas on that ?

Sep 10 2008

Zenoss Core Network and System Monitoring , the Book

Harish Kumaresh from Packt wrote me last week to ask if I wanted to review their Zenoss Core book

The package arrived today ..

So I`ll try to free up some time asap to actually read it :)

PS. Yeah I know, the pictures are is so "Dries", but I like the concept :)

Sep 02 2008

MySQL HA And Drupal

Lenz just posted pointers to a great presentation of his about MySQL HA and Drupal,

Seems like a small part of me did go to Szeged afterall :)