Re: Benchmark (slightly off topic but oh well) - Mailing list pgsql-performance

> 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)