when my database had 2 million rows everything loaded intantly, added 6 million more rows and now it takes 10-15 seconds to load a page... any simple fix? i have a decent dedicated server , dual processors, 2200mhz 2gb rams. i dont know much about databases but can i index every field on the table or use fulltext or something that will make it faster
Indexing every field would make it slower. The best option is to index fields that are common to more than one table, and fields that are commonly used in a where clause. Make sure you aren't making select *, count * statements. Foreign keys can slow things down a bunch as well. What kind of database are you using, MySQL, MSSQL, etc., and on what operating system? Also, is this a single table you're using, or many?
mysql db select * from companies WHERE category like '%$tcategory%' AND state like '%$state%' AND city like '%$tcity% 1 large table has 10 fields
i think i got it, as there is 700 categories 20,000 cities 50 states tried reversvng the query to start with state as it has the least and end with cities as it has the most, that seemed to change the time from about 15 seconds load time to about 5 seconds, now if i use = instead of like loads almost instant
The Select * and the Like '%%' are very slow just by the way they are designed. If you can, only select specific columns (SELECT id, name, state), and use indexed int (tinyint, smallint, int, etc.) fields instead of char or varchar fields.
As said, your problem is using Like statements as these are terribly inefficient. For things like state, category and city I would expect you to be able to have exact matches rather than having to use like statements. These can be held in seperate tables and cached to fill drop down lists for the user to select from having an integer value. Your search then becomes an exact match on 3 int/ smallint type columns which will be massively quicker.