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

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.