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=kolese...@gmail.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
