Unanswered: How Do I optimize MYSQL search results?
I am total newbie here, so my greeetings to all the members and admiinnistrators.
I have 8 tables, each has fields ranging from 290-310, and all are default MyISAM type. All has one index field, that is entry_id, which is auto_increment field.
Now When I conduct search, based on 20 different paramaters on any of these table, the search times ranges from 0.5 seconds to 40 seconds depending on the paramaters I have used to search the table as well as number of records the search finds in the result set.
Now my question are:
1) Is this a reasonable time?
2) What are ways that I can minimize the search result time, so that it comes close to 2-3 seconds for any number of parameters I use to search the table?
The scripting language I am using is PHP 4.3.8 and MySQL 4.0.20-standard .
Any help in this regard will be highly appreciated.
there are two types of database performance tuning. Both are used to help return the result of SQL queries in the shortest possible amount of time. One looks at database table structure. The other looks at SQL/query design. They kinda go hand in hand.
you have not indicated the number of rows in each table but I'd guess that the SQL/query design is to blame for the high search result times. if the SQL queries are looking through a lot of data perhaps a few strategic secondary indexes would help.