Increase speed to Database?

[Anon]

I have some problem about to show data from database ... I have 50000 record data are relation in 6-7 table
when I show SQL result is very slow about 15-40 seconds /access
I use index every table that related....and when data is a little(2000 record) is faster 1-2 secondsCan help me to resolve this problem...?
Thank you very much for every answer..:-)
Mr.Why

[[deleted]]

I assume you use a JOIN statement to get your results?
Did you make sure every table has an index that contains all the columns that you use in the join statement?

Use the "explain" in SQL to find out which indexes are used by your query.

And finally, make sure your database can be faster. Does it have enough memory? is it a busy machine? is it a high-spec machine? a P100 will not be as fast as a p3-1.5G

[brandonschnell]

1) make sure to use limit and only return a couple of rows at a time

2) make sure all of the rows have an auto_increment primary key and reference any row using the id where possible

3) use "EXPLAIN SELECT ..." on your select statement and try to optimize them so that the indexes are being used.

4) only index rows that are being used to search, not all rows.

5) and if all else fails, simplify your queries or run them once every hour or so and just show users the cached query results.