Reduce Query Execution Time

[Mr.Ram]

Hi, how can we reduce the time of query execution time? I m using mysql. I have to execute one query which select data from lot of tables . It takes more time to execute. Please give me good suggestion for this.......

[debasisdas]

Check for proper join and existence and use of indexes in the table structure.

[Mikav6]

Hi, how can we reduce the time of query execution time? I m using mysql. I have to execute one query which select data from lot of tables . It takes more time to execute. Please give me good suggestion for this.......

- If table index uses all the columns in the select statement and in the sort order used in the select, it'll speed things up (db only needs to use the index-table). State column names in select explicitly (select groupid,name,price), don't use select *. This helps the database optimizer to do it's work.
- For massive queries data dublication speeds things up. e.g. don't always use (just) 2nd or 3rd normal form. If you now join two tables to get value of one column from the 2nd table, you can eliminate the join by adding/duplicating the column in the 1st table. This way you can speed up the query at the cost of disk space.
- At the extreme you could have just 1 table with all the columns you use in your query. It would of course be very fast to read (slower to insert/update because of data duplication) but takes up a lot more disk space. At the other extreme is 3rd normal form database. The optimal case is somewhere between these two.
- You might also speed things up by splitting the query into several parts : 1st insert results from one quert into a temporary table and then use the temporary table in the next query. Or add the temporary columns in the tables themself and 1st update the values for the temporary columns and then do the final query using them. It all depends on the database schema,query and data itself.

[mwasif]

Read this article thoroughly. It will be helpful.