Pre-Define Database Table

[matt smith]

A part of my web app requires maximum performance. I notice when I use anything other than $db->exec, F3 makes a SHOW columns call. I realize why it's doing that. What I wanted to know though is can I define a class which describes the table, so a SHOW columns call is not necessary?

I also tried the Cortex add-on and have the table defined in a class, but it also still makes a SHOW columns call.

I know that such calls can get cached by mysql, I would still like to alleviate the unnecessary overhead since my schema will not be changing. Also, while we're at it, can you tell F3 to only bring back certain fields?

Thanks.

[bcosca]

A part of my web app requires maximum performance. I notice when I use anything other than $db->exec, F3 makes a SHOW columns call. I realize why it's doing that. What I wanted to know though is can I define a class which describes the table, so a SHOW columns call is not necessary?

I also tried the Cortex add-on and have the table defined in a class, but it also still makes a SHOW columns call.

The 4th argument of the SQL mapper constructor is a TTL to give the schema detector a hint about how often a SHOW COLUMNS call is issued.

 

I know that such calls can get cached by mysql, I would still like to alleviate the unnecessary overhead since my schema will not be changing. Also, while we're at it, can you tell F3 to only bring back certain fields?

The SQL mapper has a 3rd argument for specifying only the fields you need.

[Emmanuel]

can you tell F3 to only bring back certain fields?

http://fatfreeframework.com/sql#schema

$columns = $db->schema('mytable', 'name;age'); // only interested in these 2 columns

 

[ikkez]

When you enable the CACHE, those show columns calls are all cached for 60sec by default.

[matt smith]

Thanks for the cache info. That's good to know.

I also noticed that after an INSERT (Save) that a SELECT call is made. Is there a way to prevent that SELECT call being made? When I'm inserting analytics at a fast clip, I would like it to just make the INSERT call and not anything unnecessary for that scenario.

Thanks again.

[bcosca]

That SELECT call is for determining the lastinsertrecord() and there isn't a way of getting around that if you have an autoincrement field.

[matt smith]

In cursor.php, the load function doesn't support passing a TTL. Is there a rationale for this?

I modified it and cortex.php's load function to support taking TTL and passing it to find and it works fine, but I don't know if you want to add this in there or not, since I'm not sure why it's not currently supported.

Thanks.

[bcosca]

Code improvements in the form of Git pull requests are welcome and much appreciated.

[bcosca]

Anyway, this oversight has been committed to the repo. See dev branch.

[matt smith]

I was just getting it together. You're too fast :-). Thanks for adding that.