1

I am building a script that checks a large network of sites for invalid links. The idea is to flag links that continuously show as not available so that they can be cleaned by the administration team.

The basic process is this:

  • Grab a set of pages - approximately 30K per day, not the same 30K each day - and extract all links from the pages
  • Insert Unique URLs into the Links Table
  • Insert records into LinksInPage containing the anchor text for each link as well as the associated LinkID for each link
  • Check the status of each link utilizing HEAD/GET methods and inserting results into the LinkResults table

I am currently planning on building this database structure in an relational database.

Tables:

  • Links

    • LinkID (auto inc)
    • URL (varchar)
  • LinksInPage

    • LinkID (foreign key to Links.LinkID)
    • AnchorText (varchar)
    • Page URL (varchar)
  • LinkResults

    • LinkID (foreign key to Links.LinkID)
    • DateOfCheck (date)
    • StatusCode (int)
    • NumFailures (int)

My question:

  • How can I efficiently handle non-unique links in this situation (I'm open to completely changing how the database is laid out)?

For example, on day one if I check example.com, I insert a record into the Links table. On day two, if I have another page that also links example.com, I don't want a duplicate entry in the Links table, but I do need a record in LinksInPage that points to the same LinkID. At, assuming at least one link per page, 30K links daily I don't think querying for a LinkID at each insert is going to be efficient. Storing everything in memory also seems like it will lead to problem in the future as the number of links gets larger.

I also need to be able to handle the LinkID when inserting link results. Again, looking up the LinkID at each insert seems inefficient.

The plan is to build this utilizing Python.

  • Consider splitting the url into its component parts, protocol, domain, path etc? – Ewan Jun 21 '15 at 10:24
2

looking up the LinkID at each insert seems inefficient

You never tested it, I assume? Sounds like a typical case of premature optimization. Using proper indexing, especially for the URLs, should make a query for an URL roughly as fast as an INSERT into the same table (maybe the INSERT will become a little bit slower since the index has to be updated). And when I understood you correctly, you will need about one SELECT query per INSERT. So the speed will mainly depend on the kind of database you pick, the network latency betweeen you application and the database, and if you create the correct indexes. Running time will not grow by an order of magnitude just because you have to make one lookup for existing elements before you do an INSERT.

So my advice is: implement and measure. If you actually have a bottleneck, you might look for further improvements like utilizing stored procedures, fine tuning your code or your database.

0

As Doc Brown says, joining properly indexed tables is efficient

You make it easier on your application by defining appropriate views and dealing with the views rather than the underlying tables

e.g. (Tested on SQL-SERVER, you didn't specify an RDBMS) See Fiddle

CREATE VIEW URLsInPage AS
    SELECT Links.[Name], LinksInPage.[Page URL], LinksInPage.[AnchorText] 
    FROM Links
    JOIN LinksInPage ON Links.[LinkId] = LinksInPage.[LinkId]
GO

CREATE TRIGGER AddLinksInPage ON URLsInPage INSTEAD OF INSERT
AS
DECLARE @Temp INT

MERGE INTO Links USING Inserted AS New ON Links.[Name] = New.[Name]
WHEN MATCHED THEN 
    UPDATE SET @Temp = 1
WHEN NOT MATCHED THEN
    INSERT ([Name]) VALUES (New.[Name])
OUTPUT inserted.[LinkId], New.[AnchorText] , New.[Page URL] INTO LinksInPage ([LinkId], [AnchorText] , [Page URL]);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.