CartMetrix - Do you know yours?

12/5/2006

MySQL Slow Query Logging

Add to /etc/my.cnf under [mysqld] stanza

MySQL5

log_slow_queries  =     /var/log/mysqld-slow.log
long_query_time   =     1

MySQL3, MySQL4

log-slow-queries=/var/log/mysqld-slow.log
set-variable    = long_query_time=1

Popularity: 8%

Share This

11/29/2006

ZenCart Orders Total Weight Query

SELECT op.products_quantity * SUM( products_weight )
FROM products p
JOIN orders_products op
WHERE p.products_id
IN (
SELECT products_id
FROM orders_products
WHERE orders_id = 'ORDER_ID'
)
AND op.orders_id = 'ORDER_ID'

Replace ORDER_ID with the orders_id in question.

Requires MySQL 4.1 or better for subselect support

Related Link: Bowflex

Popularity: 14%

Share This

10/23/2006

MySQL Database Performance - ZenCart Sales Report

One of my client's ZenCart installs was loading the server down so much to generate sales reports with the Sales Report v1 contribution that the public side was noticeably less responsive for the couple of minutes it took to build the report. The site has been running for over three years and contains 9k orders, so there is a lot of data to weed through even for a weekly report. A quick look at the slow query log listed the following query as the culprit:

SELECT count( op.products_id ) AS attr_cnt, o.orders_id, opa.orders_products_id, opa.products_options, opa.products_options_values, opa.options_values_price, opa.price_prefix
FROM orders_products_attributes opa, orders o, orders_products op
WHERE o.orders_id = opa.orders_id
AND op.orders_products_id = opa.orders_products_id
AND o.date_purchased >= '2006-10-18T00:00:00'
AND o.date_purchased < '2006-10-19T00:00:00'
AND op.products_id =179
GROUP BY products_options_values
ORDER BY orders_products_id;

Running this query through EXPLAIN shows 20978 rows examined. In this case, thats 20978 rows per day included in the sales report.

The main problem with this query is the ORDER BY clause which cannot utilize an exisiting index. Adding the following index:

ALTER TABLE 'orders_products_attributes' ADD INDEX ( 'orders_products_id' )

limits the rows examined for each query to 68 (results).

This simple change allows the sales report to load in seconds as opposed to minutes with no noticeable lag on the public side.

The create table SQL for the current version of ZenCart (v1.3.5) contains the following index in orders_products_attributes:

...
KEY idx_orders_id_prod_id_zen (orders_id,orders_products_id)
...

This index is used in the query in question. In my tests it was only slightly slower than the single column index above. EXPLAIN showed about 270 rows examined with the two column index on my dataset while the single column index only examined 68 rows.

Popularity: 19%

Share This

8/4/2006

Quick and Dirty MySQL Backup

This is great for a quick backup of all databases before performing maintenance or check/repair.

mysqldump –all-databases -p | gzip > db_dump-YYYYMMDD.gz

Replace YYYYMMDD with Year, Month and Day.

Related Link: payday loans cash advance needs

Popularity: 20%

Share This

2/23/2006

MySQL5 JOIN Syntax Changes

Changes in the SQL parser in MySQL5 have exposed previously unnoticed errors in several open source applications I support recently. These changes are to comply more accurately with the SQL:2003 standard. A good thing. The bad thing is most apps throw a db error when they encounter a JOIN query of this specific form.

Until MySQL 5.0.1 parentheses in a table_reference were simply dropped and join operations were grouped to the left. As of MySQL 5.0.1, nested join operations are allowed, with the JOIN operator having a higher precendence than the comma (,) operator. Queries that happened to work before will produce error now if MySQL doesn’t understand how the tables should be joined. In some cases, result sets could change for some queries. I haven’t seen one of these in action yet, but I have a few example cases in mind.

Queries affected by this change may result in one of the following errors.

Unknown column ‘xxx’ in ‘on clause’
Unknown column ‘xxx’ in ‘where clause’

In several ZenCart installations, I was able to fix the bug by adding the proper parenthesis around the table names included in the JOINs. Other instances of this error were fixed by re-ordering the table_references.

From the MySQL5 documentation:

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

Example:

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column ‘t1.i1′ in ‘on clause’ error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

For full details see:
http://dev.mysql.com/doc/refman/5.0/en/join.html

The ZenCart people are currently working to straighten these bugs out with their forthcoming ZenCart v1.3 release. Patches for the latest two versions are available at http://www.dataweb.no/dm/index.php/Downloads.

Popularity: 15%

Share This

« Previous Page


damonparker.org is proudly powered by WordPress
Entries (RSS) and Comments (RSS).

copyright © 2002-2008 damonparker.org. all rights reserved.

Close
E-mail It