Let’s say that we the tables
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
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
[LIST]
[]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.
[/LIST]
My questions are:
[LIST=1]
[]What is the cruical factor here that would increase searching speed noticeably?
[]And what methods to test this would you recommend?
[*]If i wanted to join more tables in that search, how significantly would joins and the number of joins impact the overall performance?
[/LIST]