Solved

SQL optimization

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql ide 10 55
mysql db 3 96
converting integer data type to time data type in sql 4 60
mysql workbecn having problems to export tables to cvs 4 28
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Creating and Managing Databases with phpMyAdmin in cPanel.

739 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