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)

















---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Reply via email to