Hey, I need your suggestion, what I try to do is to divide big table into couple small ones , so it could work faster, it is working faster but I found an issue and cant decide what to do. What I am thinking is that - should be divide table into 10 pieces or 100 pieces, 100 pieces will work very very fast but I dont know how to run full text search thru all those tables, like its more faster to run 1 search thru 10 tables than thru 100 tables or should I join tables and how fast is that. Need your suggestions, thank you very much
UNION should help you run the same search in multiple tables.. How fast it is is a matter of indexing, structure of the tables and so on. Give us more information on what you want to do and we´ll be able to answer more specific.
well I got a full table with about 4 million ads and its about 3gig in size, my issue is extracting specific ads for specific categories even with indexing getting very slow, so what I did is divided that table by categories into small ones, but when I need to run search thru the tables (if not specific category chosen) I still have to join them together
Use MySql MERGE engine. It will be very helpful to you. http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html
You cannot search the MERGE table with FULLTEXT search (its a MERGE limitation). With the current design you should go as n3r0x suggested. I recommend you to useSphinx if you have that much amount of data and your host support it. If you have a dedicated server, then you can set it up easily. It is realy fast in FULLTEXT searching as compared to MySQL.
I agree with mwasif. I missed on checking that MERGE does not support full text indexing. If you have dedicated server, Sphinx is worth exploring.. I am also trying to learn it..
It will be a good option get better speed for your case if you didn't need FULLTEXT. For complete details checkout MySQL manual.