Will more MySql tables slow down searches on MySql database?

Question

I have a classifieds website, and I am thinking about redesigning the database a bit.

Currently I have 7 tables in the db. One table for each "MAIN CATEGORY". For example, I have a "VEHICLES" table which holds all information about the following categories of classifieds:

   cars
   mc
   mopeds/scooters
   trucks
   boats
   etc etc

However, users on the website usually search in specific categories. For example, the user chooses the "cars" category to search in, and enters a keyword.

My code today, will search the entire VEHICLES table for all records with the field "category" equal to "cars", and then get their details:

 "SELECT * IN vehicles WHERE category='cars' AND alot of other conditions" // just for example, not tested

I am thinking about making a table now, for each of these "sub-categories". Ie, one for cars, one for mc, one for trucks etc, so that search isn't done through information which isn't needed.

Will this increase search speed? Because I have calculated that I will need atleast 30 or so tables for this.

Thanks

Answer 1

With a properly indexed table and a "reasonable" number of rows, you will not gain much speed from this approach. Anything you gain in speed of execution you will lose in time-to-market because your programming will become more complicated.

Do not perform this optimization unless and until you encounter a performance problem in testing with a representative set of data.

Answer 2

It will increase the speed of a search within the same category. It will potentially slow down queries where you need aggregate information from the different categories. You need to decide which is the best option for your site.

How many records do you have in total in the vehicles table. Its quite likely that adding proper indexes will greatly increase the speed of your searches.

Check out the 'EXPLAIN' query option in MySQL. Understanding this will help you optimize your database a lot with indices.

Answer 3

if you are using php try something like

$query = mysql_query($sql); while($row = mysql_fetch_assoc($query)){ $tempvalue[]=$row; }

and then to loop the info use for like sentence

foreach($tempvalue as $key => $value){ write the table ..... }

maybe mysql isnt slow and the problem is in the code

test dont kill anyone =)

Answer 4

Performance optimization is as much art as science, and to really understand what's the best option requires that you do some benchmarking; anyone offering a definitive answer given the available information is just wrong. That said, a few thoughts on your situation:

• You don't say what type your category column is now, but if it's a string type, it's probably using more space than other options, thus making the table larger. Proper indexing can help tremendously with speed, but a larger table with larger indexes will always work to do just the opposite.

• As already mentioned by someone else, your queries within a category will be faster in the simple case of a category search. How much faster depends on how much data you have in your current table, and the increases may be negated if you have to join in other tables to satisfy the need for all the other conditions to which you alluded. OTOH, it may actually speed things up in certain join cases (e.g., if you were doing self-joins with your all-encompassing table).

• If you're working with a lot of data, splitting into multiple tables can greatly ease backups.

• Splitting into multiple tables may also make it easier to shard your data across multiple servers for performance reasons. Similarly, it may make replication setups easier to keep running.

• If you're tracking data that's category-specific, separate tables enables you to better normalize your database and likely reap some nice performance as a result of using much smaller tables.

• Splitting obviously means modifying your code. If your code is of the old, creaky type, you may very well achieve a performance gain from the clean-up. Of course, there's also the risk that you'll break something....

• Check your indexes. Bad indexes are a very common cause of poor performance but are relatively easy to fix with a bit of quality time spent on self-education. MySQL's EXPLAIN can tell you whether your queries are using the indexes, and the index stats (look in the docs) can tell you how efficiently your indexes are working.

• Finally, speaking of code, check yours. Try experimenting with a few approaches, regardless of how the database is set up. For example, it may be quicker to do a couple of separate queries and join the results in code than to do the join in the database. Likewise, it's often quicker to do things like sorts in code, particularly in cases where a join or something means the database would have to create a temporary file/table. Again, check the EXPLAIN output, and if you can't eliminate a problem area in your queries, see if it helps to simplify the queries and do more work in the code. This can be particularly beneficial in the common case where the web server has more resources to spare than the database server.

There are many more factors to consider. Ultimately, though, the best way to make these decisions is not to spend time pondering theories but to put both methods to the test. Create some test databases and benchmark the sort of queries you'd run most often, with and without simulated load. You'll get your answer.