Hello.
Your query performs a full table scan, because if you match text with '%...' wildcard,
MySQL can't using index. Try to use external full-text
search engines like Sphinx (http://www.sphinxsearch.com/) or Lucene
(http://lucene.apache.org).
> 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.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
