Performance-Probleme :(

[teresiak]

Moin!

Ich habe leider ein arges Problem was die Performance einer Datenbankstruktur angeht und hoffe das mir hier jemand weiterhelfen bzw. einen Tipp geben kann.

Folgende Struktur:

CREATE TABLE `products_categories` (
`products_id` bigint(255) NOT NULL default '0',
`categories_id` int(11) NOT NULL default '0',
`categories_sub_id` int(11) NOT NULL default '0',
`categories_sub_books_id` int(11) NOT NULL default '0',
PRIMARY KEY (`products_id`),
KEY `categories_id` (`categories_id`),
KEY `categories_sub_id` (`categories_sub_id`),
KEY `categories_sub_books_id` (`categories_sub_books_id`)
) TYPE=MyISAM;


CREATE TABLE `products_data` (
`id` bigint(255) NOT NULL auto_increment,
`products_isbn` varchar(50) NOT NULL default '',
`products_ean` varchar(50) NOT NULL default '',
`products_name` varchar(255) NOT NULL default '',
`products_system` varchar(100) NOT NULL default '',
`products_datentraeger` varchar(100) NOT NULL default '',
`products_uvp` varchar(20) NOT NULL default '',
`products_hersteller` text NOT NULL,
`products_vertrieb` varchar(100) NOT NULL default '',
`products_beschreibung` text NOT NULL,
`products_schauspieler` varchar(255) NOT NULL default '',
`products_regie` varchar(255) NOT NULL default '',
`products_produktion` varchar(100) NOT NULL default '',
`products_spiellaenge` varchar(30) NOT NULL default '',
`products_fsk` varchar(30) NOT NULL default '',
`products_land` varchar(50) NOT NULL default '',
`products_jahr` varchar(20) NOT NULL default '',
`products_sprache` varchar(50) NOT NULL default '',
`products_ton` varchar(255) NOT NULL default '',
`products_untertitel` varchar(255) NOT NULL default '',
`products_bildformat` varchar(100) NOT NULL default '',
`products_extras` text NOT NULL,
`products_erscheinung` int(15) NOT NULL default '0',
`products_image` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1117230 ;


CREATE TABLE `products_stats` (
`products_id` bigint(255) NOT NULL default '0',
`sales_last` int(11) NOT NULL default '0',
`sales` int(11) NOT NULL default '0',
`views_last` int(10) NOT NULL default '0',
`views` int(10) NOT NULL default '0',
PRIMARY KEY (`products_id`),
KEY `test` (`products_id`,`sales_last`)
) TYPE=MyISAM;




Die Abfrage sieht so aus:

SELECT
a.id, a.products_ean, a.products_name, a.products_system, a.products_uvp, a.products_hersteller, a.products_schauspieler,
a.products_jahr, a.products_image,
b.sales_last,
c.categories_id, c.categories_sub_id, c.categories_sub_books_id
FROM
products_data a
JOIN products_stats b ON (a.id = b.products_id)
JOIN products_categories c ON (b.products_id = c.products_id && categories_id = 21)
LIMIT 0, 25


Das Funktioniert; jedoch nicht so wie ich mir das vorstelle.

Die Tabelle products_stats ist nach sales_last vorsortiert. Ich möchte also gerne die kompletten Ergebnisse ohne ein ORDER BY sortiert ausgeben. Funktioniert nur leider nicht Mit dem ORDER BY dauert die ganze Abfrage gut 1.4 Sekunden. Das dauert zu lange.

Hat jemand Ideen; falsche Datenbankstruktur gewählt?


Grüße

Stefan

[Benny-one]

versuch doch mal alles in ein array zu legen und da sortieren, ob das schneller geht

[teresiak]

Original geschrieben von Benny-one
versuch doch mal alles in ein array zu legen und da sortieren, ob das schneller geht

Hi,

das sind 3.500.000 Datensätze. Da wird das mit dem Array nicht funktionieren. Bei kleineren Daten kein Problem.

[goth]

Schau' mal nach was ein EXPLAIN/DESCRIBE für das Statement ausgibt ...

Im übrigen würde ich den Join für products_categories vorziehen, da er eine eine Eingrenzung vornimmt

SELECT 
	a.id, a.products_ean, a.products_name, a.products_system, a.products_uvp, a.products_hersteller, a.products_schauspieler, 
	a.products_jahr, a.products_image,
	b.sales_last,
	c.categories_id, c.categories_sub_id, c.categories_sub_books_id
  FROM products_data a 
  JOIN products_categories c ON (b.products_id = c.products_id && categories_id = 21)
  JOIN products_stats b ON (a.id = b.products_id) 
 LIMIT 0, 25

Eventuell kannst Du auch, wenn beispielsweise eine [1:1] Beziehung zwischen products_data und products_stats vorliegt mit 'nem LEFT JOIN arbeiten ... der ist etwas schneller ...

[mrhappiness]

schreib mal ein EXPLAIN vor das select und schau dir an, wie mysql deine abfrage ausführt

[teresiak]

Original geschrieben von goth
Schau' mal nach was ein EXPLAIN/DESCRIBE für das Statement ausgibt ...

Im übrigen würde ich den Join für products_categories vorziehen, da er eine eine Eingrenzung vornimmt

SELECT 
	a.id, a.products_ean, a.products_name, a.products_system, a.products_uvp, a.products_hersteller, a.products_schauspieler, 
	a.products_jahr, a.products_image,
	b.sales_last,
	c.categories_id, c.categories_sub_id, c.categories_sub_books_id
  FROM products_data a 
  JOIN products_categories c ON (b.products_id = c.products_id && categories_id = 21)
  JOIN products_stats b ON (a.id = b.products_id) 
 LIMIT 0, 25

Eventuell kannst Du auch, wenn beispielsweise eine [1:1] Beziehung zwischen products_data und products_stats vorliegt mit 'nem LEFT JOIN arbeiten ... der ist etwas schneller ...

Hi,

diese Abfrage ist super schnell. Jedoch wird das Ergebnis in der falschen Reihenfolge ausgegeben. Zwischen Left Join und normalem Join gibt es keine Messbaren unterschiede. Hier mal die EXPLAIN - Ausgabe bei insgesamt 300.000 Testdatensätzen bezogen auf Deine oben genannte Abfrage...


c ref PRIMARY,categories_id categories_id 4 const 241228 Using where
b eq_ref PRIMARY,test PRIMARY 8 c.products_id 1
a eq_ref PRIMARY PRIMARY 8 b.products_id 1

[goth]

Eventuell kann ein

  FROM products_categories c
  JOIN products_data a
    ON c.products_id = a.id
  JOIN products_stats b
    ON c.products_id = b.products_id
 WHERE categories_id = 21

noch etwas mehr erreichen ... für die Reihenfolge gibt's ein ORDER BY .. !

[teresiak]

Original geschrieben von goth
Eventuell kann ein

  FROM products_categories c
  JOIN products_data a
    ON c.products_id = a.id
  JOIN products_stats b
    ON c.products_id = b.products_id
 WHERE categories_id = 21

noch etwas mehr erreichen ... für die Reihenfolge gibt's ein ORDER BY .. !

Das Problem ist genau das ORDER BY. Sobald ich dieses Verwende dauert die Abfrage einfach zu lange. Das schnellste was ich hinbekommen habe sind 1.4 Sekunden. Das ist jedoch einfach zu lange. Problem bei dem ORDER BY ist, das mehr als 95% der sortierten Spalte die gleichen Inhalte besitzen. Um dieses Problem zu beheben habe ich die Tabelle products_stats vorsortiert.

Die anderen zu verknüpfenden Tabellen sollten die Reihenfolge von products_stats annehmen.

Dies funktionierte bislang jedoch nur, wenn ich folgende Abfrage benutzt habe:

SELECT
a.*, b.products_name
FROM products_stats a
JOIN products_data b ON (a.products_id = b.id)
LIMIT 0, 25

Super schnelle Abfrage und auch noch in der richtigen Reihenfolge.

Sobald ich jedoch zusätzlich über products_categories entsprechende Kategorie auswählen möchte, also ein weiteres JOIN einbaue, geht die Reihenfolge verloren.

[mrhappiness]

und wenn du nicht a mit b und b mit c joinst sondern a mit b und a mit c?

[goth]

Das Problem ist nur, das eine SQL-Datenbank eine Blackbox ist ... auf die "Vorsortierung" von Tabellen kannst Du Dich schlichtweg nicht verlassen ...

[teresiak]

Original geschrieben von mrhappiness
und wenn du nicht a mit b und b mit c joinst sondern a mit b und a mit c?

SELECT
a.id, a.products_ean, a.products_name, a.products_system, a.products_uvp, a.products_hersteller, a.products_schauspieler,
a.products_jahr, a.products_image,
b.sales_last,
c.categories_id, c.categories_sub_id, c.categories_sub_books_id
FROM
products_data a
JOIN products_stats b ON (a.id = b.products_id)
JOIN products_categories c ON (a.id = c.products_id && categories_id = 1)
LIMIT 0, 25


Die Reihenfolge geht auch dabei verloren...

[teresiak]

Original geschrieben von goth
Das Problem ist nur, das eine SQL-Datenbank eine Blackbox ist ... auf die "Vorsortierung" von Tabellen kannst Du Dich schlichtweg nicht verlassen ...

Sch****e. Ist das ein Problem welches lediglich bei MySQL liegt? Funktioniert die Vorsortierung bei MS-SQL besser?

[goth]

Nein ... sicherlich nicht ... das ist einfach das Grundprinzip von SQL als Datenbank Abfragesprache ... Du kannst Dich nur auf das verlassen was Du in Deiner Abfrage formuliert hast ... !!

[teresiak]

Oke. Danke Dir.

Wenn ich ne performante Lösung finde poste ich diese hier.


Grüße

Stefan

[goth]

Vielleicht findest Du ja auch hier noch Informationen zum Optimieren ...

http://dev.mysql.com/doc/mysql/en/OR...imization.html