Hallo Thomas,
erst einmal vielen Dank für Deine Hilfe. Hier die Daten die Du benötigst:
CREATE TABLE `element` (
`operator` varchar(8) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`language` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`elementid` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
`value` text COLLATE utf8_unicode_ci,
`modified` datetime NOT NULL,
`type` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`parentid` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`operator`,`language`,`elementid`),
UNIQUE KEY `UNIQUE` (`operator`,`language`,`parentid`,`elementid`),
KEY `elementid` (`elementid`),
KEY `parentid` (`parentid`),
KEY `operator` (`operator`,`language`,`parentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `object_to` (
`operator` varchar(8) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`language` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`guid` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`objectid` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`hasobjects` tinyint(1) NOT NULL,
`template` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`hide` tinyint(1) DEFAULT NULL,
`area` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`sorting` smallint(6) NOT NULL,
`startdate` bigint(20) DEFAULT NULL,
`enddate` bigint(20) DEFAULT NULL,
PRIMARY KEY (`operator`,`language`,`guid`,`objectid`),
KEY `operator` (`operator`,`language`,`guid`),
KEY `guid` (`guid`),
KEY `objectid` (`objectid`),
KEY `template` (`template`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| element | InnoDB | 10 | Compact | 1862866 | 703 | 1310588928 | 0 | 4140531712 | 57671680 | NULL | 2012-07-06 08:12:37 | NULL | NULL | utf8_unicode_ci | NULL | | |
+---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| object_to | InnoDB | 10 | Compact | 249241 | 222 | 55476224 | 0 | 138248192 | 57671680 | NULL | 2012-07-06 08:33:08 | NULL | NULL | utf8_unicode_ci | NULL | | |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
+----+-------------+-------+--------+--------------------------------------------+---------+---------+--------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------+---------+---------+--------------------------------+------+-------------+
| 1 | SIMPLE | e1 | range | PRIMARY,UNIQUE,parentid,operator | UNIQUE | 808 | NULL | 43 | Using where |
| 1 | SIMPLE | e2 | eq_ref | PRIMARY,UNIQUE,elementid,parentid,operator | PRIMARY | 429 | const,const,cops3.e1.elementid | 1 | |
+----+-------------+-------+--------+--------------------------------------------+---------+---------+--------------------------------+------+-------------+
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| sql_buffer_result | OFF |
+-------------------------+----------+
Vielen vielen Dank nochmal!
Achso, zu Deinen Fragen:
Das "SELECT *" habe ich hier nur der einfach halt halber geschrieben. Das werde ich ändern. Dein vorgeschlagenes "IN" optimiert leider nicht die Performance.
BG,
Christoph