Daniel wrote:
1. The class i'm using at the moment is very lightweight and if i had to use prepared statements i think it adds bulk to writing SQL.
I'm not sure I agree with you on that point.
Code: Select all
$this->database->getRows("select * from zone_to_geo_zone where geo_zone_id = '" . (int)$this->config->get('flat_geo_zone_id') . "' and country_id = '" . (int)$this->address->getCountryId($this->session->get('shipping_address_id')) . "' and (zone_id = '" . (int)$this->address->getZoneId($this->session->get('shipping_address_id')) . "' or zone_id = '0')")
Would become
Code: Select all
$this->database->getRows(
"select * from zone_to_geo_zone where geo_zone_id = :geoZoneId and country_id = :countryId and (zone_id = :zoneId or zone_id = 0)",
array(':geoZoneId' => $this->config->get('flat_geo_zone_id'),
':countryId' => $this->address->getCountryId($this->session->get('shipping_address_id')),
':zoneId' => $this->address->getZoneId($this->session->get('shipping_address_id'))
));
We don't have to cast (the database knows what the column types are supposed to be), we don't have to write horrible scary long concatenated strings (see how much easier to read the query is), and (perhaps most importantly) we don't have to worry about SQL injection
at all because the query is sent separately from the bound data. Additionally, if we use the same query a lot (eg. for a bulk insert or update), we also save lots of time in the database server when using a prepared statement because the server only needs to parse and optimise the query once (we call execute() multiple times on the statement with different values), instead of every time it's executed.
2. I seem to remember InnoDB stores most things in the RAM and people on shared hosts might have problems. I remeber one US retailer containg me because he was having problems getting the site to run with 100,000 products.
InnoDB uses additional memory to increase performance, but like everything, the amount it uses is configurable (just like MyISAM's key_buffer), so the only time there should be a problem with memory performance would be when the host didn't configure the server properly and it was going into swap, or if the tables aren't indexed properly. The difference between MyISAM and InnoDB in this respect is that when you are using MyISAM, the key_buffer is loaded with just the index of the table. In contrast, when InnoDB buffers its index, it is implicitly buffering the data in the table as well (which makes for very fast data retrieval).
One of the few negative points to note about InnoDB is that whereas MyISAM internally caches COUNT(*), InnoDB can't do that (because of transaction isolation), so SELECT COUNT(*) operations take longer. However, InnoDB is
significantly faster in pretty much all other cases, and with the additional features (foreign key constraints, ACID compliance, transactions), it's a no-brainer which one should be used for any sort of business-critical or high-performance MySQL-based database application (IMO).
)
i recently told my client there's an update coming out soon which will fix the paypal callback bug she has with her site. she's on 0.7.7 i think at the mo.
