MySQL Forums
Forum List  »  German

Re: Produktdatenbank mit Produkteigenschaften
Posted by: Thomas Wiedmann
Date: March 12, 2012 06:30AM

Hallo Simon,
hiermit werden alle Attribute per "AND" verknüpft.
SELECT a1.parts_id
  FROM ( SELECT parts_id 
           FROM att
          WHERE val_s = 'blau'
            AND attnames_id = 1
       ) a1
  JOIN ( SELECT parts_id 
           FROM att
          WHERE val_f = 1e+23
            AND attnames_id = 2
       ) a2
    ON a1.parts_id = a2.parts_id
  JOIN ( SELECT parts_id 
           FROM att
          WHERE val_s = 'vier-beinig'
            AND attnames_id = 3
       ) a3
    ON a2.parts_id = a3.parts_id    
  JOIN ( SELECT parts_id 
           FROM att
          WHERE val_f > 4000
            AND attnames_id = 5
       ) a4
    ON a3.parts_id = a4.parts_id;
+----------+
| parts_id |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

mysql>

Zusätzlich die Spalte TEXT anzeigen
CREATE TABLE IF NOT EXISTS `parts` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`man_id` int(10) unsigned NOT NULL, 
`cat_id` int(10) unsigned NOT NULL, 
`herstellernr` varchar(150) COLLATE utf8_unicode_ci NOT NULL, 
`seotext` varchar(150) COLLATE utf8_unicode_ci NOT NULL, 
`text` text COLLATE utf8_unicode_ci NOT NULL, 
`stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
PRIMARY KEY (`id`), 
UNIQUE KEY `man_id` (`man_id`,`herstellernr`), 
UNIQUE KEY `seotext` (`seotext`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; 

INSERT INTO parts VALUES
( 2, 3, 4, 'h-nr', 'seo-text', 'text', CURRENT_TIMESTAMP );

SELECT a1.parts_id, p.text
  FROM ( SELECT parts_id 
           FROM att
          WHERE val_s = 'blau'
            AND attnames_id = 1
       ) a1
  JOIN ( SELECT parts_id 
           FROM att
          WHERE val_f = 1e+23
            AND attnames_id = 2
       ) a2
    ON a1.parts_id = a2.parts_id
  JOIN ( SELECT parts_id 
           FROM att
          WHERE val_s = 'vier-beinig'
            AND attnames_id = 3
       ) a3
    ON a2.parts_id = a3.parts_id    
  JOIN ( SELECT parts_id 
           FROM att
          WHERE val_f > 4000
            AND attnames_id = 5
       ) a4
    ON a3.parts_id = a4.parts_id
  JOIN parts p
  ON p.id = a4.parts_id;
+----------+------+
| parts_id | text |
+----------+------+
|        2 | text |
+----------+------+
1 row in set (0.00 sec)

mysql>

Performance ist ein anderes Thema, das hat weniger was mit komplizierten Abfragen, als mit günstigen oder ungünstigen Index und Parametern (als auch mit Hardwareleistung) zu tun.


Für Performance bitte einen neuen Thread aufmachen, falls es ein Thema ist.

=============================================
Um bei Performancefragen richtig helfen zu können, bedarf es folgender Informationen (für alle beteiligten Tabellen):

* SHOW CREATE TABLE tbl; -- liefert engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'; -- liefert die Tabellengrößen
* EXPLAIN SELECT ...; -- EXPLAIN Ausgabe für den Zugriffpfad
* SHOW VARIABLES LIKE '%buffer%'; -- wichtige MySQL Server Einstellungen

Beispiel EXPLAIN..

mysql> EXPLAIN SELECT * FROM kunde;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | kunde | system | NULL          | NULL | NULL    | NULL | 0    | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.19 sec)

mysql>
Bitte so SQL-Befehle und Ausgaben mit [ code ] [ / code ] (ohne die Leerzeichen) formatieren, sonst kann man es nicht richtig lesen.
=============================================


Grüße
Thomas

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.