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

 · 

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