GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Keeping searchable varchar fields in a separate table impact on performance?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Keeping searchable varchar fields in a separate table impact on performance?

    Let's say that we the tables

    Code:
    CREATE  TABLE IF NOT EXISTS user (
      id INT NOT NULL ,
      first_name VARCHAR(100) NULL ,
      last_name VARCHAR(100) NULL ,
      birth_date DATETIME NULL ,
      email VARCHAR(255) NULL ,
      password VARCHAR(255) NULL ,
      hash VARCHAR(255) NULL ,
      activation_code VARCHAR(255) NULL ,
      PRIMARY KEY (id) ,
    ENGINE = InnoDB;
    
    CREATE  TABLE IF NOT EXISTS article (
      id INT NOT NULL ,
      user_id INT NOT NULL ,
      date_created DATETIME NULL ,
      date_from DATETIME NULL ,
      date_to DATETIME NULL ,
      lat DOUBLE NULL ,
      long DOUBLE NULL ,
      PRIMARY KEY (id, user_id) ,
      INDEX fk_story_user1_idx (user_id ASC) ,
      CONSTRAINT fk_story_user1
        FOREIGN KEY (user_id )
        REFERENCES mydb.user (id )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB

    If we were to separate the title and description data into another table like this, how significantly would searching improve if we had 10,000,000 articles.

    Code:
    CREATE  TABLE IF NOT EXISTS mydb.article_content (
      article_id INT NOT NULL ,
      title VARCHAR(50) NULL ,
      description TEXT NULL ,
      PRIMARY KEY (story_id) ,
      UNIQUE INDEX article_id (article_id ASC) ,
      CONSTRAINT fk_story_content_article1
        FOREIGN KEY (article_id)
        REFERENCES article (id )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB
    A few notes:
    • by searching i mean full-text search
    • title: varchar(50), description: TEXT
    • mysql version 5.5
    • storage engine used innoDB ( So no fulltext indexing support )
    • There will be a load balancer setup somewhere later in the project.

    My questions are:
    1. What is the cruical factor here that would increase searching speed noticeably?
    2. And what methods to test this would you recommend?
    3. If i wanted to join more tables in that search, how significantly would joins and the number of joins impact the overall performance?






    Last edited by keeperhood; 04-16-2013, 02:21 PM.

  • #2
    InnoDB has fulltext support in 5.6.

    I don't think you improve performance by splitting the table. Due to per-row storage overhead, less data fits in RAM, and you have the joining overhead (which is low). You really have to measure this.

    Comment

    Working...
    X