473,324 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Query Execution Time

I m using inner join where there is no keys (Primary and foreign)
i want to know that Does key affects execution times?
Apr 4 '08 #1
2 2419
Hi, abhishektiwari,

Yes. If you have lots of information then it slows queries down by a lot. You need to find a balance and set keys on the columns you use most in SELECT but use as few keys as possible and try to avoid keys for columns which you UPDATE or INSERT often. I once managed to speed up a database access about 100 times by adding primary keys (the amount of joins in the queries was unbelievable).

Keys speed up SELECT a lot and slow down UPDATE/INSERT/DELETE.

Regards,
Tom
Jul 28 '08 #2
coolsti
310 100+
Try to prepend the word "EXPLAIN" to your query and then run it from a mysql console window (or any application that allows you to type in the query and see the query results).

The EXPLAIN keyword will cause mysql to not carry out the query, but to provide you with a table containing information about what mysql needs to do in order to carry out the query. In particular, it will tell you whether ALL rows of a table need to be examined or only some of them due to the presence of an index.

This may help in giving you an idea of what indexes would help best. You could add the index, run the query again with EXPLAIN, see the results, and then remove the index and start all over again with another index.

If the EXPLAIN query shows you that too many tables need to examine ALL rows, then you definitely can speed things up by adding an index (or indexes).
Jul 30 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Andrew Mayo | last post by:
There is something very strange going on here. Tested with ADO 2.7 and MSDE/2000. At first, things look quite sensible. You have a simple SQL query, let's say select * from mytab where col1 =...
6
by: Muharram Mansoorizadeh | last post by:
Hi there, I've a table with 18 millions of recordes shaped like this : Code nvarchar(80) , State int , school int , class int , Term nvarchar(80) The following query takes too long to run ( more...
3
by: Will Atkinson | last post by:
Hi All, I'm a relative newbie to SQL Server, so please forgive me if this is a daft question... When I set "Show Execution Plan" on in Query Analyzer, and execute a (fairly complex) sproc, I...
7
by: Thomi Baechler | last post by:
Hello Everybody I run the following query against to identical databases. Execution time on the first DB is 0 seconds, on the other 6 seconds! SELECT dbo.HRMABZ.EMPKEY ,...
6
by: UnixSlaxer | last post by:
Hello, Running a query for the first time on DB2 takes a fixed amount of time. But when query is executed for the second time, the amount of time is usually less since the query is (most...
6
by: Ryan | last post by:
I came across a situation that I've been unable to explain and was hoping somebody had an answer: I had written an update query which was taking about 8 seconds to run and considered it too...
3
by: serge | last post by:
How do I determine which method I should use if I want to optimize the performance of a database. I took Northwind's database to run my example. My query is I want to retrieve the Employees'...
3
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these...
3
by: rcamarda | last post by:
Hello, I ran a query that I thought would take an hour, but instead took 14 hours to run. The consequence was it bogged down our data warehouse and the overnight build was adversely impacted. Is...
1
by: Don Li | last post by:
Hi, Env: MS SQL Server 2000 DB Info (sorry no DDL nor sample data): tblA has 147249 rows -- clustered index on pk (one key of datatype(int)) and has two clumns, both are being used in joins;...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.