Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    12

    How to design my search tables

    Hi
    I am using a kind of search table design that is separated from original table. In this design I have one table for all tables of my database that its name is indexer.

    I separated these two because of :
    1. I don't want to explicitly search by column name and just search by text -- You can say that it I can use full text search
    2. I have some columns that are like enumeration type and original text is in a file, not a table in database - You can say that I can use a table for storing enumeration data but I have some efficiency and application limitations and can't use such a idea
    3. I have some columns that are referencing to a table and I store id of them as foreign key - You can say I can join them and then search but because of efficiency I don't want to join tables

    In my design I have these columns :
    id -- primary key
    table_name -- name of table that indexed keywords belong
    search_id -- id of column in original table
    keywords -- fulltext index

    I search each column and its data in keywords by a format like this : [<<column_name:riority>>data]

    for example here is a row on table :
    id table_name search_id keywords
    45 users 421 [<<first_name::2>>michael ][<<last_name::1>>moore][<<salutation_id::3>>Dr Doctor][<<gender::4>>male][<<related_user_id::4>>charles moore Dr male]

    In users table salutation_id and related_user_id are foreign key and gender is enumeration type. I search with LIKE operator using fulltext index on keywords column and find records that I searched for. My problem is I don't know how to use priority in ORDER BY clause.

    If there is another efficient design please offer it.

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Without knowing a great deal more than you've disclosed about what you're doing, I don't know a good way to advise you.

    You say that you don't want to JOIN to improve performance. This implies multiple terabyte databases to me.

    You say that you want to use full-text search, but you've opted to create a table and user generated code instead. Does this mean that your database engine does not support full-text search?

    Help me to help you... I need to understand your problem to offer suggestions on how to solve it!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2008
    Posts
    12
    Thanks Pat
    I'm designing and developing a CRM application that may have very large tables so joining may reduce performance. For example my account table has foreign key to 8 tables and 7 many to many relations.So If I join them to search, It may cause lots of performance problems. Although I'm designing it for multiple DBMS so I may encounter many limitations (e.g. in mysql all tables are innodb that does not support fulltext search so i separated search table and made its engine to myisam)

    Another thing about my application is that it should support multiple language. For example in enumeration type that I mentioned in post, I store numbers in tables and translate these numbers to texts that should be displayed. Imagine if I want to opt to search there is no text in database so I should store text representation somewhere.

    If I need to explain more please tell me, I will post more detailed information about my problem.

    Thanks in advance

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    If you upgrade from MySQL to MariaDB 10.0.5 you can use Full-Text search on InnoDB tables. See this article for details.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •