1

I have these tables and I'm trying to rotate the subscriber to column table to horizontal and filter its result based on multiple and/or conditions like the following:

WHERE  first_name LIKE 'm%' AND email LIKE '%com'

This is the fiddle

These are my two tables:

Fields Table

+----+------------+
| id |label       |
+----+------------+
|  1 | email      |
|  2 | first_name |
|  3 | last_name  |
+-----------------+

Subscribers Fields Table

+----+--------------+----------+---------------+-------------------+
| id | mail_list_id | field_id | subscriber_id | value             |
+----+--------------+----------+---------------+-------------------+
|  1 |            1 |        1 |             1 | mark@examble.com  |
|  2 |            1 |        2 |             1 | Mark              |
|  3 |            1 |        3 |             1 | Wood              |
|  4 |            1 |        1 |             2 | luan@domain.com   |
|  3 |            1 |        2 |             2 | Luan              |
|  4 |            1 |        3 |             2 | Charles           |
|  5 |            1 |        1 |             3 | marry@domain.com  |
|  6 |            1 |        2 |             3 | Anna              |
|  7 |            1 |        3 |             3 | Marry             |
|  8 |            2 |        1 |             4 | kevin@domain.com  |
|  9 |            2 |        2 |             4 | Kevin             |
| 10 |            2 |        3 |             4 | Faustino          |
| 11 |            2 |        1 |             5 | frank@examble.com |
| 12 |            2 |        2 |             5 | Frank             |
| 13 |            2 |        3 |             5 | Denis             |
| 14 |            2 |        1 |             6 | max@example.com   |
| 15 |            2 |        2 |             6 | Max               |
| 16 |            2 |        3 |             6 | Ryan              |
+----+--------------+----------+---------------+-------------------+

This is what I tried, but it caused issues that the email and first_name return 0 instead of value. Also it doesn't work with AND operator:

select 
  subscriber_id,
  MAX(case when field_id = '1' then value else 0 end) as email,
  MAX(case when field_id = '2' then value else 0 end) as first_name,
  MAX(case when field_id = '3' then value else 0 end) as last_name
from test_fields_table
WHERE (field_id = 3 AND value LIKE 'm%') OR (field_id = 1 AND value = '%com')
group by subscriber_id limit 100;

If I remove the WHERE condition, the query works with good performance.

I also tried to add my query in a subquery give it an alias and then search that generated virtual table using the alias field name instead of the field id, but in this case I will have to remove the limit parameter from the subquery in order to be able to search for the full table not just in the first 100 records, which causes very bad performance since this table will be too large 100-500 million records and I need to get the query result in under 4 seconds.

7
  • This is a 1-time task, correct? You will capture the result in a fresh table, correct? Then you will throw away the original schema, correct? You will not succeed in getting much performance from the current schema. Sorry to be blunt. – Rick James Nov 21 '20 at 18:19
  • @RickJames actually it's an existing email marketing app similar to Mailchimp that use this schema, it's current query is very slow when doing search and it's not posible to currently to order by different columns, I'm trying to improve that app by editing the query or the schema itself, I guess one more issue with that schema is that it's records will grow very fast so if 1 customer have an email list with 100k email and each contact has a 6 custom fields in avrage that means 600k records for just 1 customer and the customer will be allowed to add extra fields if needed. – Lara Nov 22 '20 at 11:20
  • @RickJames What do you think of that schema, to create a single table with like 50 fields, the field names will be field_1, field_2 etc.. then create other table that gives a meaningful name for each of those fields alias names, field_1 = First Name... and each of those fields on the main table will have different type of data like first name, email etc.. that way i will have to deal with one flat table – Lara Nov 22 '20 at 11:27
  • You will find that very messy to maintain. And you will find that field_1 never changes from being first_name. So, why have the extra complexity when simply giving it a suitable column name is easy. – Rick James Nov 22 '20 at 16:36
  • @RickJames I'm thinking of that schema actually in order to allow the customers to add there own custom fields and allow them to name it as they like. as you pointed out some fields may be used by most of customers like first name, last name, email, address, so i can other idea is to add a pre-defined fields for each customer and allow each customer to add his own custom fields using the field_1, fields_2 columns and set a maximum custom fields the customer can create for each mail list to like 50 field,Do you suggest a better schema that allow a fast select and search through multiple fields? – Lara Nov 22 '20 at 20:22
0

You can use HAVING to filter the columns you created:

select 
  subscriber_id,
  MAX(case when field_id = '1' then value else 0 end) as email,
  MAX(case when field_id = '2' then value else 0 end) as first_name,
  MAX(case when field_id = '3' then value else 0 end) as last_name
from test_fields_table

group by subscriber_id
HAVING email LIKE '%com' 
AND last_name LIKE 'M%'
limit 100;

See result

0

Your field_1 schema is likely to be slower than EAV or JSON. Wordpress, for example, uses an EAV schema pattern -- WP users are often grumbling on this and other forums about poor performance. JSON has pros and cons.

For performance, you must have the more common search columns in a single table with suitable datatypes. Less common search columns can be buried in EAV or JSON and tested by the application.

To allow a customer to add a commonly-searched column requires teaching him about a few datatypes (date, datetime, money, float, integer, string), and fabricating an ALTER to add the column to the table. Adding an index gets messier because it should involve multiple columns. For example, INDEX(last_name), INDEX(first_name) is handy if you only search on one of those columns. But, if the user usually searches on both columns, then you need INDEX(last_name, first_name). This is hard to anticipate.

If your customers will have only a thousand rows, none of this matters much for performance. But, long before a million rows, all methods on the table suffer som or a lot from performance.

Tell me more about the application space. (Documents / General products / Specific products (eg cameras) / Weather sensors / Geographic locations / ...) Maybe I can give some more concrete tips.

"Find the nearest coffee shop" via latitude and longitude is especially tricky; it needs its own discussion. Its performance optimization does not apply to other applications, and vice versa.

Comments on your SQL:

WHERE (field_id = 3 AND value LIKE 'm%')
   OR (field_id = 1 AND value = '%com')
group by subscriber_id
limit 100;

Notes:

  • OR is especially hard to optimize; it is likely to lead to a full table scan, checking every row.
  • value LIKE %com cannot use INDEX(value) because of the leading wildcard. (REVERSE() may be part of a workaround.
  • Because of the GROUP BY, the entire table will be scanned before getting to the LIMIT. That is, the query will be slow regardless of the LIMIT.
  • LIMIT without and ORDER BY does not say which rows you will get.
  • The "field_N" technique fails to make it easy to test numeric data. The numbers 1,2,3,15,26,108 will sort as 1,108,15,2,26,3. (+0 is a workaround, but it defeats the use of any index. WP has this problem.)
3
  • Thanks Rick for your answer, The app i'm talking about is an email marketing app where each app user can create a mail list with custom fields and then use filtering to make segments to control which contacts on his mail list will get sent that markeing email and which will get another email etc by filtering based on country, brand, total spent on webshop etc.... Your idea about asking the customer about the data type and alter the table to add columns is great if there is a single user for the app but imagin if each customer will add extra 5 different columns to the table – Lara Nov 22 '20 at 21:33
  • An idea i just though about now is to create a certain amount of fields for each datatype needed, like 30 varchar, 30 Bigint etc.. and when the app user try to create a custom field he will be resticted to create maximum of 30 text fields and 30 number field and use the field_N schema for that, I know it will look a bit mesy but not sure what other schema that can have close performance and flexability as this one. – Lara Nov 22 '20 at 22:05
  • 30 varchars, etc. That's another variant on the difficult-to-handle EAV. – Rick James Nov 22 '20 at 22:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.