CODEWITHSUNDEEP
mysql
user614784
user614784

Reputation: 3

MySql Query, Database design,query efficiency

I have been working on php and mysql for 1 year. I have come across some code in php, where the programmers writes a query which he encloses it in for loop or foreach loop, and the query gets generated as like this (takes 65.134 seconds):

SELECT * from tbl_name where PNumber IN('p1','p2'.......,'p998','p999','p1000')

In my mind I believe that query is horrible, but people who are working before me, they say there is no other way round and we have to deal with it. Then I thought it should be wrong database design. But cannot come up with the solution. So any suggestions or opinions are welcome.

Couple of things to add. Column is indexed and table has almost 3 million records. I have given you example as p1,p2 etc..But those are originally phone numbers like 3371234567,5028129456 etc. The worst part, that I feel is, this column is varchar instead of int or big int which makes the comparison even worse.My question is, can we call this a good query, or is it wrong to generalize and it depends on the requirement?

Upvotes: 0

Views: 95

Answers (2)

Eljakim
Eljakim

Reputation: 6937

What can help us help you is if you could tell us the result of explain on your query.

ie:

explain SELECT * from tbl_name where PNumber IN('p1','p2'.......,'p998','p999','p1000');

This will give us some information on what the database is trying to do.

Upvotes: 0

Nonym
Nonym

Reputation: 6299

I'm posting this with hopes that we can decrease the turnaround time by providing an example.

Check with your developers and see how they are producing the SQL command.

All those numbers must be coming from somewhere. Ask them where.

If the numbers are coming from a table, then we should simply JOIN those two tables.

EXAMPLE:

Granting that the phone numbers are stored in a table named PHONE_NUMBERS under a column named Phone -- and using your example tbl_name to which we match the column PNumber 

SELECT t1.*
FROM tbl_name AS t1
    INNER JOIN PHONE_NUMBERS AS t2
    ON t1.PNumber = t2.Phone

However, even as an example, this is not enough. Like @George said, you'll have to give us more information about the data structure and the data source of the phone numbers. In fact, depending on the kind of data you show us and the results you need, your SQL query might need to remain using an IN statement instead of an INNER JOIN statement..

Please give us more information...

Upvotes: 1

Related Questions