3 replies to this topic

[missionaire]

Hi,

I am currently having more than 1000 data rows in one of my tables, and when I tried to run a data retrieval for all my data, the process is terribly slow.

I only have the primary key as an index, and the data retrieval will retrieve data from 2 to 3 other tables, together with the table that contains 1000 rows.

Is there any way that I will be able to optimize the database to make it run faster?

Thank you very much.

[Demonslay]

Well, as far as I know, all you can do is optimize the table with indexes for columns you would frequently use for indexing your search, and use those indexes in your WHERE or ORDER BY clauses.
The second half is optimizing your actual query, using more clauses such as WHERE, ORDER BY, and LIMIT.

[Hayden]

OPTIMIZE TABLE  `table_name`

or

OPTIMIZE TABLE  `table_name`, `table_name_2`

for multiple tables.


you can put it in a PHP script to run in a cron job automatically once a day.

[Tirus]

Demonslay, on Feb 7 2007, 12:47 PM, said:

Well, as far as I know, all you can do is optimize the table with indexes for columns you would frequently use for indexing your search, and use those indexes in your WHERE or ORDER BY clauses.
The second half is optimizing your actual query, using more clauses such as WHERE, ORDER BY, and LIMIT.

I completely agree with Demonslay...the appropriate table indexing, combined with the proper queries is what will optimize your speed at finding results. After optimizing tables with thousands of rows, I can safely say that indexing is your friend. I am sure P2L has some tutorials on indexing and if not, just do a search through google, I found quite a few good information sites from there.

Good luck!