Can this be optimized (probably a newbie question)

[outraegis]

I've got a big databases (1.4M records) with about 40 fields. It's doing very enhanced log analysis. One of the fields is the title of an article, defined as a VARCHAR(255). The field is indexed. The engine is InnoDB.

When I do the following query, the process takes forever:

SELECT articleTitle, count(articleTitle) from logdata group by articleTitle order by count(articleTitle) desc

So, what can I do to make this (and all the searches like it) fast enough to be useful? This database is 1.4M records now, but it will eventually grow to 20M records+ shortly.

Thanks in advance!

Raegis

 

[r937]

hmmm, i wonder if two articles are allowed to have the exact same title...

r937.com | rudy.ca

 

[outraegis]

> hmmm, i wonder if two articles are allowed to have the exact same title...

It is possible, but very unlikely. However, there are certainly many instances of the article title in the table since we're looking at number of reads.

Are you implying something here (if so, I'm not gettin' it)?

Thanks!

 

[r937]

implying? me? ;-)

how does this perform --

select article_id
     , count(*) as articles
  from logdate
group
    by article_id

where article_id is the primary key

r937.com | rudy.ca

 

[outraegis]

I tried it with articleTitle (the full title) rather than articleID because, for historical reasons, there's not a universal ID in the database (I could construct one by a concat, but I'm expecting that'd be slow as well).

Also, articleTitle is a key, but not the primary key. The incrementing log entry id is the primary key.

I know, for example, when I run:

select year, count(*) from logdata group by year order by count(*) desc

that the result is about 8 seconds. Now, clearly year is 4 chars, while the article title is much bigger. But given that I'm indexing it, is there something else I can do to make this (and probably other related queries, like authorName) run faster?