Custom breadcrumbs makes use of the mysql mediumtext column type on the 'visibility_php' column, to store the php visibility settings. This is the case for all three custom_breadcrumb database tables that I have in my database (custom_breadcrumbs, custom_breadcrumbs_views and custom_breadcrumbs_paths). The column is included in every single lookup on those tables. That can mean 3 lookups per page.

Each of these lookups is unable to use MySQL's in memory table cache due to the inclusion of a TEXT field, and so temporary tables must be created on disk for each of these lookups. This results in a considerable performance issue - on every page. On disk temporary tables can have a massive effect on performance. I think that the general practice is to either not use text or blob columns, or move the TEXT/Blob data to a separate table, which allows you to do your lookup on the main table, utilising indexes and in memory temporary tables, and then join the blob data only if it's needed.

Please consider either changing these columns to varchar (perhaps this will be limiting on the amount of php that can used), making the use of php visibility settings an optional extra (I have no need for it) and/or moving the TEXT columns to a database table of their own.


MGN’s picture

Category: bug » task

This is interesting, and worth looking into. If anyone would like to benchmark alternative table structures and demonstrate a significantly improved performance, it might help identify an different approach. I've run some standard benchmarks (using a mix of about 40 custom breadcrumbs in a standard testing environment) to see how much of a performance hit there is with each additional custom breadcrumbs sub module, but I haven't found anything significant yet. So I wouldn't expect the difference between mediumtext and varchar to be that significant either. But I am ready to see the benchmark data that says otherwise.

Marking this as a task for anyone who is interested to propose a specific alternative supported by benchmark data.

ManyNancy’s picture


xtfer’s picture


rup3rt’s picture


locomo’s picture


hedac’s picture

subscribing.. Maybe is not the same issue but I have found hundreds of queries like this SELECT * FROM custom_breadcrumbs_paths WHERE specific_path =
is it normal?

rjivan’s picture

Same here. I see over 700 calls to SELECT * FROM custom_breadcrumbs_paths WHERE specific_path = 'xxxxx' on every page. Can't the results of this query be cache using the $static variable and reused?

jelenex’s picture

This is already fixed in the 2.x-dev version - see #809054: Abnormal number of db_query calls; caching is needed

Anyway, I think it is a rather severe problem, because it can slow down the site considerably and it requires module users to hunt down the problem and search the issue queue. And this kind of module isn't exactly where I'd expect any major slowdowns. I'd really like to see the bugfix included into next beta/RC release as soon as possible..

achton’s picture

Subscribing. I don't think comments #6, #7, #8 are related to the original issue?