Arash Mousavi's questions

No MySQL, quando tento alterar a chave primária por esta consulta:

 ALTER TABLE `resources` DROP PRIMARY KEY, ADD
 PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`);

Eu executo esta consulta com o usuário root do MySQL, mas ocorreu este erro:

#1878 - Temporary file write failure.

O projeto da minha mesa é:

CREATE TABLE `resources` (
    `role_id` INT(11) UNSIGNED NOT NULL ,
    `category_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 1 ,
    `lang` BINARY(2) NOT NULL ,
    `model` SMALLINT(5) UNSIGNED NOT NULL ,
    `rule` CHAR(4) NOT NULL ,
    PRIMARY KEY (`category_id`, `lang`, `model`, `rule`, `role_id`)
 )
 ENGINE = InnoDB;

Esta tabela tem cerca de 2000 linhas.

Eu uso MySQL 5.6

Por que esse erro ocorreu?

O que devo fazer?

Eu tenho esta tabela:

CREATE TABLE IF NOT EXISTS `usergroups` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `user_id` int(11) unsigned NOT NULL,
      `group_id` smallint(5) unsigned NOT NULL,
      PRIMARY KEY (`group_id`,`user_id`),
      KEY `id` (`id`),
      KEY `user_id` (`user_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5496 ;

ao executar esta consulta:

EXPLAIN SELECT `UserGroup`.`user_id` FROM   `usergroups` AS `UserGroup` WHERE  `UserGroup`.`group_id` = 1 limit 30

sua saída é:

1       SIMPLE        UserGroup      ref  PRIMARY  PRIMARY  2        const  543      Using index

Acho que algo está errado! porque afetou 543 linhas, mas acho que deveria afetar 30 linhas no máximo. É verdade?

Tenho uma tabela com esta estrutura:

mysql> describe user_groups;

+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | int(11) unsigned     | NO   | MUL | NULL    | auto_increment |
| user_id  | int(11) unsigned     | NO   | PRI | NULL    |                |
| group_id | smallint(5) unsigned | NO   | PRI | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+

chave primária desta tabela é: PRIMARY(user_id, group_id) Tenho uma consulta simples:

mysql> EXPLAIN SELECT `UserGroup`.`user_id` , `UserGroup`.`group_id` FROM `user_groups` AS `UserGroup` WHERE `UserGroup`.`user_id` IN ( 1, 2 );
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | UserGroup | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+

ele tem using where;, de acordo com a busca de disco adicional, eu conheço using where;o conceito e também sei que não é terrível, mas conceitualmente quero saber por que essa consulta tem using where;, embora a condição esteja diretamente na chave primária e não tenha ordem ou qualquer coisa que cause using where;. Como posso consertar isso using where;?

Temos sites de notícias de alto tráfego, quero adicionar um recurso que cada usuário pode pesquisar em todo o conteúdo do site, como news, polls, comments, galleriesetc . Cada tipo de conteúdo tem sua(s) própria(s) tabela(s).

Decidi criar uma tabela que contém todo o conteúdo de todos os tipos:

CREATE TABLE full_text_search
(
    master_id INT NOT NULL,
    content_text TEXT NOT NULL,
    PRIMARY KEY ( master_id )
);

Eu gero um número único master_idpara cada conteúdo de todos os tipos para identificar cada um content_textna full_text_searchtabela.

por exemplo:

News table:
+----+-------------+---------+---------+----------+------------+
| id | news_title  | lead    | subtitle|  content | master_id  |
+----+-------------+---------+---------+----------+------------+
|  1 |  sometitle  |some lead| subtitle|content 1 |     3      |
|  2 |  some title |some lead| subtitle|content 2 |     5      |
+----+-------------+---------+---------+----------+------------+

article table:
+----+-------------+---------+------------------+---------+------------+
| id | title       | author  | short description| content | master_id  |
+----+-------------+---------+------------------+---------+------------+
|  1 |  sometitle  | someone | very short desc  |content1 |     1      |
|  2 |  some title | otherone|  some short desc |content2 |     4      |
+----+-------------+---------+------------------+---------+------------+

Como você pode ver master_id, é único entre as tabelas acima. Sempre que um novo conteúdo de cada tipo é inserido, também devo inseri- INSERTlo na full_text_searchtabela.

PERGUNTAS

• Para muitas inserções por dia (cerca de 3000 de todos os tipos), é uma boa solução ou é anti-padrão?
• É uma escolha melhor se eu separar esta tabela das minhas outras tabelas e colocá-la em qualquer outro banco de dados, como outro RDBMS ou NoSQLs?
• Quaisquer outras soluções?

Criei uma tabela usando uma consulta de seleção complicada via CREATE TABLE ... AS SELECT.... Como posso adicionar uma chave primária de autoincremento nesta consulta?

Por exemplo:

create table `user_mv` select `user`.`firstname` as 
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang` 

Esta consulta cria uma tabela que contém firstname, lastname, lang, username, group_namecolunas. Eu quero que também tenha uma idcoluna que seja uma chave primária de autoincremento.

Existe alguma maneira de fazer isso alterando esta consulta? Eu sei que posso fazer isso alterando a tabela depois de executar essa consulta, mas se houver alguma maneira de fazer isso diretamente na create tabledeclaração, gostaria de saber como fazer isso.

quando uso CREATE TEMPORARY TABLEpara criar uma tabela temporária, o collation dessa tabela é utf8_unicode_cipor padrão. por que por padrão é utf8_unicode_ci? Como posso alterar o padrão?

Eu tenho uma tabela de posts que tem a coluna post_order. Eu armazeno a ordem de cada postagem nele. quando altero a ordem de uma linha de 25 para 15, devo atualizar toda a linha de 15 para o final. É bom para poucas linhas, mas em milhares de linhas é pior.

Existe algum design melhor para ordenar posts, que seja mais eficiente?

Onde posso encontrar a documentação do tpcc-mysql? Pesquisei no google mas não encontrei nenhuma documentação a respeito.

Instalei o Percona Server 5.6.12-rc60.4, quando desejo criar tabelas como:

CREATE TABLE IF NOT EXISTS `mydb`.`table` (
`id` INT NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY ( `id` ) 
) ENGINE = XtraDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

ocorreu este erro:

 #1286 - Unknown storage engine 'XtraDB' 

Como posso criar uma tabela com o mecanismo XtraDB?

Para a coluna de propósito booleano, qual é o melhor: anulável char(0)ou tinyint(1).

Eu sei que bool é um apelido para tinyint(1) , mas no livro "High Performance MySQL" publicado pela O'reilly disse:

"Se você deseja armazenar um valor verdadeiro/falso em um único bit de espaço de armazenamento, outra opção é criar uma coluna CHAR(0) anulável. Essa coluna é capaz de armazenar a ausência de um valor (NULL) ou um zero - valor de comprimento (a string vazia)."

qual é melhor para tamanho, desempenho, indexação ou ...

ATUALIZAÇÃO : Achei este link útil para esta pergunta: Armazenamento de valor booleano eficiente para tabelas Innodb

Eu sei que para cada chave estrangeira devemos fazer um índice em sua coluna, mas não é necessário quando essa coluna na primeira parte do índice PRIMARY.

O MySQL aceita esta consulta:

CREATE TABLE IF NOT EXISTS `poll_option_i18ns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poll_option_id` int(11) NOT NULL,
  `lang` char(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `title` varchar(127) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`poll_option_id`,`lang`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `poll_option_i18ns`
  ADD CONSTRAINT `poll_option_id` FOREIGN KEY (`poll_option_id`) REFERENCES `poll_options` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Mas no MySQL workbench não consegui adicionar a chave estrangeira sem o índice! É um bug do MySQL Workbench ou eu faço da maneira errada?

Eu uso o MySQL 5.6 e tenho duas tabelas cada uma com 16 milhões de linhas:

CREATE TABLE IF NOT EXISTS `newsstudios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16855382 ;

CREATE TABLE IF NOT EXISTS `newsstudio_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsstudio_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `newsstudio_id` (`newsstudio_id`),
  KEY `category_id` (`category_id`),
  KEY `newsstudio_id_category_id` (`newsstudio_id`,`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16864013 ;

Eu tenho uma consulta com pedido por order by newsstudios.id ASC:

SELECT SQL_NO_CACHE id FROM `newsstudios` WHERE exists 
(
  select newsstudio_id from newsstudio_categories 
  where newsstudios.id=newsstudio_categories.newsstudio_id 
  and newsstudio_categories.category_id in (1303,1313,1323,1333,1343,632)
) 
order by newsstudios.id limit 5;

o resultado dessa consulta é:

+------+
| id   |
+------+
|   27 |
|   47 |
|   87 |
|  110 |
|  181 |
+------+
5 rows in set (0.19 sec)

mas quando mudo a direção do order by para DESCo tempo de execução da query diminui 100 vezes:

+------+
| id   |
+------+
| 98232|
| 98111|
| 95222|
| 88132|
| 78181|
+------+
5 rows in set (21 sec)

Primeiro: por que essa mudança na direção do pedido causa essa diferença enorme de desempenho?

Segundo: antes desta consulta eu tentei LEFT JOINe WHERE INconsultas em vez de WHERE EXISTS, mas eles têm resultado duplicado que devo usar GROUP BYque causa using filesorte using temporaryque diminui muito o desempenho. Você tem alguma sugestão para que a consulta tenha melhor desempenho?

Não consigo entender esta declaração em Otimizando o tamanho dos dados :

Declare colunas com informações idênticas em tabelas diferentes com tipos de dados idênticos, para acelerar as junções com base nas colunas correspondentes.

Alguém pode descrever esta afirmação com um exemplo?