[TYPO3-core] RFC #3107: Duplicate database index in indexed search

[Ernesto Baschny [cron IT]]

Dmitry Dulepov schrieb am 24.03.2010 15:28:

> This is SVN patch request.
> 
> Type: bug, trivial
> 
> Branches: 4.4 (database change)
> 
> BT reference: http://bugs.typo3.org/view.php?id=3107
> 
> Problem: index_section table contains a duplicate index. This causes
> unnecessary updates, increases database size and makes it more difficult
> for MySQL to choose the best index.
> 
> Solution: get rid of the duplicate index.

This is not a duplicate, as already mentioned. If you remove that one,
you will no longer have an index to search by "phash", for example.

BEFORE:

mysql> explain select * from index_section where phash='1234';
+----+-------------+---------------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table         | type | possible_keys | key     |
key_len | ref   | rows | Extra |
+----+-------------+---------------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | index_section | ref  | joinkey       | joinkey | 4
      | const |    1 |       |
+----+-------------+---------------+------+---------------+---------+---------+-------+------+-------+

AFTER:

mysql> explain select * from index_section where phash='1234';
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table         | type | possible_keys | key  |
key_len | ref  | rows | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | index_section | ALL  | NULL          | NULL | NULL
   | NULL | 2057 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+

To be sure if you drop a key that it won't impact your performance, you
have to analyse all potential queries. If you have done that and you can
prove that there are no such queries (like searching for phash alone),
it would be ok to optimize it.

Otherwise I see no need to do it: phash and rl_0 are both 32-bit-int,
index_section is not the most populated table in indexed search, so the
minimal database size impact of that index isn't really worth the risk
of having indexed search more unperformant.

Cheers,
Ernesto