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 :
SELECT node.nid,
content_type_presentation.field_datum_value,
content_type_presentation.field_location_value,
content_type_presentation.field_country_value ,
content_type_presentation.field_event_link_title,
content_type_presentation.field_event_link_url
FRON content_type_presentation, node
WHERE node.type="presentation" AND node.nid = content_type_presentation.nid
ORDER BY content_type_presentation.field_datum_value desc;
However when using Views , this is what is being created.
SELECT DISTINCT(node.nid) AS nid,
node_data_field_country.field_datum_value AS node_data_field_country_field_datum_value,
node_data_field_country.nid AS node_data_field_country_nid,
node.type AS node_type,
node_data_field_country.field_event_link_url AS node_data_field_country_field_event_link_url,
node_data_field_country.field_event_link_title AS node_data_field_country_field_event_link_title,
node_data_field_country.field_event_link_attributes AS node_data_field_country_field_event_link_attributes,
node_data_field_country.field_location_value AS node_data_field_country_field_location_value,
node_data_field_country.field_country_value AS node_data_field_country_field_country_value,
node_data_field_link.field_link_url AS node_data_field_link_field_link_url,
node_data_field_link.field_link_title AS node_data_field_link_field_link_title,
node_data_field_link.field_link_attributes AS node_data_field_link_field_link_attributes,
node_data_field_link.nid AS node_data_field_link_nid
FROM node node
LEFT JOIN content_type_presentation node_data_field_country ON node.vid = node_data_field_country.vid
LEFT JOIN content_field_link node_data_field_link ON node.vid = node_data_field_link.vid
WHERE UPPER(node_data_field_country.field_country_value) != UPPER('')
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
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | node_data_field_country | ALL | PRIMARY | NULL | NULL | NULL | 33 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | node | eq_ref |vid | vid | 4 |buytaert_blog.node_data_field_country.vid | 1 | |
| 1 | SIMPLE | node_data_field_link | eq_ref | PRIMARY | PRIMARY | 4 | buytaert_blog.node.vid | 1 | |
+----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------------------------+------+----------------------------------------------+
Now my simpler query uses the same amount of rows to return the result but doesn't build a temporary table.
+----+-------------+---------------------------+------+-----------------------+------+---------+---------------------------------------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------+------+-----------------------+------+---------+---------------------------------------------+------+----------------+
| 1 | SIMPLE | content_type_presentation | ALL | nid | NULL | NULL | NULL | 33 | Using filesort |
| 1 | SIMPLE | node | ref | PRIMARY,node_type,nid | nid | 4 | buytaert_blog.content_type_presentation.nid | 1 | Using where |
+----+-------------+---------------------------+------+-----------------------+------+---------+---------------------------------------------+------+----------------+
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.
