I have a problem of huge load on some large tables.
What I'm trying to do is set a relation to a large table.
There are more than 10,000 records in cities table, 241 records in country table and 64 records in states table.
There is a better way to do this?
Suggestion: When table is huge can we have an ajax loading gif and limit de result to first 500 records.
Can you use some cache, once is fully joined the tables?
Thank you for your time.
SQL
CREATE TABLE IF NOT EXISTS `cities` (
`country_iso` varchar(2) NOT NULL,
`state_iso` varchar(2) NOT NULL DEFAULT '',
`region_id` tinyint(2) unsigned zerofill NOT NULL,
`place_code` varchar(9) NOT NULL,
`name_fr` text NOT NULL,
`name_en` text NOT NULL,
`url` varchar(43) NOT NULL,
`lang` enum('fr','en') NOT NULL DEFAULT 'en',
PRIMARY KEY (`country_iso`,`state_iso`,`place_code`),
UNIQUE KEY `placeCode` (`place_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `country` (
`ISO` varchar(2) NOT NULL,
`nom_fr` varchar(50) NOT NULL,
`nom_en` varchar(50) NOT NULL,
`name` varchar(255) NOT NULL,
`printable_name` int(255) NOT NULL,
PRIMARY KEY (`ISO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `states` (
`country_iso` varchar(2) NOT NULL,
`state_iso` varchar(2) NOT NULL,
`name_fr` varchar(50) NOT NULL,
`name_en` varchar(50) NOT NULL,
`ip_region` varchar(2) NOT NULL,
PRIMARY KEY (`country_iso`,`state_iso`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
PHP
$crud = new grocery_CRUD();
$crud->set_theme('datatables');
$crud->set_table('cities');
$crud->set_relation('country_iso', 'country', 'ISO');
$crud->set_relation('state_iso', 'states', 'state_iso');
$crud->set_subject('Ville');
$crud->required_fields('country_iso','state_iso','region_id','place_code','name_fr','name_en','url','lang');
$crud->columns('country_iso','state_iso','region_id','place_code','name_fr','name_en','url','lang');
$output = $crud->render();
$this->_admin_output($output);
