Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have an ASP.Net application that is using a MySQL database. My queries are not executing as fast as I would like them too. Are there some standard ways to increase the speed of a MySQL database short of putting it on a faster PC.

For example, (I'm not sure if these are true or not) using prepared statements over transactional SQL. Using foreign keys if applicable? Things along those lines.

EDIT: The database is on a remote machine

share|improve this question
    
Probably better migrated to stack –  Gratzy Nov 7 '11 at 21:44
1  
There is no silver bullet or the DB would automatically do it for you. Your best bet is to evaluate your indexes and tweak them to the type of queries you are running that are slower than you'd like. –  JohnFx Nov 7 '11 at 21:59
    
@Gratzy: For this to make sense on stackoverflow, OP would have to post some sample queries and execution plans. Right now, it's a bit too high-level for stackoverflow. –  FrustratedWithFormsDesigner Nov 7 '11 at 22:06
    
As @JohnFx writes, it's not possible to know why your queries are slow. You should profile your queries, identify the ones that are lagging and ask specifically on them (on StackOverflow). Every RDBMS has its quirks and tricks, there are of course several high level optimization techniques that should apply to most (as answers bellow explain) but they may or not may solve your problem (I have 2*10^6 blobs in one legacy db, indexes don't do much). If you are interested in those high level techniques you should rephrase your question (or ask a different one). –  Yannis Rizos Nov 7 '11 at 22:07
1  
Ah, network is involved. Then consider how fast you can move data, and if you need all the data you move. –  user1249 Nov 8 '11 at 7:12
show 1 more comment

5 Answers

Speeding a database is a big subject, also, you need to tell us whether you have problems in querying the database, updating or deleting data. However, there are known facts that you can check:

0 - Make sure you are using the appropriate driver to connect to the database and that you are using the correct way to connect (for example using ODBC may be slower than a native driver). Use connection pools.

1 - Have a correct design.

2 - Have PK and FK defined with the same data type to speed joins.

3 - Create indexes on PK and FK for non-trivially sized tables

4 - Choose the right type of indexes

5 - Optimize your selects. Avoid "SELECT *" and don't join to tables unless you need to

6 - Qualify your selects properly so that the number of data rows returned is just enough to do the business function at hand. Don't return all the data all the time unless you need to.

7 - Avoid using large binary objects in queries. Consider removing photos from database to file system storage if possible.

8 - Use aggregate functions and ORDER BY wisely. Choose your clustering index so that you could avoid sorts if possible.

8a - Avoid using Not in WHERE and attempt to avoid complex transform operations.

8b - Make sure indexes are used in your queries otherwise tune the queries to utilize indexes or create the necessary indexes.

9 - Use a single column to build the key rather than multiple columns when possible.

10 - Check your physical design of tables and indexes. See how your space is allocated

11 - Consider index rebuilding and file system defragmanation

12 - Check strategies for fine tuning full-text-search (if you are using it) - See: FTS

13 - Determine if your network speed is good enough.

14 - Compare the transaction time in your ASP.NET application vs. the same query or transaction when performed on a console. The difference should be close. If you find a big variation then the problem may be with how you connect, the network or some other problem.

share|improve this answer
add comment

What you want to do is learn how to profile your queries using the MySQL EXPLAIN command (doc). That will tell you how the query is pulling data out of the database - whether its scanning rows one by one or using indexes. Using that, you can often dramatically speed up queries. Of course, if you're not now using indexes, that would be the first place to start...

The effect of prepared vs non-prepared statements... thats generally minimal. You'll want to profile that too in your code, but only after you've optimized indexes based on what you learned from running EXPLAIN.

share|improve this answer
    
Prepared statements help with efficiency and to some extent scalability for larger volume situations. Generally the database will have to work harder with unprepared statements versus prepared one. So, in high volume situations prepared is better (database will be able to handle more of them at once), although the speed difference of the query will be marginal. If this is a single user instance then prepared versus unprepared makes no difference as indicated. –  Jon Raynor Nov 7 '11 at 21:57
    
+1 EXPLAIN/SHOW PLAN type functions and indexing are bread and butter database optimization that everyone should know. –  Jeremy Nov 7 '11 at 21:58
add comment

First thing to consider is if your indexing is correct. Are your queries sargable (ie can they use the index if it exists)? Are your foreign key fields indexed?

Next are you doing things in a set-based manner or looping through records which is often slower in a database.

Are you using known performance killing techniques (in sql server this would include the use of correlated subqueries, cursors, scalar UDFs, functions in joins etc., not sure what the known killers are in MySQL but there are books available I'm sure on the topic.

Are you using select * and returning more data than you need especially in joins where at least the joins fields are repeated.

share|improve this answer
add comment

Some things that could be generally happening here:

  1. The queries are slow. Run the query directly on the MySql instance. Host fast? If it takes a few seconds tuning the query is necessary with additional indexes, etc.
  2. There is too much data coming across the network. The query could be running instantaneously, but the amount of the data coming over the wire is too much. Return less data or page the data back to the client would be some suggestion(s).
  3. The server is real busy. What's the CPU and memory look like on the server? Is it close to 100%?

Prepared statements don't make that much difference until the server has a high volume of queries coming through. Speed wise prepared versus unprepared are just about the same, but with prepared statements the server doesn't have to work as hard, so it can use more resources to do other things.

share|improve this answer
add comment

Is the database write the only place you can speed up your program. Without more information on your use case, is it possible to use a topology of a write-behind cache?

A write-behind cache will allow you to put elements into a cache to be written to a database at a later date. For database entries that do not have further dependencies (such as being read from the database within a set time period) this would be ideal as once an element is in the cache, it will be in the database as far as your application is concerned.

This is the ehcache on how to implement a write-behind cache and I have also linked to a decent video presenting how a write-behind cache can remove a database bottleneck from an application. http://vimeo.com/21193026

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.