arthaeus

OK, now I can understand what you were talking about with the redundancy. If i change one of the tables, the other must change as well, introducing more work. How about this: I ONLY insert the search data into the search table, then extended profile data goes in the profile_data table. I believe this would remove the dependency and redundancy, while allowing for flexible profiles and quick search. You have really helped. Thank you!

Hey dbo what I ended up doing was creating a different table with ONLY the searchable fields included. I figured that since I will ONLY EVER use 9 fields to search, I wont need to make this table any wider. I will still be able to create additional fields for the profiles, but these will be displayed on the profile as tags. These tags are searchable via my tags algorithm, which is completely independent of my new table. This new table has DRASTICALLY reduced my query time from 9 seconds to .10 seconds when I search all 9 fields on a 100,000 member dummy db. OK, now can I get some input on these changes, and I have a couple questions about this approach. First, is .1 seconds acceptable for a query on a live site? If not, what range should I aim to get it within. (now that i think about it, i havent added an index to that table, so that should speed it up even more) Second, knowing that I will not ever expand my search table, was adding this table a smart thing to do to speed up my app? Thanks!!

exactly the kind of answer I was looking for. many good points that i will certainly look into. Thank You

hey dbo. What is a good response time? when you talk about doing the work on the server are you talking about using stored procedures? just one more quick question for you too, would it be quicker if i created another table based off my profile_data table so that one row would hold all of that user's attributes? for example firstname middlename lastname age foo bar key --------------- --------------------- ----------------- --------- -------- -------- -------- mike john jones 30 bar foo 001 then i wouldnt need subqueries. it would be select key where foo='bar' and age='30' and middlename='john'...... etc. what are your thoughts on making a seperate table for queries

One more thing.... here is the query, right: select * from elgg_users eu where ident in ( SELECT owner FROM elgg_profile_data WHERE name='firstname' and value = 'mike' ) and eu.ident in ( SELECT owner FROM elgg_profile_data WHERE name='lastname' and value = 'jones' ) and eu.ident in ( SELECT owner FROM elgg_profile_data WHERE name='middlename' and value = 'john' ) now this is only for a 3 attribute search. i can build a bigger query by just adding more: and eu.ident in ( SELECT owner FROM elgg_profile_data WHERE name='middlename' and value = 'john' ) clauses. will this be efficient for a query that could be as specific as 10 elements?

just a little recap: i have a table who's structure is as follows: CREATE TABLE `elgg_profile_data` ( `ident` int(10) unsigned NOT NULL auto_increment, `owner` int(10) unsigned NOT NULL default '0' COMMENT '-> users.ident', `access` varchar(20) collate utf8_unicode_ci NOT NULL default 'PUBLIC', `name` varchar(255) collate utf8_unicode_ci NOT NULL default '', `value` text collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`ident`), KEY `owner` (`owner`,`access`,`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=19001 ; as you can probably tell, each attribute about a user is stored on a separate row ie if a user has a firstname, lastname, and middle name properties, they will have 3 corresponding rows in the database with the row.name = firstname, lastname and middle name. and it's corresponding row.value = mike jones john. here's the query i developed to search for a user. select * from elgg_users eu where ident in ( SELECT owner FROM elgg_profile_data WHERE name='firstname' and value = 'mike' ) and eu.ident in ( SELECT owner FROM elgg_profile_data WHERE name='lastname' and value = 'jones' ) and eu.ident in ( SELECT owner FROM elgg_profile_data WHERE name='middlename' and value = 'john' ) now i pumped this database with over 1 million rows of dummy data, and this query took 9 seconds. is this unacceptable? not looking for coding help, just a "it's a go", or "its a no go" (and why!) thanks so much for everyone who has already helped

lol! hey, let me post this a different way. CREATE TABLE `elgg_profile_data` ( `ident` int(10) unsigned NOT NULL auto_increment, `owner` int(10) unsigned NOT NULL default '0' COMMENT '-> users.ident', `access` varchar(20) collate utf8_unicode_ci NOT NULL default 'PUBLIC', `name` varchar(255) collate utf8_unicode_ci NOT NULL default '', `value` text collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`ident`), KEY `owner` (`owner`,`access`,`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=19001 ; is it me, or does this table design not make sense? one row for each attribute. i guess it makes sense if you want to be able to add fields to a profile, but how are you supposed to do a very specific query when the table is designed like this?!?!?! if a user had 10 fields in their profile, there would be 10 rows of data for that user! lots of duplication, and i always understood that data duplication should be avoided. wouldn't it make more sense to have ONE row with all your data so you could make a query like select * from foo where param1 = 'bar' and param2 = 'foobar' and param3 = 'barfoo' so easy! i have no idea how to query my current table for more than one attribute. is this bad database design??

hey thanks for the reply. i already have the code that will return all of the zips within X miles of a particular zip. a little more info. the database i have to use for the users is arranged like this. i didnt design this database so bear with me. makes more sense to me to put all this information in one row? no? so for my hypothetical user, his entries would look as follows table user_details ------------------------------------ userId 100 trait_name car trait_value ford userId 100 trait_name beer trait_value budweiser userId 100 trait_name zip_code trait_value 90210 3 rows in the table for 3 traits???? i dont know why they did it this way. i just want to know the most efficient method of searching for users who for example: drive a ford, and drink budweiser beer, and live within 100 miles of 90210. lets say for example that within 100 miles of 90210 we have zip codes 90211 90212 90213 90214 ... ... ... If you were going to solve this problem would you do : select BEER and CAR where zip = 90211 select BEER and CAR where zip = 90212 select BEER and CAR where zip = 90213 select BEER and CAR where zip = 90214 this would give all of the users within these zip codes who drive CAR and drink BEER.

hello all. i have a question regarding an efficient way to create a query that looks for users based on zip codes. heres a rundown my site will have many users with many searchable fields. a hypothetical example follows: name : foo username: foo car_type ford beer_type budweiser ZIP_CODE 90210 etc.. ok now i want to be able to search for users within X miles of a zip code. for example, if i wanted to search within 100 miles of the zip code 90210, there would be about 75 zip codes to search through. and further, i want to know who drives a ford and drinks budweiser within 100 miles of 90210. i was able to put together a working query on another website, but i am no sql expert so i have no idea where to start in terms of optimizing it. NOW FOR THE QUESTION would you approach it by creating 75 different queries, one for each zip code in which you search that zip code for car_type = 'ford' AND beer_type = 'budweiser' ? there has to be a more efficient way. or was i already right? Thank you very much

I can write a string to the screen, but if i want to put tabs between them, php will print ONLY ONE space instead of the tab. if i replace the '\t' with 50 spaces, the screen will only output one. im trying to create a tree like structure like the following element one *<tab>* response to element one element two *<tab>* response to element two each of the tabs is reduced to one character. can anyone help??