I ran into something interesting this morning while trying to patch some bugs in my blogger that I thought I should share. In short, you shouldn't use GROUP BY statements in a VIEW if you intend to benefit from any indexes on the original columns. Basically, the GROUP BY nullifies the index.
To explain further, I'll include the example I was working on here.
I have a caching table called c_blog that's populated by a more complex query on blog post creation or comments. That table has the following structure:
CREATE TABLE c_blog (
id INT UNSIGNED NOT NULL,
title VARCHAR(255),
content MEDIUMTEXT,
music VARCHAR(255),
created DATETIME,
modified DATETIME,
comments INT UNSIGNED NOT NULL,
private BOOL,
worksafe BOOL,
favourite BOOL,
allowComments BOOL,
UNIQUE(id),
FULLTEXT(title,content),
KEY(created),
KEY(private),
KEY(worksafe),
KEY(favourite),
KEY(allowComments)
);
Note the FULLTEXT index on title and content. I use this in my search section to get sorted results based on relavence.
However, security is handled by means of a view. I find out what kind of user you are, and select the appropriate view to use so the right people see the right stuff. However, the v_blog_private view looked like this:
CREATE OR REPLACE VIEW `v_blog_private` AS
SELECT
v.id,
v.created,
v.modified,
v.content,
v.title,
v.music,
v.comments,
v.private,
v.worksafe,
v.favourite,
v.allowComments,
l.user
FROM
v_blog v
LEFT JOIN
blog_lkp_blogUser l ON l.blog = v.id
GROUP BY
v.id;
And running that view in my search section blew up with the following error:
Can't find FULLTEXT index matching the column list
Now, clearly, the FULLTEXT index was there. The problem comes with the fact that the GROUP BY statement in the view was generating a completely different result set that was in itself not indexed.
The solution was simple enough, just move the GROUP BY out of the view and into the actual query calling the view. But the problem confused me enough that I figured I should post it here for others to find.
