I am programming now a task having a business database over then 20M records. This is actually the table structure:

CREATE TABLE IF NOT EXISTS `business` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rdate` datetime NOT NULL,
`valid` int(1) NOT NULL,
`visible` int(1) NOT NULL,
`lockItem` int(1) NOT NULL,
`parent` int(10) NOT NULL,
`titleENG` varchar(254) COLLATE utf8_bin NOT NULL,
`address` varchar(254) COLLATE utf8_bin NOT NULL,
`city` varchar(254) COLLATE utf8_bin NOT NULL,
`state` varchar(254) COLLATE utf8_bin NOT NULL,
`zipCode` varchar(254) COLLATE utf8_bin NOT NULL,
`country` varchar(254) COLLATE utf8_bin NOT NULL,
`webAddress` varchar(254) COLLATE utf8_bin NOT NULL,
`phone` varchar(254) COLLATE utf8_bin NOT NULL,
`fax` varchar(254) COLLATE utf8_bin NOT NULL,
`contact_name` varchar(254) COLLATE utf8_bin NOT NULL,
`contact_title` varchar(254) COLLATE utf8_bin NOT NULL,
`contact_gender` enum('','male','female') COLLATE utf8_bin NOT NULL,
`company_employee` varchar(254) COLLATE utf8_bin NOT NULL,
`text` text COLLATE utf8_bin NOT NULL,
`bigPic` varchar(254) COLLATE utf8_bin NOT NULL,
`keywords` varchar(254) COLLATE utf8_bin NOT NULL,
`lowerTitle` varchar(256) COLLATE utf8_bin NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `visible` (`visible`),
 KEY `parent` (`parent`),
 KEY `valid` (`valid`),
 KEY `titleENG` (`titleENG`),
 KEY `state` (`state`),
 KEY `country` (`country`),
 KEY `city` (`city`),
 KEY `keywords` (`keywords`),
 FULLTEXT KEY `titleENG_2` (`titleENG`),
 FULLTEXT KEY `lowerTitle` (`lowerTitle`),
 FULLTEXT KEY `phone` (`phone`),
 FULLTEXT KEY `city_2` (`city`),
 FULLTEXT KEY `state_2` (`state`),
 FULLTEXT KEY `business_search` (`lowerTitle`,`phone`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=12409005 ;

A number of my queries i am doing via fulltext, question basically may use via LIKE and taking advantage of lower() functions etc' however with high-speed.

Can someone advice me some suggestions?

All that you should do would be to alter the collation for needed posts. For example:

ALTER TABLE `business` CHANGE COLUMN `city` `city` VARCHAR(150) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL;

You'll be able to make use of the same fulltext search.

  • You do not need INDEX on id as you have one
  • MyISAM is faster with char-s compared to varchar-s
  • If you do not use bin collation, you do not need lower(). Use situation insensitive collation, should you really do not require it situation-sensitive
  • Look at your queries and indexes. Maybe compound indexes on multiple posts will work better then individual indexes (and every index makes writting reduced)
  • What's your write/read ratio? For those who have lots of place/updates, you need to minimize the amount of indexes, too consider moving to InnoDB
  • Offload the entire-text search to Sphinx, Solr or any 3rd party internet search engine (or build full-text index by yourself)
  • visible index appears unnecessary - the worthiness is either or 1, which means index isn't effective, and I am confident you've got no query that filters only on 'visible'