What is the best MySQL table structure.. or the best way to query my existing structure?

A User must fulfill all criteria in your query, so you have to join tblData multiple times, just like it were multiple tables.

A User must fulfill all criteria in your query, so you have to join tblData multiple times, just like it were multiple tables: SELECT u. * FROM tblUsers you JOIN tblData d1 ON d1. Uid = u.Id AND d1.

DataType=1 AND d1. DataValue=21 JOIN tblData d2 ON d2. Uid = u.

Id AND d2. DataType=2 AND d2. DataValue BETWEEEN 400 AND 500 Answer to additional question in comments For this to be performant, indexes are crucial.

In particular case you will probably need the following indexes: CREATE INDEX tbldata_uid_idx ON tblData(uid); CREATE INDEX tbldata_datatype_datavalue_idx ON tblData(dataType, dataValue); I assume that id is the primary key of tblUsers and is indexed automatically as such. Read about multi-column indexes in the manual. JOIN performance has been increased recently, but is still lacking behind other database systems like Oracle, SQL Server or PostgreSQL where JOINs are handled very performant.

MySQL is not the best choice for lots of JOINs and subqueries. For your particular case (multiple joins that can be combined) bitmap index scans will provide top performance - a feature that is not present in MySQL.It has an "index_merge" feature so substitute for that.

Perfect! Thank you so much... I never realized you could do multiple joins of the same table in a row! – Guybrush Threepwood Dec 8 at 6:29 Hmm, now I'm starting to wonder about performance and scalability.

Let's say the average user has 20 additional data points to check. There would be 20 rows in tlbData for every row in tlbUsers. The tlbData would continue to grow linearly like this.To query, that would require 20 JOINs in a row, and that seems... slow?

Lets say the DB grew to 1GB, would that be a 20GB pass? Or would it be smart enough to cache the query results... or do it all in one pass?Hmmm... – Guybrush Threepwood Dec 10 at 1:59 @GuybrushThreepwood: the key element will be indexes. For lots of joins and subqueries with big tables mysql may be not the best choice, but it can certainly do the job.

I added an additional answer to my answer above. – Erwin Brandstetter Dec 10 at 2:39 Ah, once again thank you for putting me on the right track! For now I will use MySQL multi-indexing, but I really appreciate that you went as far as listing the bitmap indexing and other available technologies in case I need to swap out MySQL... great stuff!

– Guybrush Threepwood 21 hours ago.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.