Solved

SQL optimization

Posted on 2013-01-14
2
183 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
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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Instering to MySQL table 5 48
Parts and Products table schema in mysql 6 52
Create a Select Query and Populate a Table 3 48
Mysql sync between 3-4 mysql db 4 33
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

773 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