automating idx updates

[homercles]

I have managed to import the data to MySQL. There are 82 columns of data of which I need probably only 20. Will the unused data slow down queries noticeably or can they be left?

I will keep googling for solutions. If anyone has suggestions on PHP querying the database let me know. Thanks.

[frobn]

I have managed to import the data to MySQL. There are 82 columns of data of which I need probably only 20. Will the unused data slow down queries noticeably or can they be left?

The query speed is dependent on server resources, database configuration, the amount of data per listing (columns) and the number of listings (rows). Its good practice to eliminate the columns you do not use. If you have more than a couple thousand listings you will likely need a dedicated or virtual dedicated server.

I will keep googling for solutions. If anyone has suggestions on PHP querying the database let me know. Thanks.

You query the db from a form with the selection criteria like price, beds, baths, etc. For example, <form action="search_results.php"> where search_results.php is the code for the way you want the search results from the query to appear on screen.

[homercles]

I do not have the full MLS yet, just a sample I am working with, but I believe there will be 6-7000 llistings on average. I do have a VPS with about 35gb available, however my inodes only about 80k are available so I'm not sure how that will impede things.

Is there a good piece of code I can use in a .sh file to remove unused columns after the import?

[REW Fergus]

Homercles, if you are concerned about query speed (and you should be), an important step is ensuring you setup the correct indices to support the queries you intend to run. The SQL "EXPLAIN" statement can be invaluable in doing this, and it's definitely something worth looking up. Failing to utilize indices where you need them can grossly slow your queries, and watch out for using the LIKE operator in your WHERE predicate -- LIKE is very slow.

[homercles]

OK thank you for the advice. For the MLS I in my area they use numbers for zones like TownnameA 100, TownnameA 200, TownnameA 300, TownnameB 800 etc. However I would like people to be able to search for the entire town like TownnameA or TownnameB as well as the smaller areas. I had been using like '%area%'. It seems to be working ok for now but I don't have 6000 properties imported yet or heavy traffic. What would you suggest?

Also I have gotten the MLS aspect of things set up pretty well, but I need to build a system for agents to login and mark their properties and possibly hot properties and users to create accounts to get emailed new listings matching their criteria. I am not sure if I should create new MySQL databases or just new tables. Is there is a difference for speed between 3 databases one table each or 1 databse with them all.

[langard]

Stay with tables. Multiple database connects/validations get dicey and slow down the queries. If you throw out the redundant/nonsense columns you don't need from the MLS, it speeds things up enormously.

So far as their "area" designations, I'd make my own if I were you and assign a separate field to it that you can use as an index for your own searches. Run a script when you import their data that auto-assigns your own regional numbers -- or whatever -- as a separate location field for your own use. That way you can consolidate regions easily and solve your search-speed problems by essentially throwing out theirs.

Alternately, if you make separate tables for your own regions (to split up the glut of information and keep the query results reasonable) you might want to construct them with the identical field names in the schema so that you can move or cross-reference them easier if need be.