This is just a quick question, mainly for my benefit, but is below correct? I always thought that if a database was designed correctly, and used a single query, that it wold be faster than looping through a field and doing a query for each element?

What are your thoughts?


/STARTQUOTE
I understand the points that you have raised regarding the database design. The approach you have taken using joins is certainly best practice, and the correct way to do it.

However, in experience, MySQL slows to an extent where it is unusable when using joins between tables containing 100,000s of records – which the Nobles Venues website will.

Using arrays in fields eliminates the need for joins in certain situations, and ensures the system will run as quickly as possible.

For example, selecting a venue with a swimming pool and self-catering would involve joining 3 tables (venues and venuesFacilities and venuesCaterers).

By using arrays, only a single table needs to be queried.
/ ENDQUOTE

Strictly speaking, multiple selects are faster than one complicated join (especially if the tables are not properly indexed) -- this, however, depends on what storage engine you are using, the nature of the query, status of the indexing, etc. Almost always though, its best to have the database do any set-related operations (joins).

Also, I don't think that this statement holds much truth:

"MySQL slows to an extent where it is unusable when using joins between tables containing 100,000s of records". There are lots of companies using MySQL that have a lot more than 100,000 records. I'm sure that it would have come up by now.

I also didn't get what was meant by 'arrays in fields'. If this means storing more than one atomic value in a field -- then this should be a red flag that the database design needs reviewing. If you read even the most basic database design book, they will tell you that you must always store the most atomic value possible in a column. Storing multiple values, or values that can mean two things causes great problems when doing queries against one of those values.

The most common example used for this is the lastname firstname situation. Suppose to store the last and first name in one column called 'Name'. How can you expect to accurately get a result set where all last names are Johnson? Someone might have entered Tom Johnson and others Johnson Thomas. The data becomes ambiguous because it is not in its most atomic form.

The best option in your case would be to create a view. Thinking in the long run, if your database server and your web server are not on the same machine (they are connected via a network) then that is a lot of data that is passing through the network. Especially if its 100,000 of records that you are fetching each time.

Thank for the reply, you kinda said what I was thinking already!

In terms of the join, it would be say a table of venues, a table of services, a table of promotions, and then cross-rerference tables for the services and venues, promotions and venues.

At the moment, in the venues table, there is a services field with say 2¬1¬4¬6¬9¬12¬ etc in it, which then requires exploding and doing a query on each element.

At the moment, in the venues table, there is a services field with say 2¬1¬4¬6¬9¬12¬ etc in it, which then requires exploding and doing a query on each element.

I would drop the venues table into an array at the begginning of the script and then after you have exploded your field values run each value through the venues array looking for a match, then you only have one query for the venues array.

If I`m searching for a particular value on a field like yours I would use FIND_IN_SET('$searchvalue',FieldName)

Hope that helps.

Philco

Yes Philco, but what he is referring to is not a set, its just a delimited list.

Yes Philco, but what he is referring to is not a set, its just a delimited list.

Doesn`t matter, if the list is comma seperated and the field is a VARCHAR type, then FIND_IN_SET works on it as if it is a set ...

ooops, ok I thought he had a -, but in fact its a ¬ in that case I would change that ¬ out for a comma... :peace:

It also has the advantage of overcoming the 64 value limit of a SET type field as you can use VARCHAR or TEXT types for the field.

Philco

Doesn`t matter, if the list is comma seperated and the field is a VARCHAR type, then FIND_IN_SET works on it as if it is a set ...

ooops, ok I thought he had a -, but in fact its a ¬ in that case I would change that ¬ out for a comma...

Right there is a perfect example of the kind of problems this creates. Its just better not to have multiple 'things' in one column. Otherwise, you will end up formatting your data so that your queries work (another sign that you need to re-think your database layout).