pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: PFC <lists@boutiquenumerique.com>
To: Mitch Pirtle <mitch.pirtle@gmail.com>
To: performance pgsql <pgsql-performance@postgresql.org>
Subject: Re: Benchmark (slightly off topic but oh well)
Date: Fri, 11 Feb 2005 19:22:35 +0100
Message-ID: <opsl1pbxejth1vuj@musicbox> (raw )
In-Reply-To: <330532b60502102122638d887@mail.gmail.com >
References: <20050210054910.76921.qmail@web50005.mail.yahoo.com >
<A07E1E9E-7B66-11D9-84A4-000D9366F0C4@torgo.978.org >
<330532b60502102122638d887@mail.gmail.com >
> For example, I am a developer of Mambo, a PHP-based CMS application,
> and am porting the mysql functions to ADOdb so I can use grown-up
> databases ;-)
Just yesterday I "optimized" a query for a website running MySQL. It's
the 'new products' type query :
SELECT product_id, pd.product_name, p.price, COALESCE( s.specials_price,
p.price ) as real_price
FROM products p, products_descriptions pd LEFT join specials s ON
(p.product_id = s.product_id)
WHERE p.product_id = pd.product_id
AND pd.language_id=(constant)
AND p.product_visible=TRUE
AND s.is_active = TRUE
ORDER BY p.date_added DESC LIMIT 6
With ~100 products everything went smooth, about 0.5 ms. I decided to
test with 20.000 because we have a client with a large catalog coming.
Wow. It took half a second, to yield six products. Note that there are
appropriate indexes all over the place (for getting the new products, I
have an index on product_visible, date_added)
I tested with Postgres : with 100 products it takes 0.4 ms, with 20.000
it takes 0.6 ms...
Postgres needs a bit of query massaging (putting an extra ORDER BY
product_visible to use the index). With MySQL no amount of query rewriting
would do.
I noted sometimes MySQL would never use a multicolumn index for an ORDER
BY LIMIT unless one specifies a dummy condition on the missing parameter.
So I had to split the query in two : fetch the six product_ids, store
them in a PHP variable, implode(',',$ids), and SELECT ... WHERE product_id
IN (x,y,z)
UGLY ! And a lot slower.
Note this is with MySQL 4.0.23 or something. Maybe 4.1 would be faster.
Here's the URL to the site. There is a query log if you wanna look just
for laughs. Note that all the products boxes are active which makes a very
long page time... There are 42000 fictive products and about 60 real
products. Don't use the search form unless you have a good book to read !
You can click on "Nouveautés" to see the old "new products" query in
action, but please, only one people at a time.
http://pinceau-d-or.com/gros/product_info.php?products_id=164
Ah, you can buy stuff with the test version if you like, just don't use
the credit card because ... it works ;)
This is the un-messed-up version (production) :
http://pinceau-d-or.com/product_info.php?products_id=164
If some day I can recode this mess to use Postgres... this would be nice,
so nice... the other day my database went apeshit and in the absence of
foreign keys... and the absence of PHP checking anything... !
test=# CREATE TABLE suicide (id INT NOT NULL, moment TIMESTAMP NOT NULL);
CREATE TABLE
test=# INSERT INTO suicide (id,moment) VALUES (0,now());
INSERT 6145577 1
test=# INSERT INTO suicide (id,moment) VALUES (0,0);
ERREUR: La colonne <<moment>> est de type timestamp without time zone
mais l'expression est de type integer
HINT: Vous devez reecrire l'expression ou lui appliquer une
transformation de type.
test=# INSERT INTO suicide (id,moment) VALUES (NULL,1);
ERREUR: La colonne <<moment>> est de type timestamp without time zone
mais l'expression est de type integer
HINT: Vous devez reecrire l'expression ou lui appliquer une
transformation de type.
test=# INSERT INTO suicide (id,moment) VALUES (NULL,now());
ERREUR: Une valeur NULL dans la colonne <<id>> viole la contrainte NOT
NULL
test=# SELECT * FROM suicide;
id | moment
----+----------------------------
0 | 2005-02-11 19:16:21.262359
mysql> CREATE TABLE suicide (id INT NOT NULL, moment DATETIME NOT NULL);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO suicide (id,moment) VALUES (0,now());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO suicide (id,moment) VALUES (0,0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO suicide (id,moment) VALUES (NULL,1);
ERROR 1048: Column 'id' cannot be null
mysql> INSERT INTO suicide (moment) VALUES (now());
Query OK, 1 row affected (0.00 sec)
hey, did I specify a default value ?
mysql> SELECT * FROM suicide;
+----+---------------------+
| id | moment |
+----+---------------------+
| 0 | 2005-02-11 19:17:49 |
| 0 | 0000-00-00 00:00:00 |
| 0 | 2005-02-11 19:18:45 |
+----+---------------------+
3 rows in set (0.00 sec)
Message-ID: <opsl1pbxejth1vuj@musicbox>
Permalink: ../opsl1pbxejth1vuj@musicbox/
Also on: postgresql.org/message-id/opsl1pbxejth1vuj@musicbox
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@boutiquenumerique.com, mitch.pirtle@gmail.com
Subject: Re: Benchmark (slightly off topic but oh well)
In-Reply-To: <opsl1pbxejth1vuj@musicbox>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox