Re: Advanced. Optimizing querry performance on a huge db with over 2.5 million entries

Hi Andy, it is difficult to give you an advice without the knowing about how you are searching the database. But if you have to search through all entries, it was a bad decision to split them up into 250 tables. Some other hints: You are lost without an index, because mysql have to do a full table scan to find the matching rows. CREATE INDEX cityidx ON cities (city(10)); creates an index for the leading 10 characters of the column which needs much less tablespace than a fulltext index and will probably fitt your needs. OPTIMIZE TABLE gm_cities; releases the tablespace lost after a DROP INDEX or after deleting rows. Do you really need a char(100) for the city names? If you can use char(50) instead, the table would be much smaller. Buy lot of ram and fast harddisks. And setup the mysql-server to use the ram proberly. Good luck, Lutz news.letters@gmx.de (Andy) writes: > Hi there, > > I used to work with a table containing over 2.5 million cities. This > was verry slow even using indexes. So I did split the huge table into > 250 tables named after their country. Example: gm_cities for the german city > table. > Now performance is ok, because my interface knows in which table > to look at. > > A new problem arised. There is a need for a search on all tables to find a > specific > city. To find this city even in one table takes 0.5s which is a lot if you > put it times 250 tables. > I have to admit that there is no index on the city column yet. I tryed to > put a full text index > on the city column, but this busted up the table size from 2.5Mb to 7.5MB > which made > the querry take 1s. > > My questions are as follows: > > 1. whats wrong with this one table having 7.5Mb instead of 2.5 I did delete > the index and optimized > the table but still 7.5 > > 2. What would be the best way to organize this data to make the search on > this 2.5 mio entrys to > take not longer than 0.5s? I could put it all into one table again, but this > would meen a week of work > because my application is based on this system. Maybe a good hint for > putting the right index > would be the best help for me. > > My tablestrukture looks like this: > > ID -> mediumint(9) unique index > city -> char(100) > province_id -> char(2) index > > A nother story would be that it might take even longer because I have to get > the province name and the country name > out of the table countries and provinces > > Thank you for any help, > > Andy