Graphing Wikipedia 2: Change in Plans

Using a Different Dataset

During development of the XML article parser in Go, I was thinking about the amount of processing time required to stream over 100 GB of text and started to wonder if there was a better aproach to finding links. Because I was actively leaning Go while developing my solution, I would not be able to utilize multithreading, leading to a much more slower design.

New Plan

Somewhere in Wikimedia dump websites there was information about an SQL file dump, that I didn’t bother to look for initially. Because databases are better at handling larger datasets¹, I would be able to create the final graph file faster.

After deciding to go look for it, I located a collection of .sql files within the database backup dumps section (once again, I am using the enwiki Aug 2026 dump).

Understanding the New Data

MediaWiki uses a large database to organize a wide range of data. After looking at what each table stores, I found what tables I need

page: This one is obvious. This table contains information about a single page, such as what kind of page it is (article, talk, …), the title of the page (this might not be the real title, refer to redirect), and its ID.

pagelink: This is the big table. This table maps a page_id from the page table to entries in the linktarget table. This will be the most useful table, as it already has all the graph edges needed.

linktarget: Inside of pagelink, there is a column called pl_target_id. This foreign key points to entries in to linktarget. A single row in linktarget contains a title of a page within the page table.

redirect: This table just points to the real name of an page, if the page needed a redirect. For example page_id = 10 has a page_title of AccessibleComputing, but the real Wikipedia page is Computer accessibility. The query below shows this:

SELECT r.rd_title
FROM page AS p 
  INNER JOIN redirect AS r 
    ON p.page_id = rd_from 
WHERE p.page_id = 10;

Proof of Concept

In both of these examples, suppose we wanted to find the inbound and outbound links for the page “Horse.” Because we only want articles, we are filtering by page_namespace of 0.

Outbound Links

This one is easy. We just get every entry in the pagelink table which has a pl_from equal to the page_id of "Horse". The other joins are just the get the page information.

SELECT lt.lt_title, p_to.page_id 
FROM pagelinks AS pl 
  INNER JOIN page AS p_from 
    ON p_from.page_id = pl.pl_from 
  INNER JOIN linktarget AS lt 
    ON pl.pl_target_id = lt.lt_id 
  INNER JOIN page as p_to 
    ON p_to.page_title = lt.lt_title 
WHERE p_from.page_namespace = 0 
  AND p_from.page_title = "Horse";

By using the query shown above, and limiting it to one, we get

+----------+---------+
| lt_title | page_id |
+----------+---------+
| Allele   |    1911 |
+----------+---------+

And sure enough, inside of the horse wikipedia page, we see a link for alleles:

Inbound Links

This one is a bit harder. Given that we know the lt_title, we then need to all the entries inside of the pagelink where lt_id = pl_target_id. The other join is to get the page information.

SELECT p.page_title, p.page_id
FROM pagelinks AS pl
  INNER JOIN page AS p
    ON p.page_id = pl.pl_from
  INNER JOIN linktarget AS lt
    ON lt.lt_id = pl.pl_target_id
WHERE lt.lt_title = "Horse"
  AND p.page_namespace = 0;

Once again, by using the query above, with a limit of 1, we get

+-------------+---------+
| page_title  | page_id |
+-------------+---------+
| Agriculture |     627 |
+-------------+---------+

Looking at the agriculture page, horse is a link there.

The New Goal

The goal now is simple. We need to make the database as small as possible, while trying to preserve at much data as possible. This data includes:

• The page title.
• All the “alternate” titles (pages that redirect to some other page) of a page.
• All outbound links of a single page.

Considerations

Databases can be big. This “graph” will have around 7 million page nodes. Transforming the Wikimedia database into something smaller will take a considerable amount of time².

1. Which is why I wanted to convert the XML dump into an SQL database, which was one of my previous goals ↩︎

2. My current setup is expected to reduce the database in around a week, even with indexing. ↩︎