filefield_paths makes use of the mysql mediumtext column type on the filename and filepath database columns. Because of this, lookups to these tables are unable to use MySQL's in memory temporary tables 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 as on-disk temporary tables can have a massive effect on performance. I don't see any need for these columns to be of type mediumtext. The core files.module uses varchar(255) to store the same data.

Please consider changing these columns to varchar which will allow MySQL to make proper use of indexes and in memory temporary tables.

Comments

deciphered’s picture

deciphered
Primary language English
Location Ballarat, Victoria, Australia
commented
Status: Active » Postponed

As not knowing enough about SQL, I would agree that the SQL could do with some optimization, however I can see need for more than 255 characters, and have specifically been asked for what is currently offered, so this particular change would cause other issues.

I will try to make time to look into other alternatives, but would also be happy with suggestions.

Cheers,
Deciphered.

gapple’s picture

gapple
he/they
Primary language English
commented

varchar fields are only limited to 255 characters prior to MySQL 5.0.3
5.0.3 and later support varchar fields up to 65,535, depending on character set and subject to the maximum row size.
http://dev.mysql.com/doc/refman/5.0/en/char.html

Before making a change, it would be worthwhile to check if the file paths are ever in fact used in queries that utilized temporary tables. If so, a varchar field of length 1000 or so may be more than adequate.

gapple’s picture

gapple
he/they
Primary language English
commented

I think with the field names in the FileFieldPaths table having the same name as those in the core file module table, there is some confusion of purpose (which I fell prey to as well)

In the core file module, the path and filename fields are in fact used for storing path and filename data which could be justifiably assumed to be limited to 255 characters each.
In FileFieldPaths, the columns of the same name do not store file information, but serialized PHP data specifying the settings for saving files. I think it's safe to say that this could very well exceed 255 characters, and will probably only be used in a simple query where temporary tables will not be needed.

This issue can probably be closed as "By Design" or "Won't Fix"

gapple’s picture

gapple
he/they
Primary language English
commented
Status: Postponed » Closed (works as designed)

I don't think core yet supports larger VARCHAR fields, and the serialized PHP data values should not be limited to 255 characters to prevent truncation.