/    Sign up×
Community /Pin to ProfileBookmark

Searching a complicated mysql database

Copy linkTweet thisAlerts:
Aug 22.2011

Dear all,

I have quite a complex problem to deal with:

My database uses a lot of InnoDB related tables. I have a database with objects that need to be searched for a) full text matches b) relational matches.

At the moment I have a sphinx instance indexing the title and description of these objects. They also have a lot of HABTM relationships with other objects and these relationships need to be part of the search filter.

I thought of having a separate “keywords” table which would have a cached counter in it to see the most common keywords (which is data I would like to have). Sort of like a tagging system.

I was wondering what the best way of doing this is.

to post a comment
PHP

2 Comments(s) ↴

Copy linkTweet thisAlerts:
@blue-eye-labsauthorAug 22.2011 — I wonder if it would be a better idea to create a mirror table under MyISAM holding only the fields that need to be full-text searched. This could also concatenate all the tags and habtm data into other fields so that they would be easily filterable in one query not several...
Copy linkTweet thisAlerts:
@UltimaterAug 23.2011 — I'd use a combination of all of the above.

Try to break the full-scale problem down into smaller bits and pieces.

Don't be afraid to use subqueries and/or temporary tables to assist you with your searches.

Searches tend to be complicated beasts at the heart.

Many softwares use a dedicated table for search results and assign each search its own search id. For example on these forums: http://www.webdeveloper.com/forum/search.php?searchid=4355095

Some use a temporary tables such as metro.net

They do such when trying to find a bus route path between two points.

Its obvious they use a temporary table since if you try to reload your page after 15 or so minutes it says the table doesn't exist.

I'd even recommend a cronjob to keep your "cached" common keywords search results up to date.

Yet try to avoid such solutions if the data being cached will constantly be out of date and tick off the end user resulting-in no results.

Be creative.
×

Success!

Help @blue-eye-labs spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.28,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...