Menu
▾
▴
#908 (ok 4.3) Improvements for the table editor (index creation)
fixed
1
2014-12-05
2006-03-31
No
If I add two indexed fields to a table, it adds a
combined index while it should add two separate indexes.
ALTER TABLE `xwi_admin_log`
ADD `a` VARCHAR( 255 ) NOT NULL ,
ADD `b` VARCHAR( 255 ) NOT NULL ;
ALTER TABLE `xwi_admin_log` ADD INDEX ( `a` , `b` ) ;
Logged In: YES
user_id=418833
This is intended behaviour, afaik.
Logged In: YES
user_id=30264
Why is it intended?
Logged In: YES
user_id=210714
Doing a combined index. phpMyAdmin has done this since 1998.
Logged In: YES
user_id=30264
That's what. But why?
A bug that was introduced in 1998 is still a bug. :)
What is more common, multiple individual indexes or one
combined index?
In a combined index, the field order is very important, but
it's not possible to specify this field order. So the
usability for defining combined indexes is very low.
Logged In: YES
user_id=210714
This behavior was done at a time where the index management
page did not exist in phpMyAdmin. It's true that this area
needs improvement. When creating a table, we have radio
buttons and this currently creates a combined index. When
editing table's structure, we have icons that add separate
indexes, plus the index management feature that enables us
to specify order. I don't really know what would be the best
thing to do. I don't know what is more common, but I agree
that not being able to specify the field order of an index
when creating a table is confusing.
Logged In: YES
user_id=418833
Maybe, the table editor really needs to be improved.
In this case, Olaf or Marc, please submit a feature request
about this.
Silently changing the behaviour of interface elements
because another behaviour might be more common would result
in more confusion than anything else because phpMyAdmin has
always behaved the way it does now. Just look at the tons of
support requests we received just because there is no
config.inc.php in PMA's root dir anymore...
Those index radio buttons are just a shortcut to what the
index editor offers and phpMyAdmin assumes the same order of
fields for the index as in the table structure which - at
least when creating a table or adding fields with a combined
index - should be the most common intention. On top of this,
it is not possible to specify the index length for each
field to be included in the index.
We cannot expect that those tiny radiobuttons offer the full
potential of the index editor. And currently, I don't see
how we could improve the interface without overloading it.
Something we could do about this would be adding a note
about this behaviour to the table editor. Another this would
be removing the index radios entirely and force users to use
the index editor, but this would make others yell at us...
afaik, this behaviour is not a bug and it's furthermore no
limitation as well since indexes may be created via the
table structure and especially the index editor. Since
creating /altering a table and creating an index are two
seperate SQL queries (except for the primary key of course),
there is no need to create all indexes when creating the table.
This is why I'd like to close this report.
Objections? :-)
Logged In: YES
user_id=210714
Or just move this to feature request, in order to keep the
discussion thread?
Logged In: YES
user_id=30264
> Objections? :-)
Yes. :)
> - at least when creating a table or adding fields with a
combined index - should be the most common intention.
Based on what do you consider that to be the most common
intention?
If I look at my own databases, multiple single-field indexes
are far more common than combined indexes.
Also, based on what do you consider the implicit ordering to
be the most common intention?
> Silently changing the behaviour of interface elements
because another behaviour might be more common would result
in more confusion than anything else because phpMyAdmin has
always behaved the way it does now.
If this is the first (bug) report about this, another
scenario is that nobody used this feature to create indexes
on multiple fields (whether single or combined) at all.
Logged In: YES
user_id=210714
We could do a poll on the users list about this feature's
removal.
Logged In: YES
user_id=30264
I'd prefer changing it to create single-field indexes/keys only.
Logged In: YES
user_id=418833
> Or just move this to feature request, in order to keep
> the discussion thread?
D'accord. :-)
>> Objections? :-)
>
> Yes. :)
Why did I expect this? :o)
> If I look at my own databases, multiple single-field
> indexes are far more common than combined indexes.
True, but this wasn't what I meant. :-)
> Also, based on what do you consider the implicit ordering
> to be the most common intention?
Personal experiences. I have spent a lot of this analysing
other peoples db structures in order to optimise those. When
creating tables people tend to add fields in the order they
use them. Of all permutations of the tupel of fields for an
index (f(1), f(2), ... f(n)) the permutation (1, 2 ... n)
should be the most common one - when creating a table. For
instance (1, 2, 3, 4) should be more common than (1, 3, 2,
4) or (3, 4, 1, 2).
This is of course not true for tables that have grown over
time. If there's a permutation that appears more common to
you, please tell me. ;-)
Of course, the most common solution is not always what you
want, but as I said earlier this is what the index editor
was made for.
> If this is the first (bug) report about this, another
> scenario is that nobody used this feature to create
> indexes on multiple fields (whether single or combined)
> at all.
Well, I did... Does that disprove your scenario? ;-)
> We could do a poll on the users list about this
> feature's removal.
Maybe we should poll about how users create their
non-primary-key-indexes. If the radio buttons are really
rarely used, we could remove them and replace them by
checkboxes "add to primary key".
Another solution would be turning the table editor into a
wizard-like interface like our setup script.