Hi! After experiencing a lot of slowdown on a website with about 8000 nodes, I've discovered a small bottleneck when Page Title is used with views.

The page_title table uses the couple (TYPE,ID) as primary key. When you add the page title field on a view, the query is build with only a LEFT JOIN to the id field.

There are two solution:
1) change the JOIN in order to use the couple TYPE,ID
2) (quick & dirty) add an index to the ID field :-)

Thanks for the attention.

Comments

dwightaspinwall’s picture

dwightaspinwall commented
Priority: Normal » Major

This is a critical issue. The view produces the following SQL:

SELECT node.nid AS nid,
node.title AS node_title,
page_title.page_title AS page_title_page_title,
node.uid AS node_uid,
node.type AS node_type,
node_revisions.format AS node_revisions_format
FROM node node
LEFT JOIN page_title page_title ON node.nid = page_title.id
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
ORDER BY nid ASC
LIMIT 0, 10

The ORDER BY clause causes a temp table and file sort to take place, which is tragic for our site with over 1,000,000 nodes. Looking into why the order by is there and how to get rid of it.

nicholasthompson’s picture

nicholasthompson
Primary language English
commented

Just out of interest, what is the use case where you need to select all 1,000,000 nodes with their page title?

dwightaspinwall’s picture

dwightaspinwall commented

Your module provides a view, 'list_page_titles', which shows the nodes with page titles. You presumably had at least one use case in mind or you wouldn't have added that view. The view is enabled by default. I granted "administer page titles" to an SEO consultant and he ran the report at admin/reports/page-title and the query ran for six minutes.

It's not that anyone wants a list of 1,000,000 nodes. But it's natural to want to browse the nodes with page titles, right? And if so, the module should support large sites.

I do not yet understand why views is throwing the ORDER BY on the query. Removing it solves the performance issue. In the meantime, I've pulled "administer page titles" from all our roles.

Thanks for the module btw.

nicholasthompson’s picture

nicholasthompson
Primary language English
commented

Right I see. I (for some reason) assumed this was a frontend (public user) issue rather than the admin report issue.

The ORDER BY is probably being added by the table sorting in the default view:
http://drupalcode.org/project/page_title.git/blob/a9a5d31d9357fa13603b3c...

NID is the default sort column.

dwightaspinwall’s picture

dwightaspinwall commented

Ah - yes I see. Well, it's natural to want to sort stuff, and for modest sized sites this isn't an issue. Not clear what the best solution is here, as it's pretty heavy to add all the required indexes, and they may not help anyway. Maybe disable the view by default, or remove the sorting options and let site builders add their own if they want, with knowledge of potential implications.

finex’s picture

finex commented

I've experienced this problem on the front-end, what @dwightaspinwall reported is a different use case.

I don't think the problem is the default page_title administration view but the wrong Join, it should be done on the full primary key.

Moreover it doesn't matter if you select only a small part of the full data with a LIMIT because Join on not indexed columns scans all the records.

If you want to do a test try this:

  1. create 10000 nodes;
  2. create a view which loads the page title field;
  3. EXPLAIN the query generated by views (or run a query plan analysis if you use MSSql) and you will see what's happening.

As I've wrote on my initial report we have two simple solutions:

  • the first is to extend the join to the full primary key, file "page_title.views.inc", function page_title_views_data(), the Join is defined only between nid and id. I don't fully know the views API, but I hope it would be possible to set the Join to the couple (type,id).
  • otherwise simply add a second index to the "id" column. It isn't the best solution but it works, usually there are more read than write on that table so performance problem related to an additional index should be not considered.

Thanks again :-)