executing the SELECT

[datadriven]

I've debated the cardinality of a table relationship. Each unique rowid can select from one on up to 250 values, for a miximum of six times. (presently six col's for this in a table -- which assumes a one-to-many relation). Even if this is a many-to-many, it may be easier to manage out of the existing table (by drop menus, since I am limiting total # of results).

Question:

If a statement filters like so, will this Select by the zipcol first (a zip code) as a condition before searching all other six cols in the table. This is a memory question:

SELECT name WHERE zipcol = "55098" AND ((col1 OR col2 OR col3 OR col4 OR col5 OR col6) = 10)

Please forget about syntax, this is a table design question.

[danfran]

I think your question is more what is the execution plan algorithm of that query in my database system?

The answer depends on your database system, index configuration and whether or not you specify HINTS. If you put all of those columns in a single [composite] index (starting with zipcol), SQL Server will simply traverse the index once and give you the results without doing a bookmark lookup. A little wasteful, IMO, but oh so fast. -All kinds of possibilities!

If you're using SQL Server, you can view the execution plan in Query Analyzer w/ CTRL K

I did a quick test (col=blah and x or y or z) on an index-less table and lo and behold a big, fat, ugly TABLE SCAN was performed.

Every index takes up space on disk and in memory so, you have to decide where you can trade speed for memory usage.. You can index every single column in your database and make it Google-speed. If you're low on memory this is pointless. You'd be better off just indexing the ZipCol and let the database engine (more processor intensive) do the work.

-Dan

[r937]

this is a table design question?

normalize the 6 columns into their own table, declare an index on the two columns (key and col), and the performance should be even way much better

[datadriven]

Doyou think this is okay for thousands of rows (index cols) ? Or should I try to place into a single column many-to-many table , from where I would need to limit to 6 total values per user; making these updateable by 6 drop menus.

Would much be saved in the way of memory by doing this way as opposed to the index/6 col's? (approx 6 X's the rows, instead of 6 seperate cols)

[r937]

i'm sorry, you totally lost me with your last post

maybe you can give some actual examples of the table layouts you're contemplating

[datadriven]

I'm sorry, I'll try to make that clearer the tables aren't entirely laid out yet)

Debating on two basic schemas;

a) Placing the mentioned six columns either along with the same table having the zipcol. Or elese creating the six columns on a seperate table linked to a table containing a zip column [ either case there are six columns ] OR

b) combining all values that would have gone into these six columns and place these values into a single col many-tomany table(actually two totals column if you count the row id). Merely transferring the six column values into six rows per user.

Would b) consume less resources with a JOIN statement having SELECT originaltable.name WHERE originaltable.zipcol = "55098" AND manytomany.col=10 (on the many-to-many)

OR would SELECT on a) consume less with SELECT name WHERE zipcol = "55098" AND ((col1 OR col2 OR col3 OR col4 OR col5 OR col6) = 10)

[r937]

yes, b) is much better

i don't see why you call it many-to-many (unless each of the cols is actually a foreign key to some 3rd table where the col is the primary key)

and this design is definitely more efficient for querying

those ORs would likely cause a table scan

[datadriven]

thing is, I'm not sure how to do an update on many-to-many values, and limit the values to 6 total -- doing these from drop menus.

[r937]

update, or insert?

if there's a maximum, this must be enforced by the application logic

but, i mean, you'd've had similar complexity, trying to decide whether to slot a new col into col4 or col5 or whatever the next free slot was, and what to do if all 6 are occupied

setting a limit on 6 rows is similar

[datadriven]

I'm going in circles.

those ORs would likely cause a table scan

But this wouldn't be nearly so bad if I could force this first:
WHERE zipcol = "55098" //Then have the SELECT search the 6 col's.

note: there shouldn't be more than 100-200 rowid's per zip code. (ea having 6 columns- also, these 6 col's contain 1-3 numerical characters only)

Doing an Update on 6 col's is much easier than 6 rows of a many-to-many table. First, it is kept to 6 seperate values automatically if updating out of seperate columns. There is no chance of ending up with more than 6 values as there is from a many-to-many table have infinite number of rows (in the event of multiple updates, for example).

Updating 250 possible values out of 6 drop menus, I would need to account for the order of how the values appear consecutively within the many-to-many table [ value 1, 2, 3, 4, 5, 6 ]. Further, I've noticed that mysql tables don't always necessarily maintain their consecutive Insert order (numerically id's yes, occurances within the row order, no)

That Table scan sounds like a lot of memory -- would this scan the entire table -- the actual listing order sequence in the SELECT doesn't matter?

One thing I forgot to mention is that the results from the SELECT will appear in large directory structure ]so the OR would need to be used for all results.