Searching by using an index table, efficient queries?

[Frieder Schüler]

Hi I have big data table with lot of data (10k rows, 30 columns) the structure is like:

id | name | decription | randomfield
1 | Firstname Lastname | This is a wonderful description | some text here
2 | Other Name | Another description | some other text data

This is my index table:

foreign_id | field | keyword
1 | name | firstname
1 | name | Lastname
1 | description | wonderful
1 | description | description
1 | randomfield | some
1 | randomfield | text
1 | randomfield | here
2 | name | other
2 | name | name
2 | description | wonderful
2 | description | description
2 | randomfield | some
2 | randomfield | other
2 | randomfield | text
2 | randomfield | here

Searchquery (a searchfield in a PHP application): "randomfield: some text". Which searches all entries where the two words "some" and "text" are in the "randomfield" field.

At the moment the application will split this searchquerie into two seperate queries for the index table

1. SELECT foreign_id WHERE field = randomfield AND keyword = some
2. SELECT foreign_id WHERE field = randomfield AND keyword = text

I select all the id's for query 1 and then for query 2, then the application merges the two resultlist to a single one.

But is there any faster / better implementation possible? Can I use a single query for this problem? I think I will need some inner joins or not? Is this faster or slower??

At the moment I use a innoDB Table with an single index over all three fields in the index table.

CREATE TABLE IF NOT EXISTS `address_index` (
`keyword` varchar(200) NOT NULL DEFAULT '',
`field` varchar(50) NOT NULL DEFAULT '',
`address_id` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`keyword`,`field`,`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;