While I'm really fond of SQL for various things, I don't think your current table structure lends itself to good searching.

I see two potential approaches:

• Restructure your "keywords" table so it contains tuples of (user,keyword). If you have the appropriate indices, you can then search based on either user or keyword and find the "best" matches:

  select
      user, count(*) as hits
  from ...
  where keyword in ('Architect', 'Sydney', 'Small')
  group by user
  order by hits desc
  [download]

• If you want a quick/better solution specialized for search, which already knows about (English) stemming etc., consider looking at ElasticSearch. I don't know how applicable it is for simple keyword search, but at least for documents a little bit larger, it seems to attract a lot of followers.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.