I have been looking at stock photo sites lately, started wondering how they are doing their keyword searched. Given a potential for millions of images, each with x keywords, I have come up with two approaches...
Approach one Images table, with a parent id Keywords table, each keyword would be its own row, and also link back to the parent id. You could then search for the keyword, get the parent id's, and do a IN (...) search against the images, thereby pulling up the images that have those keywords. Potentially problematic as assuming million of images, and average 10 keywords per image, you end up with a keyword table that has 10's of millions of rows, along with lots of duplicate keywords. Approach two Use a link table, this resolves the duplicate keyword issue, and I am sure there will be many dupes. However, it adds a third table. This would complicate the JOIN query. Are there other approaches? Which approach would yield the best performance for growth issue? -- ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 <http://www.newgeo.com> Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
