⚠ 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

adding set_relation() results in a time out from the database



kenvogt

kenvogt
  • profile picture
  • Member

Posted 23 May 2012 - 19:55 PM

I created a form that works fine (with the caveat noted below). I then tried to add set_relation(). It failed with a "324 No data received" error. When I looked at the processlist in mysql, I can see that it was trying to execute a statement that looked like this:


SELECT *
FROM (
`saying`
)
LEFT JOIN `author` AS j4f331e2f ON `j4f331e2f`.`id` = `saying`.`author_id`


Since there are 350,000 rows in 'sayings' and 75,000 in 'authors', I am not surprised that it timed out. Since the form will only show 25 items, why does this statement not include 'LIMIT 0, 25'? Even without set_relation() the form takes over 4 seconds to load, no doubt due to trying to load 350,000 rows before it processes them.

web-johnny

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

Posted 24 May 2012 - 06:01 AM

Do you use datatables theme? The datatables theme works with client-side scripting only. This means that ALL the data is loaded once.

If you have a huge amount of data you have to use flexigrid theme.

kenvogt

kenvogt
  • profile picture
  • Member

Posted 24 May 2012 - 16:03 PM

No, I am using the default theme.

kenvogt

kenvogt
  • profile picture
  • Member

Posted 24 May 2012 - 16:16 PM

I tried adding:

->set_theme('flexigrid')

and I got a 500 error.

In case you are wondering, my directory tree is like so:
root
[indent=1]application[/indent]
[indent=1]assets[/indent]
[indent=2]grocery_crud[/indent]
[indent=3]themes[/indent]
[indent=4]datatables[/indent]
[indent=4]flexigrid[/indent]

(Sorry about the repeated edits, I messed up the indenting.)

kenvogt

kenvogt
  • profile picture
  • Member

Posted 25 May 2012 - 23:37 PM

This is turning out to be a dropdead problem for me. Is there any explanation for sending a query without a LIMIT parameter?

kenvogt

kenvogt
  • profile picture
  • Member

Posted 26 May 2012 - 00:18 AM

And why does ->set_theme() cause a 500 error?

kenvogt

kenvogt
  • profile picture
  • Member

Posted 28 May 2012 - 15:22 PM

I really need answers to these questions.

web-johnny

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

Posted 28 May 2012 - 20:27 PM

Probably the ->set_theme() causes 500 error as you have a timeout (datatables has client-side scrtipting so it tries to retreive ALL the data at once). The query without a LIMIT it is a query that I count with num_rows and not with count_all_results as I had some problems with the grouping results. Either way is the exact same thing. The only difference is that when you have num_rows you require more memory to the database as it stores the dump result to the memory.

There is no way to have COUNT(*) with grouping so I chose this solution. If you have problem with this you can change it from the application/models/grocery_crud_model.php (145-161 line) and change the code:


function get_total_results()
{
//set_relation_n_n special queries. We prefer sub queries from a simple join for the relation_n_n as it is faster and more stable on big tables.
if(!empty($this->relation_n_n))
{
$select = "{$this->table_name}.*";
$select = $this->relation_n_n_queries($select);

$this->db->select($select,false);
return $this->db->get($this->table_name)->num_rows();
}
else
{
$this->db->from($this->table_name);
return $this->db->get()->num_rows();
}
}


with something like this:


function get_total_results()
{
return $this->db->count_all_results($this->table_name);
}

kenvogt

kenvogt
  • profile picture
  • Member

Posted 28 May 2012 - 21:29 PM

I made the change you suggested and now it times out on this:

SELECT saying . * , (
SELECT GROUP_CONCAT( DISTINCT category.name )
FROM category
LEFT JOIN category_saying ON category_saying.saying_id = category.id
WHERE category_saying.category_id = saying.id
GROUP BY category_saying.category_id
) AS categories
FROM (
`saying`
)

web-johnny

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

Posted 28 May 2012 - 22:05 PM

Just don't have the relation_n_n column at your list and it will probably work.
For example if you have:

function film_management()
{
$crud = new grocery_CRUD();

$crud->set_table('film');
$crud->set_relation_n_n('actors', 'film_actor', 'actor', 'film_id', 'actor_id', 'fullname','priority');
$crud->set_relation_n_n('category', 'film_category', 'category', 'film_id', 'category_id', 'name');

$crud->columns('title', 'description', 'actors' , 'category' );
$crud->fields('title', 'description', 'actors' , 'category' ,'release_year', 'rental_duration', 'rental_rate', 'length', 'replacement_cost', 'rating', 'special_features');

$output = $crud->render();

$this->_example_output($output);
}


just change it to :


function film_management()
{
$crud = new grocery_CRUD();

$crud->set_table('film');
$crud->set_relation_n_n('actors', 'film_actor', 'actor', 'film_id', 'actor_id', 'fullname','priority');
$crud->set_relation_n_n('category', 'film_category', 'category', 'film_id', 'category_id', 'name');

$crud->columns('title', 'description'); //<-- I just removed the 'actors' and 'category'
$crud->fields('title', 'description', 'actors' , 'category' ,'release_year', 'rental_duration', 'rental_rate', 'length', 'replacement_cost', 'rating', 'special_features');

$output = $crud->render();

$this->_example_output($output);
}


If you want the column at the list you have to optimize the query by using set_model or change the model of grocery CRUD.

kenvogt

kenvogt
  • profile picture
  • Member

Posted 28 May 2012 - 22:13 PM

Ok, thank you for investigating.

kenvogt

kenvogt
  • profile picture
  • Member

Posted 30 May 2012 - 19:12 PM

[quote name='web-johnny' timestamp='1338236857' post='2036']
{Use} something like this:


function get_total_results()
{
return $this->db->count_all_results($this->table_name);
}

[/quote]

After working through some other issues, I did that which is noted above but it is still hanging up on the statement noted at the beginning. Is there any other place that this statement gets generated other than "function get_total_results()"?

kenvogt

kenvogt
  • profile picture
  • Member

Posted 30 May 2012 - 19:43 PM

Actually, the query in question is not exactly the same. Here is what PROCESSLIST tells me:


[color=#000000][font=sans-serif][size=3][left]Automatically appended backtick to the end of query![/left][/size][/font][/color]
[color=#000000][font=Verdan, Arial, Tahoma][left][color=#990099][b]SELECT[/b][/color] [color=#FF0000]COUNT[/color][color=fuchsia]([/color] [color=fuchsia]*[/color] [color=fuchsia])[/color] [color=#990099][b]AS[/b][/color] [color=#008000]`numrows`[/color]
[color=#990099][b]FROM[/b][/color] [color=fuchsia]([/color][/left][/font][/color]
[indent=1][color=#000000][font=Verdan, Arial, Tahoma][left][color=#008000]`saying`[/color][/left][/font][/color][/indent]
[color=#000000][font=Verdan, Arial, Tahoma][left][color=fuchsia])[/color]
[color=#990099][b]LEFT[/b][/color] [color=#990099][b]JOIN[/b][/color] [color=#008000]`author`[/color] [color=#990099][b]AS[/b][/color] j4f331e2f [color=#990099][b]ON[/b][/color][color=#008000]`j4f331e2f`[/color][color=fuchsia].[/color][color=#008000]`id`[/color] [color=fuchsia]=[/color] [color=#008000]``[/color][/left][/font][/color]

kenvogt

kenvogt
  • profile picture
  • Member

Posted 30 May 2012 - 23:08 PM

I got it to stop failing by commenting out the following from ./application/library/grocery_crud.php (around line 443):

if(!empty($this->relation))
foreach($this->relation as $relation)
$this->basic_model->join_relation($relation[0],$relation[1],$relation[2]);


What problems might this cause?

kenvogt

kenvogt
  • profile picture
  • Member

Posted 07 June 2012 - 18:29 PM

Will this hack of mine cause any problems?

web-johnny

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

Posted 08 June 2012 - 23:29 PM

Yes without this the set_relation will not work at the list.