Forum Moderators: coopster
As you see, it's not a good idea at all, because there are so many countries and it's immpossible to create N indexs on these columns.
The new idea is using bit operation:
1 0 1 0
us uk cn ru
The new table looks like this:
---------
AD table
---------
ad_id
ad_name
..
country (INDEX)
To select all ADs for US customers:
SELECT ad_id, ad_name FROM ad WHERE country & 8 > 0
country & 8 means 1010 & 1000 = 1000 > 0
Is this a good idea?
It may be easier to search that kind of field.
Hope this helps
Michal Cibor
PS. The 1 column per 1 country is a very baad method
My reaction to this, though, why have a relational database if you aren't going to set up relations? I think that with well-indexed tables, the speed difference would be minimal.
---------
AD table
---------
ad_id
ad_name
---------
countries table
---------
country_id
country_code
country_name
country_time_zones
country_languages
---------------
countries_to_ads
---------------
country_id
ad_id
SELECT a.ad_id, a.ad_name FROM AD a
INNER JOIN countries_to_ads c2a ON a.ad_id LIKE c2a.ad_id
INNER JOIN countries c ON c.country_id LIKE c2a.country_id
Then I have a normalized table that lets me use standard SQL rather than proprietary extensions like SET or difficult to read code like bitwise ops. It also makes it extensible if I decide, as in my example, that I want to add in time zones or languages as country criteria.
[edited by: ergophobe at 5:41 pm (utc) on Aug. 14, 2005]
If there are two SET columns and a TINYINT column in my WHERE clause, and I create an index on these columns, for example: (SET country, SET category, TINYINT status), does this index still work?
SELECT id FROM tab WHERE FIND_IN_SET('us',country)>0 AND FIND_IN_SET('category_of_page',category)>0 AND status=1 ORDER BY ad_impression LIMIT 0, 1
Btw, should I add the ad_impression column to my INDEX (SET country, SET category, TINYINT status, INT ad_impression)?
Which is faster? Join table or SET column in one table?
Personally, unless one query benchmarks really slow, who cares? You would be joining on indexed integer columns. It doesn't get much faster than that as joins go. Yet, for what is probably less than 0.01 seconds savings per query, you are giving up all the advantages of a relational database and using a proprietary data type that you can't necessarily transfer to another type of DB server.
if there are two SET columns ... does this index still work?
Remember that the more repetition there is, the less effective an index is. If I have 10,000 records that are in a set with only 2 options, there is no point in indexing that column.
should I add the ad_impression column to my INDEX
Will it be a search criterion? If not, then no, there's no point to indexing it.
Will it be a search criterion? If not, then no, there's no point to indexing it.
Yes according to EXPLAIN SELECT, the index on ORDER BY clause does not work, but mysql doc said
In some cases, MySQL can use an index to satisfy an ORDER BY clause without doing any extra sorting. The index can also be used even if the ORDER BY doesn't match the index exactly, as long as all the unused index parts and all the extra are ORDER BY columns are constants in the WHERE clause. The following queries use the index to resolve the ORDER BY part.
[dev.mysql.com...]
as long as all the unused index parts and all the extra are ORDER BY columns are constants in the WHERE clause.
Interesting. I wasn't aware of that WTR to ORDER BY.
