CODEWITHSUNDEEP
c#asp.netmysqlfull-text-search
Steve
Steve

Reputation: 3703

MySQL indexing for searching multiple columns

I am curious to find out what people think, we have a very small development team and no real (hard-core) database expertise onsite.

We are scoping a new project that has a requirement of “intelligent search” what this means is they have specified ambiguous searching, for example:

A person record (held within innodb due to transactional requirement)

  • Firstname (varchar)
  • Lastname (varchar)
  • EmailAddress (varchar)
  • Lastlogin (datetime – updated on each login)
  • LastOrder (datetime – updated when an order is accepted)

They want to be able to search on any or multiple fields including ranges for both date columns and partial completion for varchars, for example:

  • Firstname + lastOrder
  • “Steve” would return all names begging with Steve such as “Steve and Steven” (like?)
  • Lastname + EmailAddress + LastLogin
  • Lastlogin BETWEEN 2011-01-01 AND 2012-01-01
  • Lastorder < 2012-01-01

I understand some of those examples are rather specific and ‘should’ always only return one row but it is an example of the requirement not the exact structure.

We are deciding how best to implement it and nobody can agree (as usual!) I would be interested to hear how other people achieve this functionally and why they opted for that solution.

  1. Index every column – not really ideal and large memory usage
  2. Composite indexes – cannot predict the order or number of columns to search
  3. Create an entry within a MyISAM and use FULLINDEX e.g. “MyFirstName MyLastName [email protected]” – downside unable to compare ranges of dates
  4. Use an indexing service such as lucene.net or similar (hard to implement on our 3 web facing IIS servers and similar issue to #3)
  5. Views to hold variations of data – downside, need indexing and more memory / CPU usage for no real benefit

  6. Clunky queries:

    SELECT
(Firstname LIKE “Steve%”) as fn_matched_data
(Lastlogin BETWEEN 2011-01-01 AND 2012-01-01) as ll_matched_data
FROM TABLE
WHERE Firstname LIKE “Steve%” OR Lastlogin BETWEEN 2011-01-01 AND 2012-01-01

I understand there are conflicting specifications – wanting to search everything as one while at the same time applying ranges etc. I really want to tell the business we are not google and to lower their expectations on the search requirements.

Thanks in advance.

Upvotes: 2

Views: 313

Answers (1)

Chris Shain
Chris Shain

Reputation: 51369

Sounds like you ought to consider a specialized external search solution like SOLR

Upvotes: 1

Related Questions