MySQL Forums :: Newbie :: Searching by using an index table, efficient queries?


Advanced Search

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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Options: ReplyQuote


Subject Written By Posted
Searching by using an index table, efficient queries? Frieder Schüler 02/25/2010 10:11AM
Re: Searching by using an index table, efficient queries? Guelphdad Lake 02/25/2010 02:40PM
Re: Searching by using an index table, efficient queries? Rick James 02/25/2010 04:47PM


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.