⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Huge page load on large tables on set_relation



loginet

loginet
  • profile picture
  • Member

Posted 13 March 2012 - 15:23 PM

Hi,

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);

web-johnny

web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 20 March 2012 - 21:23 PM

An AJAX set_relation would definitely solve your problem. I tried to implement ajax-chosen to the version 1.2 and I had EVERY functionality ready , however it just had lot of BUGS ( /topic/157-custom-message-action/page__view__findpost__p__850 ) . The bad news is that I will probably not have ui autocomplete at the version 1.2.1 . Perhaps in version 1.2.2 ? I don't know, it always depends on how much time I will have and if I receive any donation till then.

Kenta Saito

Kenta Saito
  • profile picture
  • Member

Posted 23 March 2012 - 01:13 AM

Hi, loginet.

It may helps taransitionaly.

"set_relation" with HUGE table goes bad. Try this picker.
/topic/241-set-relation-with-huge-table-goes-bad-try-this-picker/