Hello All,
I have a MYISAM table (employment_summary) with over 6 Lac records. I use it
to do fast keyword searches on employments and want to retrieve the results
grouped by the executive. The executive table is also a huge INNODB table
and has over 6 Lac records. If I do any join operations with
employment_summary and executive table, the search becomes very slow and
sometimes takes over 3 mins to return. To improve the performance, I moved
all the searchable data from executive to employment_summary and tried to
avoid doing any join between MYISAM and INNODB.
But even after this the search is not really fast. For instance, a simple
query below takes a around 50 sec:
select * from employment_summary where (MATCH(title) AGAINST(' +director'
IN BOOLEAN MODE)) group by executive_id limit 0,200;
Running explain on the above query, I realised, it is using the full text
index on title but it is not using the index on executive_id for grouping
the results. If I try to run the same query without doing the text search,
it returns really fast and it is using the index on executive_id column:
select * from employment_summary group by executive_id limit 0,200;
Here is the employment_summary table on which I am trying the above queries.
| employment_summary | CREATE TABLE `employment_summary` (
`id` varchar(32) NOT NULL default '',
`executive_id` varchar(32) NOT NULL default '',
`firstName` text,
`lastName` text,
`title` text,
`job_description` text,
KEY `execIdIndex` (`executive_id`),
KEY `empIdIndex` (`id`),
FULLTEXT KEY `jobDescriptionFullTextIndex` (`job_description`),
FULLTEXT KEY `titleFullTextIndex` (`title`),
FULLTEXT KEY `firstNameIndex` (`firstName`),
FULLTEXT KEY `lastNameIndex` (`lastName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
Is there anything I am missing? How can I make the query use the index for
grouping?
-Harini
