List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:August 1 2009 2:29pm
Subject:Table advice.
View as plain text  
I have a database that I am (will) be using to track URL's. The table
structure looks like this:

CREATE TABLE event
(
  eid           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  timestamp     INT(10) UNSIGNED NOT NULL DEFAULT 0,
  ip            INT(10) UNSIGNED NOT NULL DEFAULT 0,
  fqdn          VARCHAR(255),
  domain        VARCHAR(63),
  tld           VARCHAR(63),
  action        VARCHAR(4),
  request       TEXT,
  referrer      TEXT,
  client        VARCHAR(255),
  INDEX eid (eid),
  INDEX timestamp (timestamp),
  INDEX ip (ip),
  INDEX fqdn (fqdn),
  INDEX domain (domain),
  INDEX tld (tld)
);

The is no real logic behind the indexes, the table was hobbled
together looking at examples. Currently I am trying queries on about
300 million records and the results are pretty crappy. for example, a
query like this:

select domain,count(domain) as count from event where domain like
'%facebook%' group by domain order by count desc;

takes about 5 minutes to complete.

Most of the queries will be like that above but probably with
additional filters like date constraints or IP constraints or a
mixture of both. I can also see searches through the requests for
filetypes etc.

Any suggestions or comments would be appreciated.

Thanks.
Thread
Table advice.Paul Halliday1 Aug
  • Re: Table advice.Alexander Kolesen1 Aug
    • RE: Table advice.Gavin Towey3 Aug