* [EMAIL PROTECTED] > > I have a question on how MySQL JOIN has effect on query (search) > performance: > > Our database consists of about 250.000 datasets (rows). > Right now, all of the 150 columns are in one big table. > > However, we do have certain columns that are empty for most rows (for > example information of the status of pictures of customer). > Out of the 250.000, only 20.000 have information on photo-status. > > The idea is to put the columns (amount: 5) about the photo-status in a > seperate table to not blow up the database (to normalize the database ?).
This is called database normalization, yes. > A search over, lets say, 15 columns, that includes one columns out of the > photo-status-columns would then have to JOIN these two tables. > Does that lead to performance drop ? Probably not, because the amount of data to process is smaller (Your 230.000 x 5 empty photo-status columns are gone) and the join lookup is (should be) indexed. > Or is maybe even faster to search with a JOIN, because we only > have 20.000 > rows in that seperate photo-status-table ? Eh... right. It is probably faster with the join. :) > What would probably happen if we did this with, lets say, 5 other > data-sets > (we would the have 7 tables connected through JOIN) ? The query would probably be even faster. :) There is an upper limit on how many joins you can do in a single statement, but this is rarely a problem. (32 in version 3.22 and 64 in version 3.23, according to crashme.) > I hope someone can help me with performance issues about JOIN. As long as all joined tables have an index (normally you join using the primary key, which is a perfectly good index, but any column/combination of columns could be used, just make sure the used columns are indexed!) there are (afaik) no performance issues with JOIN. On the contrary: most mysql databases (of some size) will be faster when normalized. -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
