Stop CakePHP 3 from listing all fields in the SELECT query it generates with find()

Question

I'm trying to optimise a large MySQL query. I accidentally found out that a query with all fields listed (SELECT Orders.id AS Orders__id, <...>; the default CakePHP behaviour) takes 4 times longer compared to a query with just SELECT * FROM - 0.324 seconds vs. 0.084 seconds; checked several times.

I'm wondering if I can disable this behaviour. I've tried:

adding 'fields' => '*' to the find() options or calling ->select('*'), but it results in SELECT Orders.* AS Orders__* which throws an SQLSTATE[42000] error.
getting rid of the aliased title with ->select(['*' => '*']) as per query-builder.html#selecting-data, but that results in SELECT * AS * which also throws an error
using ->enableAutoFields(false)

I also tried to Google but I don't even know how to call this

I want the generated query to just be `SELECT * FROM <...>` and not list all of the fields it is about to select — ᴍᴇʜᴏᴠ, May 25 '18 at 12:19
What I am thinking is that you didn messure query times correcty eg. without clearing cache etc. — Antoniossss, May 25 '18 at 12:36
Are you really sure about the time spent? How did you measure that? — ndm, May 25 '18 at 12:36
@ndm got it from the MySQL Workbench "Action Output" https://i.imgur.com/KerEgJC.png, you think is inaccurate? — ᴍᴇʜᴏᴠ, May 25 '18 at 13:01

score 1 · Answer 1 · answered May 25 '18 at 18:03

1

It seems you want to override the benefits of the ORM so I'm going to suggest a method that should not be used in normal operations.

$datasource = ConnectionManager::get('default');
$datasource->execute('SELECT * FROM some_table;');

If you want to hydrate entities you must allow the select statement to alias the fields so the above will not give you entities.

My opinion is that you should use the normal select and optimize your caching strategies for this data.

answered May 25 '18 at 18:03

styks

3,193
1
23
36

I use the `contain` construction *a lot* in this query, so issuing SQL directly is not nearly effective. This may still be an option for someone else however. Thanks, upvoted. By the way, welcome to 1K club haha:) – ᴍᴇʜᴏᴠ May 28 '18 at 08:41

score 0 · Accepted Answer · answered May 28 '18 at 08:40

Apparently, I can't not have the fields listed because of how CakePHP ORM was designed.

So instead, solved by manually whitelisting only the fields I actually need. The query is still relatively fast after all (around 100 ms. according to my measurements).

Stop CakePHP 3 from listing all fields in the SELECT query it generates with find()

2 Answers2

Linked