Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a query I need to make more efficient. Trying a couple different things, one of which is SHOW PROFILE . That shows that the most massive timesink by far is "preparing".

As far as I understand, that state indicates that the database is planning, preparing and generally thinking about how to get the results most efficiently which leads me to belive that it can be reduced by providing hints, indices or rewriting the query.

Exactly how those hints, indicies or rewrites should be made escapes me though.

How do I go about optimizing "preparing". All I have now is a single profile row and a search enginge that directs me to articles about prepared statements.

PS: I realise that the "correct" answer probably is "Rewrite everything into normalised tables, relations and make a better query", which is what I'm actually doing.

share|improve this question

closed as unclear what you're asking by Paul White, Mark Storey-Smith, Max Vernon, Marian, Kin Feb 20 '14 at 14:32

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

Yes "write better queries against a database with a better design" might be the answer, but you should probably consider updating this question with an example of the query that's spending excessive time in the "preparing" state so we get some idea of what you're dealing with. – Michael - sqlbot Feb 3 '14 at 2:28
@Michael-sqlbot Would an example really help? I've already figured out that the JOINs were bad; and I feel that the example would only distract. The question is "What do I look for when a query spends a lot of time 'preparing'?", not about any particular bad example of SQL. – Odalrick Feb 3 '14 at 11:54
up vote 1 down vote accepted

Preparing also include parsing and compilation of query into native inner code. Replace direct queries by stored routines that compiled once at creation time.

share|improve this answer
I did read something like that and devised a naive experiment: I PREPARE:d the raw query, and then EXECUTE:d it. I noticed no difference and disregarded it as not working. Could it have? – Odalrick Jan 30 '14 at 18:01

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