Warning: Creating default object from empty value in /www/htdocs/v030397/mysql-qotd/wp-content/plugins/sitepress-multilingual-cms/sitepress.class.php on line 4991

Warning: Creating default object from empty value in /www/htdocs/v030397/mysql-qotd/wp-content/plugins/sitepress-multilingual-cms/sitepress.class.php on line 4993
Question 73: How will the table look? – MySQL Question of the Day

Skip to content

Question 73: How will the table look?

20. June 2010
By plogi in mysql questions

1 Comment

create table bla_di_da(bla int,di int, da int);
alter table bla_di_da add index (di);
alter table bla_di_da add index (di);
alter table bla_di_da add index (di);

a) The 2nd and 3rd ALTERs will fail
b) All the ALTERs will run fine and 3 indexes will be the result
c) All the ALTERs will run fine and 1 index will be the result

 
[ Just for fun ]

Tags: , ,

Comment Feed

One Response

  1. plogi — 20. June 2010 @ 13:36:29 —


    b

    Three indexes on the same column will be created! If seen this in production databases, and it just makes things slow and uses extra storage. When creating DDL, give indexes a name, then this cannot happen.


    MariaDB [tt]> show create table bla_di_da\G
    *************************** 1. row ***************************
    Table: bla_di_da
    Create Table: CREATE TABLE `bla_di_da` (
    `bla` int(11) DEFAULT NULL,
    `di` int(11) DEFAULT NULL,
    `da` int(11) DEFAULT NULL,
    KEY `di` (`di`),
    KEY `di_2` (`di`),
    KEY `di_3` (`di`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

You must be logged in to post a comment.