Re: Attempt to split big table into smaller one made query slower

[Rick James]

select  p.keyword, p.type,
        sum(titleTag) + sum(descriptionTag) + sum(keywordTag) + sum(h1Tag) + sum(copy) + sum(imgAltTitle) + sum(linkText) as megaSum ,
        g.position, g.siteUrl
    from  reportGoogleKeyword g, pageKeywords2 p
    where  p.dateAdded >= '2010-03-01 07:04:01'
      and  p.dateAdded <= '2010-03-01 07:28:35'
      and  g.clientID = 110
      and  g.dateAdded >= '2010-03-01 07:00:01'
      and  g.dateAdded <= '2010-03-01 07:34:15'
      and  p.keyword = g.keyword
    group by  g.position, g.keyword
    order by  megaSum desc, g.keyword, g.position

"keyword" was ambiguous -- I changed it to be specifically from reportGoogleKeyword.

Note from the EXPLAIN that it starts with a table scan. It would help to have some index to get started with. Let's add an index to each table; not sure which one MySQL would prefer:
For reportGoogleKeyword :
INDEX(clientID, dateAdded)
For pageKeywords2 :
INDEX(dateAdded)
INDEX(keyword, dateAdded)

I'll guess that with those indexes it will start with the new index on reportGoogleKeyword, then join to the other table via (keyword, dateAdded).

Another thing to try: Flip the order of group by g.position, g.keyword. It won't change the semantics any, but might optimize slightly better.

If you did not have any indexes like these on the original (combined) table, then perhaps (probably) you could have sped it up even more without splitting.

key_buffer_size is plenty big for the tables in question. Nothing to recommend there.

megaSum is a weird metric.

Is there only one "keyword" per page? If not, then I would argue that the current (and original?) schema is misdesigned. For a many-to-one relationship,... There should be a table with information about each page; no keywords there. Then another table lists keywords plus page ids; this table would have only two columns.

How big are the keywords? If they average, say, more than 10 characters, another optimization is to 'normalize' out keywords. That is have a table of id <=> keyword, and use those ids instead of VARCHAR(100) in the other table(s).