Here's an answer from someone at eZ:
the issue is already created as an improvement. it is not a bug, since the
fetch operator has so many functions that trying to foresee all use cases
is just not possible. performance bottlenecks such as yours depend on the
specific usage case by case, and trying to make a "generic" optimization at
design time is just not a good idea. actually, i think it's an
so, enter the mysql query EXPLAIN. if you have a specific fetch operator
(the remote_id in your case) that overloads the db, you need to optimize
the installation, as long as kernel and db schema are not modified, you can
customize to increase the performance. in this case there's already an
improvement because it seems an optimization common enough to be useful to
the consequences for your system should be none, apart from the big
performance improvement. that is, assuming that your db server can cope
with the index. it is loaded into ram, and it can grow to 100's of MB in
size. so, be sure that the server has enough ram, or mysql will start
thrash-swapping trying to keep all that info in server memory.
a generic index would be created with the 100 chars length, but since your
db has a very large size, you can try optimizing the index according to the
contents that you have in the remote_id field, following this rule:
optimize the index for the N first chars, that depends on your data in the
field. double N, double the memory, halve the search time. please refer to
mysql docs to create an index with ( n ) size.
you will not have performance hit for updates, the innodb b-tree indexation
in mysql will take care of that. if over time you find that the update is
getting slower, running ANALYSE or OPTIMIZE on the table will be enough to
rebuild the index. even with massive updates, running this operation
monthly should be enough(if you ever need it at all). beware that these
commands lock the tables, please check the mysql documentation if you
intend to use them.
i recommend that you set the generic index on a stage setup, and check how
much ram it eats in the server, check how fast the queries go. then you can
try to fine-tune it, set index length to 50 (or increase server ram),
repeat until you get a balance that works.