Eu tenho duas tabelas que representam uma lista de urls e seus índices de palavras relacionadas. Aqui estão as definições de tabela para referência.
desc urllist;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| url | text | NO | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
e
desc wordlocation;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| urlid | bigint(20) unsigned | NO | | NULL | |
| wordid | bigint(20) unsigned | NO | | NULL | |
| location | int(10) unsigned | NO | | NULL | |
+----------+---------------------+------+-----+---------+-------+
O aplicativo de software é um web spider. Ele rastreia uma lista de URLs, extrai esses URLs e os insere na urllisttabela. Em seguida, um indexador verifica quais urls ainda não foram indexados e, em seguida, procede à indexação dos referidos urls.
Aqui está a consulta que estou usando para encontrar itens na tabela da esquerda ( urllist) que ainda não foram indexados na tabela da direita ( wordlocation). Esta consulta é sugerida no site mysql.com :
select * from urllist ul
left join wordlocation wl on ul.id = wl.urlid
where wl.urlid IS NULL;
No momento em que escrevo, meu banco de dados de teste tem apenas 600 URLs indexados e a tabela de localização de palavras tem 1,3 milhão de linhas. No entanto, minha CPU está em 100% e o máximo que esperei para ver se a consulta seria concluída é meia hora (o que, aliás, nunca aconteceu).
Para ser completo, aqui está a explicação da consulta:
explain select * from urllist ul left join wordlocation wl on ul.id = wl.urlid where wl.urlid IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
| 1 | SIMPLE | ul | ALL | NULL | NULL | NULL | NULL | 50364 | |
| 1 | SIMPLE | wl | ALL | NULL | NULL | NULL | NULL | 1351371 | Using where; Not exists |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
Preciso que essa consulta seja concluída em segundos, não em minutos. Além disso, estou preocupado com a escalabilidade. Eu tenho 40.000 urls únicos esperando para serem adicionados ao índice, então como posso levar isso em consideração com minha tabela e design de consulta? 400.000 URLs?
Apenas algumas notas sobre minhas decisões sobre a estrutura atual da mesa.
Não tenho intenção de parar em 400.000 urls, mas talvez bigint(20) seja um pouco zeloso demais?
Url como texto é por razões mais práticas. Eu indexo muitos domínios asiáticos e de outros idiomas estrangeiros que não aparecem como seus equivalentes Kanji ou outros caracteres no banco de dados e frequentemente ocupam mais de 255 caracteres.
Estou usando MySQL. Estou definitivamente aberto a sugestões para melhorar o design de tabelas e consultas. Por favor, deixe-me saber se eu posso fornecer mais informações.
