MySQL Geschwindigkeit

[theone]

Ich habe gerade ein Performance Problem bei einer Anfrage.

Diese dauert (je nach Systemlast) zwischen 10 und 80 sec.

SELECT 
o.CustomerID as o_CustomerID,
o.OrderID as o_OrderID,
o.ExternalOrderID as o_ExternalOrderID,
o.OrderType as o_OrderType,
o.OrderStatus as o_OrderStatus,
o.OrderTimestamp as o_OrderTimestamp,
o.DeliveryAddressID as o_DeliveryAddressID,
o.TotalBrutto as o_TotalBrutto,
ca.CustomerID as ca_CustomerID,
ca.Company as ca_Company,
ca.AdditionalName as ca_AdditionalName,
ca.FirstName as ca_FirstName,
ca.Surname as ca_Surname,
ca.Street as ca_Street,
ca.HouseNumber as ca_HouseNumber,
ca.ZIP as ca_ZIP,
ca.City as ca_City,
ca.CountryID as ca_CountryID,
cd.CustomerID as cd_CustomerID,
cd.Company as cd_Company,
cd.AdditionalName as cd_AdditionalName,
cd.FirstName as cd_FirstName,
cd.Surname as cd_Surname,
cd.Street as cd_Street,
cd.HouseNumber as cd_HouseNumber,
cd.ZIP as cd_ZIP,
cd.City as cd_City,
cd.CountryID as cd_CountryID,
cu.CustomerNumber as cu_CustomerNumber,
cu.Company as cu_Company,
cu.AdditionalName as cu_AdditionalName,
cu.FormOfAddress as cu_FormOfAddress,
cu.Title as cu_Title,
cu.FirstName as cu_FirstName,
cu.Surname as cu_Surname,
cu.Street as cu_Street,
cu.HouseNo as cu_HouseNo,
cu.ZIP as cu_ZIP,
cu.City as cu_City,
cu.CountryID as cu_CountryID,
cu.CountryISO2 as cu_CountryISO2,
cu.ContactPerson as cu_ContactPerson,
cu.Telephone as cu_Telephone,
cu.Mobile as cu_Mobile,
cu.Email as cu_Email,
cu.EbayName as cu_EbayName,
cu.Newsletter as cu_Newsletter,
cu.Language as cu_Language,
cu.VAT_ID as cu_VAT_ID,
cu.DateOfBirth as cu_DateOfBirth,
cu.LastSalesOrder as cu_LastSalesOrder,
cu.LastSalesOrderCount as cu_LastSalesOrderCount
FROM orders o,
customer_address ca,
delivery_address cd,
customer cu
WHERE o.CustomerID = ca.CustomerID
AND o.CustomerID = cd.CustomerID
AND o.CustomerID = cu.CustomerID
AND o.OrderID ='$OrderID' 

Jede der 4 Tabellen ist ~100.000 Einträge und 10mb schwer.

Ich habe schon versucht mit Joins das zu bewältigen, aber diese sind genauso schnell.

In dem Beispiel suche ich nach einer Bestellnummer, ich suche aber auch teilweise nach Kombis z.B. Name & PLZ.

[h3ll]

Indexe richtig gesetzt?

Was sagt EXPLAIN?

Verwende bitte nicht die Theta-Style Schreibweise (FROM a, b, c). Die ist schwer lesbar und verleitet zu Fehlern.

[theone]

+----+-------------+-------+------+---------------+------------+---------+--------------------------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref                      | rows  | Extra                          |
+----+-------------+-------+------+---------------+------------+---------+--------------------------+-------+--------------------------------+
|  1 | SIMPLE      | cu    | ALL  | NULL          | NULL       | NULL    | NULL                     | 19691 |                                |
|  1 | SIMPLE      | o     | ALL  | NULL          | NULL       | NULL    | NULL                     | 21224 | Using where; Using join buffer |
|  1 | SIMPLE      | cd    | ref  | CustomerID    | CustomerID | 302     | db374593146.o.CustomerID |     1 | Using where                    |
|  1 | SIMPLE      | ca    | ALL  | NULL          | NULL       | NULL    | NULL                     | 21226 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------------+---------+--------------------------+-------+--------------------------------+

[h3ll]

possible keys => NULL

Ganz schlecht. Dir fehlen die passenden Indexe.

[theone]

AAhh, super! Ich hab jetzt indexe gesetzt und komme auf 2sec.
Damit kann man doch arbeiten! Danke

+----+-------------+-------+------+-------------------------+------------+---------+--------------------------+------+-------------+
| id | select_type | table | type | possible_keys           | key        | key_len | ref                      | rows | Extra       |
+----+-------------+-------+------+-------------------------+------------+---------+--------------------------+------+-------------+
|  1 | SIMPLE      | o     | ref  | OrderID,CustomerID      | OrderID    | 302     | const                    |    1 | Using where |
|  1 | SIMPLE      | cu    | ref  | CustomerID              | CustomerID | 302     | db374593146.o.CustomerID |    1 | Using where |
|  1 | SIMPLE      | ca    | ref  | CustomerID              | CustomerID | 302     | db374593146.o.CustomerID |    1 | Using where |
|  1 | SIMPLE      | cd    | ref  | CustomerID,CustomerID_2 | CustomerID | 302     | db374593146.o.CustomerID |    1 | Using where |
+----+-------------+-------+------+-------------------------+------------+---------+--------------------------+------+-------------+