Performance - slow mysql queries caused by use of TEXT type for php_visibility column [#583876]

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 commented 22 September 2009 at 02:45

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.

Comment #2

ManyNancy CreditAttribution: ManyNancy commented 23 January 2010 at 00:54

Comment #3

xtfer CreditAttribution: xtfer commented 29 April 2010 at 00:20

Comment #4

rup3rt CreditAttribution: rup3rt commented 6 July 2010 at 12:40

Comment #5

locomo CreditAttribution: locomo commented 16 July 2010 at 05:07

Comment #6

hedac CreditAttribution: hedac commented 1 December 2010 at 04:25

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?

Comment #7

rjivan CreditAttribution: rjivan commented 6 December 2010 at 14:47

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?

Comment #8

jelenex CreditAttribution: jelenex commented 8 December 2010 at 15:56

@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..