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.
Comments
Comment #1
MGN CreditAttribution: MGN commentedThis 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.
Comment #2
ManyNancy CreditAttribution: ManyNancy commentedSubscribe
Comment #3
xtfer CreditAttribution: xtfer commentedsubscribe
Comment #4
rup3rt CreditAttribution: rup3rt commentedSubscribe
Comment #5
locomo CreditAttribution: locomo commentedsubscribe
Comment #6
hedac CreditAttribution: hedac commentedsubscribing.. 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?
Comment #7
rjivan CreditAttribution: rjivan commentedSame 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?
Comment #8
jelenex CreditAttribution: jelenex commented@rjivan
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..
Comment #9
achtonSubscribing. I don't think comments #6, #7, #8 are related to the original issue?
Comment #10
lamp5