Solved

SQL optimization

Posted on 2013-01-14
2
185 Views
Last Modified: 2013-01-18
Hello,
  I have a table that contain phone numbers. This table is very large and has an index on ph#.

I have an application that users can query for data using the ph#. They should be able to enter partial phone  #

my application has a  text box for users to enter the ph# and a combox with the following [equal ,.startwith, endWith]. The phone number is not in a standard format

here an example of the table
IDx PH
1     9243333
2     53333
3     3333378
4     923-1334



Suppose a user is looking for all information where the  phone#  endwith 3333 . How would I write this query ?. The result should be index = 1 ,2]

I am using SQLITE DB
0
Comment
Question by:SiemensSEN
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 8

Accepted Solution

by:
virtuadept earned 150 total points
ID: 38778450
The "equal" query will be very fast if there is an index on the phone number. So I would try that first and if there is a hit, return those records. If you must ALWAYS do a "end with" then you're going to be very slow no matter what. I would suggest that you not make that an always function, maybe allow the user to have an "exact match" radio button and a "contains" button. And you have to use WHERE PH LIKE '%'+@whatuserinput+'%' to do partial phone number searches.

EDIT: one more tip, if you can make it where they have to select radio button:

() equals () starts with () ends with

Then you can have 3 queries, the top being fastest:

WHERE PH = @userinput

WHERE PH LIKE @userinput+'%'

WHERE PH LIKE '%'+@userinput

And if the data is clustered index on PH then the 2nd WHERE will be pretty fast also.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 38782414
> If you must ALWAYS do a "end with"

you could then store, besides the actual phone number, the "reversed phone number", index that, and search by the reserves string also.
 
Dx PH     PHr
1     9243333   3333429
2     53333      33335
3     3333378      8733333
4     923-1334    4331329

Open in new window

with index on PHr, this query will be quite fast:
SELECT * from yourtable WHERE PHr LIKE '3333%' 

Open in new window

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to loop bootstrap columns which contain database records 9 68
MySQL Memory Keeps Increasing 4 62
mysql db 3 81
Unwanted output from my query 5 40
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question