MySQL: Quickly Select Data with Multiple Conditions from One Table

Submitted by Peter Majmesku on Fri, 08/04/2017 - 11:09

Selecting data from one table with multiple conditions with Subselects is slow. Joins are quicker. Here is an example:

select * from OrderMetaData
Join OrderMetaData as a on OrderMetaData.id = a.Order_id and a.name = 'store' and a.value = 128
Join OrderMetaData as b on OrderMetaData.id = b.Order_id and b.name = 'processing_status' and b.value = 4;

This query takes 0,44 seconds in a table with > 800.000 entries. The sub-select takes 17 seconds.

Tags