Everything is a Freaking DNS problem - cck http://127.0.0.1:8080/blog/taxonomy/term/1121/0 en CCK & Views, My First Experiences http://127.0.0.1:8080/blog/cck-views-my-first-experiences <p>and probably my last one .. </p> <p>I <a href="http://www.krisbuytaert.be/blog/migrate-multisite-and-restyle">mentioned </a> 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 <a href="http://www.inuits.be/">another site</a> for everybody to use.</p> <p>Some people suggested that I'd have a look at CCK &amp; 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.</p> <p>Now I must admit that <a href="http://www.krisbuytaert.be/pics/hybernate.jpg">I`m not really fond</a> of "generated queries"</p> <p>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.<br /> So I was a bit afraid of what Views would do ..</p> <p>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</p> <p>Now if I'd create a special content type for this I probably get some more benefits.<br /> And I could get the data from the tables with a rather simple query like this :<br /> <div class="geshifilter"><pre class="text geshifilter-text" style="font-family:monospace;"><ol><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">SELECT node.nid,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> content_type_presentation.field_datum_value,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> content_type_presentation.field_location_value,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> content_type_presentation.field_country_value ,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> content_type_presentation.field_event_link_title,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> content_type_presentation.field_event_link_url</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> FRON content_type_presentation, node</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> WHERE node.type=&quot;presentation&quot; AND node.nid = content_type_presentation.nid</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> ORDER BY content_type_presentation.field_datum_value desc;</div></li></ol></pre></div></p> <p>However when using Views , this is what is being created. </p> <p><div class="geshifilter"><pre class="text geshifilter-text" style="font-family:monospace;"><ol><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">SELECT DISTINCT(node.nid) AS nid,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node_data_field_country.field_datum_value AS node_data_field_country_field_datum_value,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node_data_field_country.nid AS node_data_field_country_nid,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node.type AS node_type,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node_data_field_country.field_event_link_url AS node_data_field_country_field_event_link_url,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node_data_field_country.field_event_link_title AS node_data_field_country_field_event_link_title,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node_data_field_country.field_event_link_attributes AS node_data_field_country_field_event_link_attributes,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node_data_field_country.field_location_value AS node_data_field_country_field_location_value,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node_data_field_country.field_country_value AS node_data_field_country_field_country_value,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node_data_field_link.field_link_url AS node_data_field_link_field_link_url,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node_data_field_link.field_link_title AS node_data_field_link_field_link_title,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node_data_field_link.field_link_attributes AS node_data_field_link_field_link_attributes,</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> node_data_field_link.nid AS node_data_field_link_nid</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> FROM node node</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> LEFT JOIN content_type_presentation node_data_field_country ON node.vid = node_data_field_country.vid</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> LEFT JOIN content_field_link node_data_field_link ON node.vid = node_data_field_link.vid</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> WHERE UPPER(node_data_field_country.field_country_value) != UPPER('')</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal"> ORDER BY node_data_field_country_field_datum_value DESC</div></li></ol></pre></div></p> <p>So there's some joins, a zillion more fields that I don't really need being fetched from my database.<br /> The explain also tells me some interesting things.. like the Using temporary AND Using Filesort</p> <p><div class="geshifilter"><pre class="text geshifilter-text" style="font-family:monospace;"><ol><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">+----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------------------------+------+----------------------------------------------+</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">| 1 | SIMPLE | node_data_field_country | ALL | PRIMARY | NULL | NULL | NULL | 33 | Using where; Using temporary; Using filesort |</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">| 1 | SIMPLE | node | eq_ref |vid | vid | 4 |buytaert_blog.node_data_field_country.vid | 1 | |</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">| 1 | SIMPLE | node_data_field_link | eq_ref | PRIMARY | PRIMARY | 4 | buytaert_blog.node.vid | 1 | |</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">+----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------------------------+------+----------------------------------------------+</div></li></ol></pre></div></p> <p>Now my simpler query uses the same amount of rows to return the result but doesn't build a temporary table.<br /> <div class="geshifilter"><pre class="text geshifilter-text" style="font-family:monospace;"><ol><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">+----+-------------+---------------------------+------+-----------------------+------+---------+---------------------------------------------+------+----------------+</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">+----+-------------+---------------------------+------+-----------------------+------+---------+---------------------------------------------+------+----------------+</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">| 1 | SIMPLE | content_type_presentation | ALL | nid | NULL | NULL | NULL | 33 | Using filesort |</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">| 1 | SIMPLE | node | ref | PRIMARY,node_type,nid | nid | 4 | buytaert_blog.content_type_presentation.nid | 1 | Using where |</div></li><li style="font-family: monospace; font-weight: normal;"><div style="font-family: monospace; font-weight: normal; font-style: normal">+----+-------------+---------------------------+------+-----------------------+------+---------+---------------------------------------------+------+----------------+</div></li></ol></pre></div></p> <p>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.</p> <p>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 :)</p> <p>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.</p> <p>But the short summary ... given my background I prefer writing the module and crafting my own queries over having Views create<br /> a query for me.</p> http://127.0.0.1:8080/blog/cck-views-my-first-experiences#comments cck drupal mysql views Sun, 07 Dec 2008 21:44:10 +0000 Kris Buytaert 838 at http://127.0.0.1:8080/blog