7 replies to this topic

[bruyndoncx]

I looked at the development guidelines with the examples for database calls.
I understand why you want to bind variables, but I wonder about the tables.
Isn't it double mapping if you have the table names defined in includes/database_tables and then use that same one for the bind value and not directly including the TABLE_NAME in the sql statement.
So what is really the benefit of doing it this way ?

[Harald Ponce de Leon]

Hi Carine..

The intentions here are not to break or not to concatenate the SQL string.

This is much better:

$osC_Database->query('select * from :table_name order by field_1');

than:

$osC_Database->query('select * from ' . TABLE_NAME . ' order by field_1');

[bruyndoncx]

ok, got it - so it is just for easier syntax to avoid errors.

[cannuck1964]

Hi,

I have not looked at the code base in a few months, but plan to do so when the next release is available. I hope that DB optimizations are occuring as well, for example, when getting a product based on an id, I do hope that the query is limited by 1 (LIMIT 1) so that once the value is found, the search stops and returns the value.

For smaller shops this may not be a huge issue, but when the shop has thousands of products and/or is heavily trafficed, then this will help the lower DB load. As well, it is just good code technique, as they should never be more then 1 row with the unique product id, and to implicitly state this in the query will make this association more clear and be more correct when the query is run.

cheers,
Peter M

[Harald Ponce de Leon]

Hi Peter..

Is that not a common myth? I am sure database servers (MySQL and co.) are intelligent enough to stop the query execution as soon as the primary key record is found, due to primary keys being unique in a database table.

[cannuck1964]

Hi Harald,

I am not positive, on that, but I will go to the MySQL site today and do some reading on this will get any relevant info and post it here later....

cheers,
Peter M

[Harald Ponce de Leon]

I had a quick look before posting but couldn't find anything concrete. Just this general blogging entry stating:

Quote

For performance reasons, unless your query is guaranteed to only have a single result (like selecting based on a primary key), you should add LIMIT 1 to the end of your query.

[cannuck1964]

This does make sense to me and have yet to find anything of relevance other then info on the group by and limit and indexing of tables. I am not an expert in MySQL optimizations etc and will go with what you have found. I generally did this out of habit (bad?) and did not feel it would hurt the query, and at the least restrict the query from doing complete table scans....


cheers,
Peter M