Search results for query: *

Ok, I have done some stress testing... I made two different setups: Setup A: Multiple tables. Each attriute datatype has a different table. I have some attributes that are in an integer table, some that are in a dimensions table, some that are in a float table. CREATE TABLE prop ( id...

Wow, so much good information. Ok, now I am kind of understanding this from a different angle. One of the big problems I had with the 150 tables was running out of file descriptors on the OS and that is why I did not want to implement this concept before getting some additional ideas. Rudy...

Now you have me itrigued. Perhaps because I have programmed with joins so long, I am brainwashed into using them. So lets, says all attributes are in one big table... CREATE TABLE `homes` ( `fkey_prop` MEDIUMINT UNSIGNED NOT NULL , `fkey_attribute` TINYINT UNSIGNED NOT NULL , `value` VARCHAR(...

I completely agree with you about not worrying about the cost of space. That isn't a concern of mine. This is going to run on a dedicated UNIX MySQL server dual processor with 4gb RAM and 15,000 RPM SCSI Ulta single platter drives. I'm not trying to save a couple bucks here. My space concern...

Rudy, The problem with 150 attributes all in one table is the attributes can have various data types. "Number of bedrooms" will never be more than 288 so TINYINT will work just fine. "Price" requires a MEDIUMINT. "Number of Bathrooms", in the USA, often includes a...

I am wracking my brain trying to figure out the best way to design the database. The database will store real estate data. My biggest concern is speed of searching. Lets say I have a home which has 150 different attributes to it. Example attributes would be price, year built, square footage...

The table is for real estate data. The table prop_int contains different attributes of homes. Example, the price of a home is attribute #35, its square footage is attribute #47. A person can search the database of 200,000+ homes by specifying a lot of optional criteria. The could put a price...

I should have posted all in one post... I keep reading my post and realizing I left stuff out. One row of data from table prop is 44 bytes. One row from prop_int is 69 bytes. Prop_int is joined 5 times, so that means a total row size of (5 * 69) = 345 bytes. Plus the 44 from the prop table, so...

Oh, if the query provides any insight.... SELECT p.id AS propid FROM prop AS p USE INDEX(x_mfl) LEFT JOIN prop_int AS t5 ON (t5.fkey_prop=p.id AND t5.fkey_woprattrib=5) LEFT JOIN prop_int AS t6 ON (t6.fkey_prop=p.id AND t6.fkey_woprattrib=6) LEFT JOIN prop_int AS t33 ON...

The query is complex and returns 16,000 out of 240,000 rows. Proper indexes are used and the query is relatively fast however it always uses temp tables and filesort. I am trying to speed it up though. When I do an explain I get...

That makes perfect sense. Thanks

hvass, Thank you! The fkey_session does have less unique values so by moving it to the beginning of the where clause and redoing my compound index to be fkey_session+stamper instead of stamper+fkey_session it is now using the index and is fast. Now though I am concerned that my knowledge of...

Hello everyone, I am having a strange problem I cannot figure out... I have a table callled tracking_clickstreams: id - unsigned bigint primary key unique fkey_session - unsigned mediumint fkey_url - unsigned mediumint stamper - timestamp The table has an index on the id field, and a compound...