Query optimization using mysql and table structure
843859Aug 20 2007 — edited Aug 21 2007Ok currently we have an application that queries one table witth over 300K rows looking up a telephone number displayed as a long. The application that one of the developers developed, before I got here does a round robin approach to querying this data.
I think my scenerio may be better, but I want to make sure I can do it, and ask a little help in doing so. So let me point out what is going on and how I think it could be better.
As, I said we are doing a search on a telephone number (long) in a table. Because we service many different states, and areas, I have determined that instead of having one table, I am going to have multiple tables and the numbers associated with that table will depend on the area that your in. Basically regional.
Another thing I think would be good is to put these numbers in order in the table, because if the number is not there then we have to create that number as an account in the system. I also think it would be easier (faster) to query in the smaller range instead of the whole table, if that make sense? One problem that I do have is that I am not a DBA. I think I will need to index that data, but I am not sure about how to go about this, and how to sort the data each time I get a new number or when the data is updated.
What I need is figuring out how to setup the table structures, how to insert the data (if needed) depending on the area you are in. and how to query the info for fast retrieval of the system.
So this is how it plays out.
Look up customer by telephone number?
if the customer information is in the local database cache - get the data associated with the customer and display the info.
If the customer info is not in the local db, then look at the main db table, get the data associated with the customer and display the info.
If the customer is not in either - create the customer information there on the spot.
Any help would be appreciated.
Things that I know I need in the table:
telnoId
telNumber
telName
telType
createDate
updatedDate
status
maybe:
index?
deleted - even if I delete the number, I still want to keep track of it for future reference.
I am running MySQL, Tomcat 5.5, and java 1.4.2
orozcom