I've got a table structure such as this (vertical design). I'm able to have limitless quantity of characteristics (eg: city, phone etc.) for every user.
┌────────┬───────────┬────────────┐ | UserID │ FieldName │ Value | ├────────┼───────────┼────────────┤ │ 341 │ city │ MyCity1 │ │ 772 │ phone │ 1234567890 │ │ 033 │ city │ MyCity2 │ │ 044 │ sex │ M │ │ 772 │ firstname │ MyName │ │ --- │ --- │ --- │ └────────┴───────────┴────────────┘
I must implement searching feature that ought to output rows which we apply query such as this for any flat designed table:
SELECT FieldName FROM tbl_UserAttributes WHERE city='%Mumbai%' AND sex='M' AND ...
Please dont request me to alter the database design.
UPDATE: At the moment, I've got a JOIN solution in position that is very slow also it dangles the server some occasions. Any alternate techniques?
EAV table is really a positive thing as lengthy as you don't have to look for multiple values at the same time by which situation it might be a poor factor.
You can't index several values at the same time since they're situated in various records.
SQL Server table you can create an indexed view over multiple values and employ it for that searches.
Oracle, you can cluster the table by
UserID which may keep all records with similar
UserID within one data page which may make use of an index around the most selective value and rapidly scan for that other values.
PostgreSQL, you can store all value in one array and index it having a
MySQL, that you can do neither of the.
Here is a query that will return the values:
SELECT * FROM tbl_UserAttributes tcity JOIN tbl_UserAttributes tsex ON tsex.userid = tcity.userid WHERE tcity.fieldname = 'city' AND tcity.value LIKE '%Mumbai%' AND tsex.fieldname = 'sex' AND tsex.value = 'M'
try not to expect so that it is extremely fast.
Should you needed to achieve the exact matching, you can produce a composite index on
(fieldname, value, userid), place the most selective
fieldname in to the first table and employ
STRAIGHT_JOIN to pressure an order:
SELECT * FROM tbl_UserAttributes tcity STRAIGHT_JOIN tbl_UserAttributes tsex ON tsex.userid = tcity.userid WHERE tcity.fieldname = 'city' AND tcity.value = 'Mumbai' AND tsex.fieldname = 'sex' AND tsex.value = 'M'
However, this will not assist with your present query, as you are searching for a wildcard match by which situation the indexes are not so useful. As well as your second table will not benefit much in the index unless of course you're querying a maternity hospital database.
Still you will save a while because the index scan may be used rather than a table scan.
Seen this before. Rather than trying to find stuff that match city and sex and whatever, count the number of characteristics suit your search query. If the count is equivalent to the amount of characteristics during your search query, it is just one of your results.
Exist a set group of FieldNames?
If you will find can one suggest establishing a view to show it horizontal making simple to use to question from. In SQL Server 2005 it might be something similar to:
SELECT * FROM (SELECT [UserID], [FieldName], [Value] FROM [tbl_UserAttributes] ) ps PIVOT ( MAX([Value]) FOR [FieldName] IN ( [City], [Phone], [sex], [firstname]) ) AS pvt
This will allow it to be horizontal although all needed [FieldName] values have to be within the IN () section to drag out a area for every. Also while using Max means for those who have multiple values for the similar FieldName it'll take out the Max one.
To individuals who offer help this is actually the classic situation of EAV (Entity Attribute Value). It's highly NOT suggested when creating programs.