08-22-2006, 09:13 PM
I currently have a large database with over 40 tables, it includes a main table for storing referral info, booking tables, job notes and many others. Just recently the database as started to slow down, currently my table are all using the engine format myISAM.
I have been reading up on this and it seems that myISAM is one of the quicker db engines, but also i have looked into using innoDB engine.
I have a lot of updates going on at the same time from a call centre full of eager workers and i can't have them moaning that it is running slow. If i was to swap of to the innoDB engine i think it would run better than it currently is but wot do u think????
also what sort of size to db's usually get up to before they start to slow down???? i want to exclude the possibility that SQL statements are causing the problem.
08-22-2006, 10:04 PM
My expertise is not database administration, but from a troubleshooting viewpoint I would try running various statistical software to isolate what is causing the bottleneck. Is the CPU slowing down, is it the hard drives, the RAM, the Internet bandwidth... I would first figure out what the bottleneck is rather then blindly moving to a different database engine. Once you know what the bottleneck is then you can determine how the bottleneck is related to and why it is affecting the speed of your database. I dunno, to me doing the proper research and troubleshooting will in the end save you more time and money, and you'll understand the stuff better.
08-22-2006, 10:04 PM
You've got to do explains on all your queries and make sure you are utilizing indexes whenever possible. Sometimes it's not enough just to have indexes-- you've got to make sure your queries use them.
08-22-2006, 10:25 PM
wot kinkda test can i run to find out what the bottle neck is???? i haven't deveoted much of my time 2wards SQL and spent most of ma time working with php so i not got a real good clue about SQL???
08-23-2006, 06:32 AM
well I'd say first off that 40 tables is most likely too many.
then you have to determine whether or not you are running queries within a php loop. (perhaps to get a category and then within each category you are running another query to get the sub-categories).
also use the word EXPLAIN in front of each of your queries and test them to see what indexes are being used etc.
there are so many things that can be a factor we basically can't tell you more than the simple things. In my mind though if you are running a database application but have concentrated on php then most likely there are a number of things you can do to tune your queries.
08-23-2006, 08:42 AM
y wud 40 table be to many, i have a lot of tables that are used to store info to stop data redundancy???
I have read on a website about some databases having 100's of tables and still working fine. wen i get into work 2day i am off to check each statement so wen i ave finisted i will post back with my results (most likly take me about a week or so)
08-23-2006, 03:44 PM
well the 40 tables could be too many if you are running 40 users and have given them a single table each for instance. many people make that error instead of using one main table and a column indicating what user the data belongs to.
if you have 40 tables and you have the same table schema across more than one table because you are splitting your data up into years or months for instance, that would be another reason you have too many tables.
yes, there are times when a larger number of tables are fine. you pointed out that you weren't well versed in mysql, so I was mentioning it since it is a common mistake made by many newer developers.
when checking your code, check indexes on your tables to make sure they are running efficiently. too few and your queries will run slower, too many and you slow down inserts/updates.
also check to see if you are running queries within loops as I mentioned as those are sure performance killers.