MySQL Forums
Forum List  »  Newbie

Searching by using an index table, efficient queries?
Posted by: Frieder Schüler
Date: February 25, 2010 10:11AM

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`)

Options: ReplyQuote

Written By
Searching by using an index table, efficient queries?
February 25, 2010 10:11AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.