increase performance while retrieving thousand of record from mysql

Question

I have one table that contain more that 40 thousand record when I retrieve record that satisfy particular condition it takes so much time.

Is there any solution to increase performance ?

Answer 1

If you are doing a lot of querying based on another column that isn't your primary key, try adding an index to that column you are putting in your WHERE clause.

Also, optimizing JOINs will help if you are using any.

Answer 2

You can use indexes to increase the performance, check 7.5.3. How MySQL Uses Indexes.

Answer 3

You could also run explain command, for example 'explain select * from mytable where someField = 'Something'; ' and see what it returns, but I'd think 'index' solution should be good enough.

Also, maybe you could consider splitting table into few simple tables. I don't know what is your structure, but I have seen in the past examples, where people would keep redundant data in the same table, for example: have customer name and address in the same table, where you could split it into customers and addresses, and then perform queries on one table only.

Hope this helps.

Answer 4

First step: find out whether or not the time is being spent running the query, or transferring the data back to you.

How long does it take to run this query-

SELECT COUNT(*)
FROM
( [your query that returns 40k rows] ) AS butts;

If it's quick, then most of the time is probably spent transferring the data back into your client. Look into using MySQL's compression.

If the server is taking a long time with the query, it's probably something that can be fixed with proper normalization and index use, as the other people here have mentioned. For help optimizing a specific query, you should paste the CREATE TABLEs for all the tables involved, and the query itself.

Answer 5

Another simple optimization for SQL is to return fewer columns from a table. If you only display two fields from the SQL table, don't retrieve the rest of the fields until someone needs to see them for a specific record.