tags:

views:

62

answers:

1
Q: 

Write an efficient query

Working on an in-house ticketing system in php/mysql. This short term ticketing system has been in place for a year or so now.

We are reaching 10,000 tickets and I would like to make the queries more efficient, since 9,500 or so of the tickets do not need to be looked at each day.

Right now the queries are getting rows by tech id, then checking to see if it's an open ticket.

I am guessing that if I make it look for open tickets then tech id the query will be more efficient. Is this true (rows with less options to rows with more options as the query builds?)

+3  A: 

Don't guess, measure!

The most important part is that you have appropriate indexes for your query. And 10000 is not a large number of rows, if this is noticably slow your query probably isn't using an index.

SO question: What resources exist for Database performance-tuning?

Fabian  2010-09-13 13:24:10
+1 for indexes. And instead of *measuring*, you could try to do the math as well, if you studied some database design. Which is faster can probably be debated though. :-)
Patrick  2010-09-13 13:35:45
Math is not my strong point. Thanks for the link. I ran the two queries and see the open tickets then tech id is faster. I am looking for efficiency or resource usage over speed for the long term and will use the links to find out how to measure that.
eric.s  2010-09-13 13:40:35

related questions

Remove Quotes and Commas from a String in MySQL
What's the best way of converting a mysql database to a sqlite one?
How do you manage databases in development, test, and production?
Multiple foreign keys?
Add 1 to a field (MySQL)
Issues using MS Access as a front-end to a MySQL database back-end?
Bigger than a char but smaller than a blob
How do I retrieve my MySQL username and password?
Long-time LAMP Developer moving to VB.NET
How do I Concatenate entire result sets in MySQL?
Full complete MySQL database replication? Ideas? What do people do?
SQL: Distribution of table in time
SQLite vs MySQL
How do I create a new Ruby on Rails application using MySQL instead of SQLite?
Multiple Updates in MySQL
MySQL/Apache Error in PHP MySQL query
What all do I need to escape when sending a (My)SQL query?
Auto Generate Database Diagram MySQL
Mechanisms for tracking DB schema changes
How big can a MySQL database get before performance starts to degrade.
Python and MySQL
SQL Server 2005 implementation of MySQL REPLACE INTO?
How to export data from SQL Server 2005 to MySQL
Throw Error In MySQL Trigger
Binary Data in MySQL