dotancohen's questions

No Mysql 8.0.28, notei que algumas linhas de metadados, como "N rows in set", são exibidas, embora não correspondam às configurações do Pager. Por exemplo:

mysql> pager grep address
PAGER set to 'grep address'
mysql> show tables;
| customer_address_entity                                |
| customer_address_entity_datetime                       |
| customer_address_entity_decimal                        |
| customer_address_entity_int                            |
| customer_address_entity_text                           |
| customer_address_entity_varchar                        |
| inventory_pickup_location_quote_address                |
| quote_address                                          |
| quote_address_item                                     |
| sales_order_address                                    |
450 rows in set (0.01 sec)

Em que nível essas linhas de metadados são adicionadas e em que nível o Pager é aplicado? As linhas de metadados são enviadas como um descritor de arquivo alternativo, semelhante a algum descritor de arquivo stdmeta ou mesmo stderror (como curlacontece com os metadados)? Ou talvez o Pager seja aplicado no nível do cliente, não no servidor?

Eu tenho um aplicativo para depurar que executa algumas dezenas de operações em um banco de dados MySQL. Essas operações são realizadas em uma única transação. Eu preciso saber o estado do banco de dados durante a depuração em um ponto de interrupção específico.

Posso fazer login no serviço MySQL usando a mysqlferramenta CLI comum. Vejo que uma transação em andamento:

mysql> SELECT * FROM information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 325090
                 trx_state: RUNNING
               trx_started: 2022-09-14 12:10:32
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 30
       trx_mysql_thread_id: 26
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 10
          trx_lock_structs: 26
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 43
         trx_rows_modified: 4
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.01 sec)

Agora, como posso ingressar nessa transação para consultar o estado do banco de dados durante a transação?

Como solução alternativa, eu poderia adicionar uma consulta ao código do aplicativo. No entanto, isso exige que eu saiba qual consulta quero executar antes de executar o aplicativo e, em seguida, recompile o aplicativo para cada consulta que desejo executar. Ter uma CLI aberta para depurar seria muito útil.

O banco de dados é MySQL 8.0 rodando no Debian.

Considere uma tabela EAV com colunas separadas para cada tipo de dados:

CREATE TABLE eav_values (
    id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entity_id INT NOT NULL,
    name VARCHAR(127) NOT NULL,
    type SET('varchar','int','datetime') NOT NULL,
    col_varchar VARCHAR(1023) NULL DEFAULT NULL,
    col_int INT NULL DEFAULT NULL,
    col_datetime DATETIME NULL DEFAULT NULL
) ENGINE=InnoDB;

Agora eu gostaria de armazenar a usernamecoluna para entity_id=5, que é um VARCHAR:

INSERT INTO eav_values (entity_id, name, type, col_varchar)
    VALUES (5, 'username', 'varchar', 'jeffatwood');

Como alguém construiria a instrução SELECT para selecionar na coluna apropriada com uma única consulta?

SELECT (MAGIC HERE) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

Seria melhor simplesmente COALESCE os valores e gerenciar cuidadosamente os dados no código do aplicativo? Não há nenhuma penalidade de desempenho real para tal SELECT?

SELECT COALESCE(col_varchar, col_int, col_datetime) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

readOu talvez duplique os dados em uma coluna VARCHAR redundante para leitura?

INSERT INTO eav_values (entity_id, name, type, col_varchar, read)
    VALUES (5, 'username', 'varchar', 'jeffatwood', 'jeffatwood');

SELECT read FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

Observe que eu gostaria de usar tipos de dados diferentes para cada valor, pois em alguns casos posso precisar indexar ou executar lógica específica de tipo de dado em determinadas colunas, nesses casos saberei qual tipo de dados ou usarei um SELECT separado para obter o tipo de coluna.

Como alguém pode incrementar um valor "fixo" na SELECTcláusula de uma consulta, de modo que cada linha tenha um conjunto de valores diferente? Para um exemplo obviamente não funcional que ilustra a intenção:

SELECT
    name, ++x as ordinal
FROM
    foo;

name   | ordinal
----------------
john   | 0
paul   | 1
george | 2
ringo  | 3

A intenção final é selecionar usuários de um banco de dados para ganhar o primeiro, segundo e terceiro prêmios. Eu teria um usuário ganhando o primeiro prêmio, cinco vencedores do segundo prêmio e vinte vencedores do terceiro prêmio:

SELECT
    name, IF(++x<2, 'first',  IF(x<7, 'second', 'third') ) as prize
FROM
    foo LIMIT 26;

name     | prize
------------------
alice    | first
bob      | second
charlie  | second
dotan    | second
erik     | second
frank    | second
george   | third
henry    | third
:        | :
:        | :

Isso está sendo executado no MySQL v5.5. Obrigada.

Para a consulta a seguir, tenho um índice de cobertura da seguinte forma:

SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent;

ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id);

Agora tenho outra consulta para a qual gostaria de adicionar um índice de cobertura:

SELECT user_id FROM foo WHERE bar_id=:bar_id AND date_sent=:date_sent;

O índice de cobertura anterior também cobre esta nova consulta? Quando executo EXPLAINa nova consulta, a seguinte saída é retornada:

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | foo   | ref  | bar_id        | bar_id | 4       | const |    5 | NULL  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+

Vejo que a chave mencionada é bar_id, mas isso me parece mostrar que este não é um índice de cobertura . Além disso, o Extracampo não mostra a Using indexsaída esperada que me garantiria que um índice de cobertura está em uso.

Eu tenho uma tabela específica para a qual não posso adicionar uma chave:

mysql> ALTER TABLE tasks ADD KEY `fruitful_user_count` (`user_id`, `is_fruitful`);
ERROR 1034 (HY000): Incorrect key file for table 'tasks'; try to repair it

Pesquisando o problema, parece que esse problema geralmente é um problema de configuração ou um problema de espaço em disco . Na verdade, esse banco de dados está sendo executado em uma instância do Amazon RDS , o que significa que é basicamente um servidor gerenciado dedicado ao MySQL com uma configuração muito padrão. Além disso, o disco alocado para nós está apenas 25% cheio.

Considerando que talvez o disco na VM (alimentado por Xen, acredito) esteja cheio, e não meu espaço em disco alocado, que provavelmente nem está na mesma sala (armazenamento de rede), reiniciei a instância do RDS na esperança de obter uma nova instância em outra VM. No entanto, isso não ajudou.

Qual deve ser minha próxima etapa de solução de problemas?

Esta é a tabela:

mysql> show create table tasks;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tasks | CREATE TABLE `tasks` (
`user_id` char(32) NOT NULL,
`module_id` int(11) NOT NULL DEFAULT '0',
`is_successful` tinyint(1) DEFAULT NULL,
`is_fruitful` tinyint(1) DEFAULT NULL,
`last_run` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_pulled` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`user_id`,`module_id`),
KEY `phone_scrapes_user` (`user_id`),
KEY `phone_scrapes_module` (`module_id`),
KEY `urgency` (`last_pulled`,`last_run`),
KEY `successful_user_count` (`user_id`,`is_successful`),
KEY `is_successful` (`is_successful`),
KEY `fruitness` (`is_fruitful`,`is_successful`),
CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Em uma nova instalação do Kubuntu 14.04, executei o sudo aptitude install mysql-server-core-5.6. A instalação não pôde ser concluída devido a dependências de alguns pacotes do KDE no mysql-server-core-5.5, que teriam sido substituídos. Agora, quando executo sudo aptitude install mysql-server-5.5, recebo este erro depois de especificar uma senha:

Configuring mysql-server-5.5
Unable to set password for the MySQL "root" user
An error occurred while setting the password for the MySQL administrative user. This may have happened
because the account already has a password, or because of a communication problem with the MySQL server.
You should check the account's password after the package installation.
Please read the /usr/share/doc/mysql-server-5.5/README.Debian file for more information.

Na verdade, eu li /usr/share/doc/mysql-server-5.5/README.Debian, mas não havia nada relevante para a minha situação. O log do MySQL ajuda:

$ tail /var/log/mysql/error.log 
140818 10:17:16 InnoDB: Completed initialization of buffer pool
140818 10:17:16 InnoDB: highest supported file format is Barracuda.
140818 10:17:16  InnoDB: Waiting for the background threads to start
140818 10:17:17 InnoDB: 5.5.38 started; log sequence number 1595675
140818 10:17:17 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'
140818 10:17:17 [ERROR] Aborting

140818 10:17:17  InnoDB: Starting shutdown...
140818 10:17:18  InnoDB: Shutdown completed; log sequence number 1595675
140818 10:17:18 [Note]

No entanto, não consigo encontrar em qual script a --explicit_defaults_for_timestampopção está definida. Tentei iniciar mysqlsem servicepara evitar a --explicit_defaults_for_timestampopção, mas ainda não inicia:

$ ps aux | grep mysql
dotanco+ 25458  0.0  0.0  11748   928 pts/4    S+   10:30   0:00 grep --color=auto mysql

$ sudo mysqld_safe --skip-grant-tables &
[1] 25470
140818 10:30:54 mysqld_safe Can't log to error log and syslog at the same time.  Remove all --log-error configuration options for --syslog to take effect.
140818 10:30:54 mysqld_safe Logging to '/var/log/mysql/error.log'.
140818 10:30:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140818 10:30:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

[1]+  Done                    sudo mysqld_safe --skip-grant-tables

$ ps aux | grep mysql
dotanco+ 25810  0.0  0.0  11748   932 pts/4    S+   10:31   0:00 grep --color=auto mysql

O erro me diz para Remove all --log-error configuration options. Portanto, editei /etc/mysql/my.cnfe comentei a seguinte linha:

log-error  = /var/log/mysql/error.log

Agora não recebo nenhum erro, mas ainda não consigo fazer login:

$ sudo mysqld_safe --skip-grant-tables &
[1] 26558
140818 10:34:37 mysqld_safe Logging to syslog.
140818 10:34:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140818 10:34:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

[1]+  Done                    sudo mysqld_safe --skip-grant-tables

$ mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Qual pode ser o problema? Como devo proceder?

EDITAR

Eu agora comentei a explicit-defaults-for-timestamplinha em my.cnf. Aqui está o arquivo inteiro, menos os comentários do cabeçalho:

$ cat /etc/mysql/my.cnf 

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
#explicit_defaults_for_timestamp

bind-address    = 127.0.0.1

#log-error      = /var/log/mysql/error.log

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

symbolic-links=0

!includedir /etc/mysql/conf.d/

Isto é o que acontece quando tento executar o MySQL:

$ sudo echo 1 >> /var/log/mysql/error.log

$ tail /var/log/mysql/error.log 
140818 10:31:49  InnoDB: Waiting for the background threads to start
140818 10:31:50 InnoDB: 5.5.38 started; log sequence number 1595675
140818 10:31:50 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'
140818 10:31:50 [ERROR] Aborting

140818 10:31:50  InnoDB: Starting shutdown...
140818 10:31:51  InnoDB: Shutdown completed; log sequence number 1595675
140818 10:31:51 [Note] 
140818 10:31:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
1

$ sudo service mysql start
start: Job failed to start

$ tail /var/log/mysql/error.log 
140818 10:31:49  InnoDB: Waiting for the background threads to start
140818 10:31:50 InnoDB: 5.5.38 started; log sequence number 1595675
140818 10:31:50 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'
140818 10:31:50 [ERROR] Aborting

140818 10:31:50  InnoDB: Starting shutdown...
140818 10:31:51  InnoDB: Shutdown completed; log sequence number 1595675
140818 10:31:51 [Note] 
140818 10:31:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
1

$ grep explicit_defaults_for_timestamp /etc/mysql/my.cnf /etc/init.d/mysql /etc/default/mysql
/etc/mysql/my.cnf:#explicit_defaults_for_timestamp
grep: /etc/default/mysql: No such file or directory

Como pode ser visto, nada de novo é gravado nos logs e a explicit_defaults_for_timestampopção não está definida em nenhum lugar.

Consegui reinstalar mysql-server-core-5.5, mas isso não resolveu o problema:

$ sudo aptitude reinstall mysql-server-core-5.5
The following packages will be REINSTALLED:
  mysql-server-core-5.5 
0 packages upgraded, 0 newly installed, 1 reinstalled, 0 to remove and 0 not upgraded.
Need to get 3,213 kB of archives. After unpacking 0 B will be used.
Get: 1 http://il.archive.ubuntu.com/ubuntu/ trusty-updates/main mysql-server-core-5.5 amd64 5.5.38-0ubuntu0.14.04.1 [3,213 kB]
Fetched 3,213 kB in 1s (3,163 kB/s)                
(Reading database ... 217167 files and directories currently installed.)
Preparing to unpack .../mysql-server-core-5.5_5.5.38-0ubuntu0.14.04.1_amd64.deb ...
Unpacking mysql-server-core-5.5 (5.5.38-0ubuntu0.14.04.1) over (5.5.38-0ubuntu0.14.04.1) ...
Replaced by files in installed package mysql-common (5.6.20-1ubuntu14.04) ...
Processing triggers for man-db (2.6.7.1-1) ...
Setting up mysql-server-core-5.5 (5.5.38-0ubuntu0.14.04.1) ...

$ sudo service mysql status
mysql stop/waiting

$ sudo service mysql start
start: Job failed to start

$ tail /var/log/mysql/error.log 
140818 10:31:49  InnoDB: Waiting for the background threads to start
140818 10:31:50 InnoDB: 5.5.38 started; log sequence number 1595675
140818 10:31:50 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'
140818 10:31:50 [ERROR] Aborting

140818 10:31:50  InnoDB: Starting shutdown...
140818 10:31:51  InnoDB: Shutdown completed; log sequence number 1595675
140818 10:31:51 [Note] 
140818 10:31:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
1

Que 1na última linha do log foi adicionado anteriormente com echoe indica que nada de novo foi adicionado ao log.

Em uma máquina Ubuntu Server 12.10, tentei excluir o ibdata1arquivo antes de ler a explicação de Rolando sobre como exatamente fazer isso . Especificamente, eu não havia definido innodb_fast_shutdown = 0antes de desligar o MySQL.

Como posso iniciar o MySQL, agora que ibdata1foi excluído, mas innodb_fast_shutdownnão foi definido?

Eu tenho backups de todos os bancos de dados, mas não consigo reinstalar o MySQL, pois a versão do Ubuntu não é mais suportada. Não consigo atualizar o Ubuntu para uma versão compatível devido a um problema de acessibilidade nas versões atuais, nem posso fazer o downgrade para 12.04 LTS (ainda compatível), pois o Ubuntu nunca foi projetado para ser rebaixado e alguns aplicativos de que preciso não funcionarão bem nisso versão mais antiga, se eu fizer uma reinstalação completa, pois eles dependem de uma série de dependências não em 12.04, mas em 12.10.

Considere uma tabela de valores e hashes, assim:

+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| val        | char(9)  | NO   |     | NULL    |                |
| val_hashed | char(50) | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

A consulta a seguir termina em 0,00 segundos:

SELECT * FROM hashes ORDER BY 1 DESC LIMIT 1;

No entanto, esta consulta leva 3 minutos e 17 segundos:

SELECT val FROM hashes ORDER BY 1 DESC LIMIT 1;

Vejo que, enquanto a consulta está sendo executada, a lista de processos mostra como status Sorting result. A situação é completamente reproduzível. Observe que há outro processo realizando INSERToperações na tabela continuamente.

Por que a consulta mais específica levaria mais tempo para ser executada do que a *consulta? Sempre acreditei que *as consultas devem ser evitadas especificamente por motivos de desempenho.

Como alguém faz referência ao campo atualmente agrupado por em uma subconsulta?

Considere uma tabela com os seguintes dados:

Client    Status    Size    
   1       A         500
   1       B        1500
   2       A        2000
   2       B         800

Estou tentando obter os seguintes dados em uma única consulta:

Client     A      B
   1      500   1500
   2     2000    800

Eu tenho jogado com variações disso, sem absolutamente nenhum sucesso:

SELECT
  t.Client,
  (
    SELECT A FROM table WHERE Client=t.Client
  ) as A,
  (
    SELECT B FROM table WHERE Client=t.Client
  ) as B
FROM table t
GROUP BY t.Client;

O status de cada Cliente não é repetido , ou seja, cada cliente deve ter apenas uma linha com um determinado status. Para o propósito desta questão, podemos assumir que há exatamente uma entrada para cada status.

Observe que são cerca de cem mil linhas: cerca de 20 mil Clientes e cinco status por cliente. Existem outros campos também (como data de atualização de status e o usuário que realizou a atualização), portanto, alterar o esquema da tabela para simplesmente Client, A, Bnão é viável.

Como posso referenciar o campo agrupado nas subconsultas?

Tenho uma tabela com cerca de 17 milhões de linhas:

mysql> describe humans_we_respect;
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
| Field               | Type                                                                    | Null | Key | Default | Extra |
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
| id                  | bigint(20)                                                              | NO   | PRI | NULL    |       |
| name                | varchar(63)                                                             | YES  |     | NULL    |       |
| address             | varchar(127)                                                            | YES  |     | NULL    |       |
| city                | varchar(63)                                                             | YES  |     | NULL    |       |
| state               | varchar(3)                                                              | YES  | MUL | NULL    |       |
| zip                 | varchar(15)                                                             | YES  |     | NULL    |       |
| country             | varchar(15)                                                             | YES  |     | NULL    |       |
| email               | varchar(127)                                                            | YES  |     | NULL    |       |
| website             | varchar(127)                                                            | YES  |     | NULL    |       |
| area_code_state     | varchar(3)                                                              | YES  | MUL | NULL    |       |
| timezone            | set('other','pacific','mountain','central','eastern','alaska','hawaii') | YES  |     | other   |       |
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
12 rows in set (0.01 sec)

Devido à estrita natureza de apenas contactar quem manifestou interesse numa newsletter, e à estrita natureza de nunca contactar alguém que pediu para não ser contactado, antes de um mailing adicionei um campo para o expressed_interest (tinyint) deafult nullqual mudo 1para quem manifestou interesse, e depois mude para nullpara aqueles que pediram para não serem contatados.

A consulta a seguir, na qual 10.000 linhas são atualizadas por consulta, leva muito tempo para ser executada (eliminada após meia hora):

UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN (1,...,10000);

No entanto, a seguinte consulta é concluída em segundos:

INSERT INTO humans_we_respect (id) VALUES (1),...,(10000) ON DUPLICATE KEY UPDATE expressed_interest=1;

Em que condições será ON DUPLICATE KEY UPDATEmais rápido do que UPDATE? Eu gostaria de saber isso para uso futuro com tabelas grandes como esta.

Isso está no MySQL 5.5.33 em execução no Amazon RDS .

Este artigo muito útil explica algumas curiosidades sobre como o MySQL Enums se comporta : http://melp.nl/2009/05/mysql-enums-and-booleans/

Não há discussão sobre por que esse comportamento existe. Além disso, ao pesquisar o assunto no Google, encontro apenas informações e especulações contraditórias, de 'blogueiros' e outros cegos guiando outros cegos.

Alguém pode descrever por que o MySQL Enums sofre com o comportamento descrito na postagem. Aqui está um exemplo de esquisitices do Enum apresentadas no post, mas há muito mais:

mysql> insert into t values('1'),('0');
mysql> select b,b=1,b=0 from t;
+---+-----+-----+
| b | b=1 | b=0 |
+---+-----+-----+
| 1 |   0 |   0 |
| 0 |   1 |   0 |
+---+-----+-----+
2 rows in set (0.00 sec)

Considere uma tabela com um statuscampo que contém um dos dois valores:

| status | set('active','default') | YES  |  | active |

Preciso saber quantos registros existem na tabela e, além disso, quantos têm o statusconjunto definido como active. Percebo que SUMretorna 1para cada registro definido para activee 2para cada registro definido para default:

mysql> select count(*), sum(status) from users;
+----------+-------------+
| count(*) | sum(status) |
+----------+-------------+
|        3 |           4 |
+----------+-------------+
1 row in set (0.00 sec)

mysql> select status from users;
+---------+
| status  |
+---------+
| default |
| active  |
| active  |
+---------+
3 rows in set (0.00 sec)

Posso confiar neste comportamento? Isso é uma prática ruim?

Estou auditando um aplicativo que venho desenvolvendo ao longo de várias semanas. Eu notei isso em CREATE TABLE:

UNIQUE KEY `baz_3` (`baz`,`foo`,`bar`),
KEY `baz` (`baz`),

O explícito é KEYredundante, como penso? Eu só quero ter certeza antes de fazer DROPisso. Pelo que li, acredito que o UNIQUE KEYfuncionará como um índice quando o aplicativo for executado, WHERE baz='something'mas o MySQL parece tão complicado que acho prudente perguntar aqui primeiro.

À luz da discussão Meta sobre permitir perguntas básicas de SQL em dba.SE , apresento um problema que estou tendo agora, para o qual a resposta no Stack Overflow é inadequada e ingênua. Espero que haja uma solução melhor para o problema do que as apresentadas no SO (já que estou enfrentando esse problema em um aplicativo), e dba.SE parece ser o lugar perfeito para encontrar uma resposta melhor.

Aqui está a pergunta original no Stack Overflow: Como você seleciona cada n-ésima linha do mysql?

Aqui está a resposta aceita:

SELECT * 
FROM ( 
    SELECT 
        @row := @row +1 AS rownum, [column name] 
    FROM ( 
        SELECT @row :=0) r, [table name] 
    ) ranked 
WHERE rownum % [n] = 1 

O problema crítico com a resposta aceita é que ela requer puxar a tabela inteira para uma tabela temporária. Portanto, abordei essa preocupação no título desta pergunta.

Considere também que a tabela pode ter excluído linhas, portanto, uma consulta alternativa que fosse simplesmente testar WHERE MODa chave primária também não é uma boa solução. Id est, a chave primária não é confiável para ser sequencial.

Existe uma maneira melhor de formular uma consulta que retornaria a cada segundo, décimo ou n-ésima linha arbitrária, que não exija puxar a tabela inteira para a memória, mas também considere as linhas excluídas?

Cada n-ésima linha pode ser definida como tal:

n =  2: Rows 0, 2, 4, 6, 8, ...
n = 10: Rows 0, 10, 20, 30, ...
n = 42: Rows 0, 42, 84, 126, ...

Meu banco de dados de destino é o MySQL 5.5 rodando em uma distribuição Linux comum derivada do Debian.

EDIT: Em resposta à resposta de Thomas:

A solução sugerida não produz o resultado esperado, veja abaixo:

mysql> SELECT 
    ->     @i:=@i+1 AS iterator 
    ->     , t.name
    -> FROM 
    ->     events AS t,
    ->     (SELECT @i:=0) AS dummy
    -> WHERE @i % 10 = 0
    -> ORDER BY name ASC;
+----------+-------+
| iterator | name |
+----------+-------+
|        1 |     0 |
+----------+-------+
1 row in set (0.29 sec)

mysql> select count(*) from events;
+----------+
| count(*) |
+----------+
|   892507 |
+----------+
1 row in set (0.17 sec)

Como alguém pode recuperar itens que, quando unidos, não possuem um campo específico ( sent) ou, se tiverem esse campo ( sent), também possuem um campo adicional ( failure_reason) para cada sentlinha?

Considere um banco de dados com duas tabelas:

list
  id INT
  name VARCHAR

log
  id INT
  list_id INT REFERENCES (list.id)
  action VARCHAR
  failure_reason VARCHAR

listConteúdo típico :

ID | Name

1  | John
2  | Paul
3  | George
3  | Atwood

logConteúdo típico :

ID | List_id | Action  | Failure Reason
---+---------+---------+---------------
1  | 1       | entered | NULL
2  | 1       | sent    | NULL

3  | 2       | entered | NULL
4  | 2       | sent    | Connection Error
5  | 2       | sent    | NULL

6  | 3       | entered | NULL
7  | 3       | sent    | Cosmic Ray

8  | 4       | entered | NULL

Pode-se ver que List_id 1tem uma entrada de log para ambos enterede sent, sem failure_reason. Assim, este item saiu de minha responsabilidade.

Da mesma forma, List_id 2tem uma logentrada para entered, e duas para sent. Isso ocorre porque o primeiro sentfalhou. Sabemos que o primeiro enviado falhou porque failure_reasonnão é NULL. Como há uma sentlinha bem-sucedida, este item também saiu de minha responsabilidade.

No entanto, List_id 3tem uma logentrada para entered, mas a única sentlinha é uma falha. Assim, este item ainda é de minha responsabilidade e deve ser recuperado na consulta .

Além disso, List_id 4tem uma logentrada para entered, mas nenhuma sentlinha. Assim, este item também continua sob minha responsabilidade e deve ser recuperado na consulta .

Eu tentei usar alguma mágica de subconsulta para obter/excluir linhas com base nos sentvalores, no entanto, como esta tabela está crescendo muito (esperam-se milhares de novos registros por dia), preciso evitar consultas que exijam o retorno de todos os resultados em todo o banco de dados.

Isso está no MySQL 5.1 no CentOS 6.x.

Tenho duas tabelas das quais preciso apenas dos resultados que aparecem em uma tabela ( list) que não aparecem na segunda tabela ( cardinal). A listchave primária da tabela é sku, e a tabela possui uma idcoluna de vestígio (que na verdade não é utilizada no aplicativo no momento). A cardinalchave primária da tabela é id. A coluna que deve unir as tabelas é sku. Estas são as tabelas (apenas campos relevantes, existem outros campos que não colei aqui):

mysql> describe list;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| id              | int(11)      | NO   |     | NULL    |       |
| name            | varchar(63)  | YES  |     | NULL    |       |
| sku             | bigint(20)   | NO   | PRI | 0       |       |
+-----------------+--------------+------+-----+---------+-------+

mysql> describe cardinal;
+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| id               | int(11)     | NO   | PRI | NULL    | auto_increment |
| sku              | bigint(20)  | YES  | MUL | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+

Segue a quantidade de registros em cada tabela:

mysql> SELECT count(*) FROM list;
+----------+
|  2677513 |
+----------+

mysql> SELECT count(*) FROM cardinal;
+----------+
|   970924 |
+----------+

Cada registro na cardinaltabela tem uma entrada válida para a skucoluna e todas essas entradas válidas existem na listtabela. Existem alguns dupes para skua cardinaltabela.

Preciso de todos os registros da listtabela que não possuem entrada correspondente na cardinaltabela. No entanto, todos os métodos que estou tentando estão retornando todos os registros da listtabela ou nenhum registro!

mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON c.id=null;
+----------+
| count(*) |
+----------+
|  2677513 |
+----------+
1 row in set (0.49 sec)

mysql> SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id=null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

A primeira consulta obviamente não sabe corresponder na skucoluna. Mas o segundo parece que deve funcionar com base em outras respostas que vi online .

Espero que cerca de 1700000 - 2000000 linhas sejam retornadas. Mas certamente não 0 ou a tabela completa. Como devo formular a consulta? Este é o MySQL 5.5.33 em execução no Amazon RDS.

Considere as seguintes consultas:

mysql> SELECT count(*) FROM list l WHERE l.source='blink';
+----------+
| count(*) |
+----------+
|     3372 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----------+
| count(*) |
+----------+
|     5116 |
+----------+
1 row in set (2.47 sec)

Não houve nenhuma INSERTou outras consultas executadas no banco de dados entre essas duas consultas em execução. A segunda consulta parece mais restritiva que a primeira, então como ela poderia retornar mais linhas? Observe que a listtabela tem mais de 2 milhões de linhas e cardinalcerca de meio milhão de linhas.

EDIT: Adicionando EXPLAINsaída:

mysql> EXPLAIN SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref         | rows   | Extra       |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | c     | ALL    | NULL              | NULL    | NULL    | NULL        | 536258 | Using where |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY,id,source | PRIMARY | 8       | direct.c.id |      1 | Using where |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
2 rows in set (0.00 sec)

mysql> select count(*) from list;
+----------+
| count(*) |
+----------+
|  2165664 |
+----------+
1 row in set (1.32 sec)

mysql> select count(*) from cardinal;
+----------+
| count(*) |
+----------+
|   537007 |
+----------+
1 row in set (0.23 sec)

Considere uma tabela de alguns membros da família:

mysql> SELECT * FROM some_table;
+--------+--------+--------+
| f_name | l_name | work   |
+--------+--------+--------+
| Meirav | Cohen  | school |
| Dotan  | Cohen  |        |
+--------+--------+--------+
2 rows in set (0.00 sec)

Suponha que essa f_nameseja a chave primária e que todas as colunas sejam VARCHAR. Dados desconhecidos não são representados como NULL, mas sim como uma string vazia (como pode ser visto na workcoluna).

Gostaria de adicionar alguns dados que conheço sobre uma pessoa, mas prefiro não consultar o banco de dados para ver o que há, mas fazer com que o banco de dados mescle o valor existente com os novos valores:

mysql> INSERT INTO some_table (f_name, l_name, work)
    VALUES ('Dotan', '', 'php')
    ON DUPLICATE KEY UPDATE
        f_name = IFNULL(f_name, 'Dotan'),
        l_name = IFNULL(l_name, ''),
        work   = IFNULL(work,   'php');

Obviamente, IFNULLnão se encaixa no trabalho, pois verifica apenas NULL, não strings vazias. Além disso, existe a desvantagem de que cada valor a ser adicionado deve aparecer duas vezes no SQL.

Existe alguma maneira de fazer com que o banco de dados mescle os dados em strings vazias? Além disso, pode ser feito de forma que as strings a serem inseridas apareçam apenas uma vez no SQL?

Eu tenho uma grande tabela de banco de dados MySQL (~ 1 milhão de linhas e crescendo) em uma instância AWS RDS Medium :

mysql> describe clients;
+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(11)       | NO   | PRI | NULL    | auto_increment |
| name            | varchar(500)  | YES  |     | NULL    |                |
| address         | varchar(500)  | YES  |     | NULL    |                |
| city            | varchar(200)  | YES  |     | NULL    |                |
| state           | varchar(100)  | YES  |     | NULL    |                |
| zip             | varchar(50)   | YES  |     | NULL    |                |
| country         | varchar(50)   | YES  |     | NULL    |                |
| phone           | varchar(20)   | YES  | UNI | NULL    |                |
| source          | varchar(20)   | YES  | MUL | NULL    |                |
| campaign        | varchar(200)  | YES  |     | NULL    |                |
| search_term     | varchar(200)  | YES  |     | NULL    |                |
| search_location | varchar(200)  | YES  |     | NULL    |                |
| added           | datetime      | YES  |     | NULL    |                |
| email           | varchar(150)  | YES  |     | NULL    |                |
| website         | varchar(150)  | YES  |     | NULL    |                |
| full_output     | varchar(5000) | YES  |     | NULL    |                |
| client          | varchar(50)   | YES  |     | NULL    |                |
| is_deleted      | int(2)        | YES  |     | 0       |                |
| is_valid        | int(2)        | YES  |     | 1       |                |
+-----------------+---------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)

Frequentemente preciso executar uma variante da seguinte consulta:

SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;

E o relevante EXPLAIN:

mysql> EXPLAIN SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key    | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
|  1 | SIMPLE      | clients    | range | source        | source | 63      | NULL | 1168144 | Using where |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
1 row in set (0.03 sec)

Quais otimizações devo fazer? Devo adicionar índices nos campos namee zipou nos campos addede source?