The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

i cannot really answer the mysql index question, since so much depends on the actual contents -- as in all competent database systems, there comes a point where the index will be ignored if it isn't selective enough

for example, suppose you have a table with many rows, and an index, but the index is on a column (e.g. gender) which has only two values

many databases will ignore this index, because to find all the rows with gender='F' requires a read of the index file and then a read of the table, so what the heck, that's two reads for half the table, mought as well ignore the index, table scans are faster anyway...

if field_1 may be only 0 or 1, would i be better to use field_1 IN (0,1) rather than what i am right now?

well, all i can say about this is that if field_1 can only contain 0 or 1, then you can drop where field_1 in (0,1) altogether, because that'll be true for every row

well this table would contain may rows, and the 3 fields could have spread values, say, from 0 to 100.

well, all i can say about this is that if field_1 can only contain 0 or 1, then you can drop where field_1 in (0,1) altogether, because that'll be true for every row

what i meant was that the only values possible below 2 were 0 and 1, but the field (as the two others) is an unsigned smallint, so there may be many values above 2. The question was really about knowing if it's better for an unsigned int to check if it's < n or IN (1,...,n-1). My guess would be that it's the same, but i'm a beginner .

i was re-reading your answer about the index and thinking about it, do you think that if i'm testing the query on a test table with only a few rows, and that query returns many of these rows, then the indexes could be not selective enough for mysql to show them up in "explain", while when the hundreds of records would be there it would show them ? In that case i should get data comparable to what there will be in production and test the query then ?

thanks for helping me out in my first steps writing my actual own queries.