Hi, I have one project for searching products from thousands of eshops. They sending me XML feeds and I import they data to 4 my tables (actualy its more, but this 4 is using for joins). products = main product data products_codelists = in which extra info it can take (for woman, gifts for birthday ..) eshops = info about eshop source products_categories = in which categories are When I wanna SELECT data i need to JOIN all these tables. If the table was less then milion it was quite fast (less then 1 sec). But when it became over the milion its getting slower (still in seconds 5-15s). I need to keep it under 1 sec. Any suggestions? It cross my mind put all key data for selecting into product table, for example 5 more fields in table for codelists, 5 more for categories, etc. and update them during the import. So i dont need that relation tables and joins anymore. I guess direct select from one table has to be faster? But this solution doesnt seems right to me What could I do if the table hase 5mil+ entries and growing? Thank you for helping me!
What query are you using to select data? Also, what type of storage engine are you using, server hardware specs, my.cnf/ini tuning?