mysql> SELECT ObjectID, -> (SELECT sysBestandsnaam FROM tblBOGObjectMedia -> WHERE ObjectID = tblBOGObject.ObjectID AND MediaGroep = 'beeld' AND sysVerwijderd='0' AND sysBestandAanwezig = '1' AND HoofdFoto = '1') as MediaLocationPath -> FROM tblBOGObject -> WHERE Postcode BETWEEN '1300AA' AND '1369ZZ' -> AND sysTransactiePlaatsgevonden = '0' -> ORDER BY Straatnaam LIMIT 0,5; +----------+----------------------------+ | ObjectID | MediaLocationPath | +----------+----------------------------+ | 97124 | 009/71/24/0097124.0001.jpg | | 94002 | 009/40/02/0094002.0001.jpg | | 98677 | 009/86/77/0098677.1000.jpg | | 98299 | NULL | | 72302 | 007/23/02/0072302.0001.jpg | +----------+----------------------------+ 5 rows in set (0.45 sec) mysql> EXPLAIN SELECT ObjectID, -> (SELECT sysBestandsnaam FROM tblBOGObjectMedia -> WHERE ObjectID = tblBOGObject.ObjectID AND MediaGroep = 'beeld' AND sysVerwijderd='0' AND sysBestandAanwezig = '1' AND HoofdFoto = '1') as MediaLocationPath -> FROM tblBOGObject -> WHERE Postcode BETWEEN '1300AA' AND '1369ZZ' -> AND sysTransactiePlaatsgevonden = '0' -> ORDER BY Straatnaam LIMIT 0,5; +----+--------------------+-------------------+-------+-----------------------------------------------------------------------------------+----------------+---------+------------------------------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------------------+-------+-----------------------------------------------------------------------------------+----------------+---------+------------------------------------+------+-----------------------------+ | 1 | PRIMARY | tblBOGObject | range | sysTransactiePlaatsgevonden_index,Postcode_index | Postcode_index | 13 | NULL | 1603 | Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | tblBOGObjectMedia | ref | PRIMARY,ObjectID_index,sysBestandAanwezig_index,HoofdFoto_index,setPriority_index | PRIMARY | 4 | ws_nvmaanbod.tblBOGObject.ObjectID | 5 | Using where | +----+--------------------+-------------------+-------+-----------------------------------------------------------------------------------+----------------+---------+------------------------------------+------+-----------------------------+ 2 rows in set (0.00 sec) mysql> DESCRIBE tblBOGObjectMedia; +-----------------------------+-----------------------------------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------------+-----------------------------------------------------------------------------+------+-----+---------+-------+ | ObjectID | int(10) unsigned | NO | PRI | 0 | | | MutatieID | int(10) unsigned | NO | MUL | 0 | | | MediaID | int(10) unsigned | NO | PRI | 0 | | | Bestandsnaam | varchar(128) | YES | | NULL | | | sysBestandsnaam | varchar(50) | YES | | NULL | | | sysBestandAanwezig | enum('0','1') | NO | MUL | 0 | | | sysBestandMutatieID | int(10) unsigned | YES | | NULL | | | sysBestandRetrievedFromHTTP | enum('0','1') | YES | | NULL | | | sysBestandRetrievedFromFTP | enum('0','1') | YES | | NULL | | | sysBestandMd5 | varchar(32) | YES | | NULL | | | HoofdFoto | enum('0','1') | YES | MUL | NULL | | | sysDLM | datetime | YES | MUL | NULL | | | sysDOC | datetime | YES | MUL | NULL | | | sysTiaraDLM | datetime | YES | MUL | NULL | | | sysTiaraDOC | datetime | YES | MUL | NULL | | | sysDOR | datetime | YES | MUL | NULL | | | sysRetrievePriority | tinyint(4) | YES | MUL | NULL | | | sysRetrievePID | int(10) unsigned | YES | MUL | NULL | | | MediaGroep | enum('beeld','document') | NO | PRI | beeld | | | MediaMimeType | enum('avi','bmp','doc','flash','gif','ipx','jpg','mpeg','pdf','tiff','wmv') | YES | | NULL | | | Omschrijving | text | NO | | NULL | | | sysVersie | char(3) | YES | | NULL | | | sysVerwijderd | enum('0','1') | YES | MUL | NULL | | | uuid | varchar(36) | YES | MUL | NULL | | +-----------------------------+-----------------------------------------------------------------------------+------+-----+---------+-------+ 24 rows in set (0.01 sec) mysql> SHOW INDEXES FROM tblBOGObjectMedia \G; *************************** 1. row *************************** Table: tblBOGObjectMedia Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: ObjectID Collation: A Cardinality: 95628 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: tblBOGObjectMedia Non_unique: 0 Key_name: PRIMARY Seq_in_index: 2 Column_name: MediaID Collation: A Cardinality: 478144 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: tblBOGObjectMedia Non_unique: 0 Key_name: PRIMARY Seq_in_index: 3 Column_name: MediaGroep Collation: A Cardinality: 478144 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 4. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: mutatieid_index Seq_in_index: 1 Column_name: MutatieID Collation: A Cardinality: 478144 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 5. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: ObjectID_index Seq_in_index: 1 Column_name: ObjectID Collation: A Cardinality: 95628 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 6. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: sysRetrievePriority_index Seq_in_index: 1 Column_name: sysRetrievePriority Collation: A Cardinality: 478144 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 7. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: sysRetrievePID_index Seq_in_index: 1 Column_name: sysRetrievePID Collation: A Cardinality: 478144 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 8. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: sysDOR_index Seq_in_index: 1 Column_name: sysDOR Collation: A Cardinality: 239072 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 9. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: sysDOC_index Seq_in_index: 1 Column_name: sysDOC Collation: A Cardinality: 59768 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 10. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: sysBestandAanwezig_index Seq_in_index: 1 Column_name: sysBestandAanwezig Collation: A Cardinality: 2 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 11. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: HoofdFoto_index Seq_in_index: 1 Column_name: HoofdFoto Collation: A Cardinality: 2 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 12. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: sysDLM_index Seq_in_index: 1 Column_name: sysDLM Collation: A Cardinality: 59768 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 13. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: sysTiaraDOC_index Seq_in_index: 1 Column_name: sysTiaraDOC Collation: A Cardinality: 119536 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 14. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: sysTiaraDLM_index Seq_in_index: 1 Column_name: sysTiaraDLM Collation: A Cardinality: 119536 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 15. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: bog_media_uuid Seq_in_index: 1 Column_name: uuid Collation: A Cardinality: 478144 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 16. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: nvm_media_uuid Seq_in_index: 1 Column_name: uuid Collation: A Cardinality: 478144 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 17. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: setPriority_index Seq_in_index: 1 Column_name: sysVerwijderd Collation: A Cardinality: 2 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 18. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: setPriority_index Seq_in_index: 2 Column_name: sysBestandAanwezig Collation: A Cardinality: 4 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 19. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: setPriority_index Seq_in_index: 3 Column_name: HoofdFoto Collation: A Cardinality: 8 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 20. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: setPriority_index Seq_in_index: 4 Column_name: sysDLM Collation: A Cardinality: 119536 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: *************************** 21. row *************************** Table: tblBOGObjectMedia Non_unique: 1 Key_name: setPriority_index Seq_in_index: 5 Column_name: sysDOC Collation: A Cardinality: 119536 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 21 rows in set (0.00 sec)