Mysql query against 800k records.. too slow

[vexusdev]

Hey guys.. I am querying individual phone numbers against a 800k mysql database.

 

Heres how my script works:

 

1. Upload text file full of numbers

2. grabs 20 phone numbers at a time and put the 20 numbers into an array

3. For loop processes the array in the WHILE loop of the file

4. Query in the process loop:

-> Query AreaCode, Exchange(3 next numbers), and first digit of the last 4 numbers

-> if no result query only AreaCode and Exchange

 

For each number it literally takes 1 second.. so as you could imagine this is way too slow!

 

Heres my code:

$process = 20;
$fh = fopen($file,'rb');
$dat = array();
while(!feof($fh)) {

//READ INTO ARRAY
for ($i = 1; $i <= $process; $i++) {
$dat[$i] = fgets($fh);
}//$i

//PROCESS ARRAY
for ($x = 1; $x <= $process; $x++) {

$phone = $dat[$x];
if($phone == ''){continue;}

$phone = str_replace("(", "", $phone);
$phone = str_replace(")", "", $phone);
$phone = str_replace("-", "", $phone);
$phone = str_replace("-", "", $phone);

if(strlen($phone) >= 10) {

$phone = substr($phone,0,10);

  $area = substr($phone,0,3);
  $exchange = substr($phone,3,3);
  $firstdigit = substr($phone,6,1);

  //First Query db  Search Area code and prefix and first digit
$result = mysql_query("SELECT * FROM provider WHERE areacode='".$area."' AND exchange='".$exchange."' AND firstdigit='".$firstdigit."' 

AND type='WIRELESS'");

//echo "SELECT * FROM provider WHERE areacode='".$area."' AND exchange='".$exchange."' AND firstdigit='".$firstdigit."' AND 

(type='WIRELESS' OR type='PCS') LIMIT 1";

if(mysql_num_rows($result)==0) {//no firstdigit so lets try just first 2
$result = mysql_query("SELECT * FROM provider WHERE areacode='".$area."' AND exchange='".$exchange."' AND (type='WIRELESS' OR 

type='PCS') LIMIT 1");

if(mysql_num_rows($result)==0){
$noncell++;
} else {
while($row = mysql_fetch_array($result))
  {
  if(strlen($row['email']) >= 3) { $cell++; 
  fwrite($txt, $phone.'@'.$row['email']."\n");
  }
  }//WHILE
}//if(mysql)
} else {//if result <> 0
while($row = mysql_fetch_array($result))
  {
  if(strlen($row['email']) >= 3) { $cell++;
  fwrite($txt, $phone.'@'.$row['email']."\n");
  }
  }
}//else

} else { //Length not 10
$noncell++;

}

}//$x PROCESS LOOP

}//WHILE

[Mchl]

What indexes you have defined on this table?

[sader]

And add LIMIT 1 in this query if this is possible

 

$result = mysql_query("SELECT * FROM provider WHERE areacode='".$area."' AND exchange='".$exchange."' AND firstdigit='".$firstdigit."' AND type='WIRELESS'");

[vexusdev]

What indexes you have defined on this table?

 

I don't... this database was hell getting it imported from a CSV file..

 

What do you suggest I do?

 

@sader

 

yeh Ill add that! thanks

[Mchl]

ALTER TABLE provider 
ADD INDEX areacode_exchange_firstdigit_type (areacode,exchange,firstdigit,type)

[vexusdev]

ALTER TABLE provider 
ADD INDEX areacode_exchange_firstdigit_type (areacode,exchange,firstdigit,type)

 

After I run this in phpmyadmin do I have to alter my php code in anyway?

 

 

Whats this mean?

#1170 - BLOB/TEXT column 'areacode' used in key specification without a key length

[Mchl]

Nope. This will add an index that can possibly speed up your searches.

[Mchl]

Whats this mean?

#1170 - BLOB/TEXT column 'areacode' used in key specification without a key length

 

Oh boy... show me your table structure first...

[vexusdev]

Whats this mean?

#1170 - BLOB/TEXT column 'areacode' used in key specification without a key length

 

Oh boy... show me your table structure first...

 

ALTER TABLE provider

ADD INDEX areacode_exchange_firstdigit_type (areacode,exchange,firstdigit,type)

 

I changed to (this worked but missing type)

ALTER TABLE provider

ADD INDEX areacode_exchange_firstdigit (areacode,exchange,firstdigit)

 

Because I made the 3 first tiny ints with correct sizes.. but the type still says the key length problem because its a text field heres my structure:

 

areacode tinyint(3)
exchange tinyint(3)
firstdigit tinyint(4)
type TEXT

 

EDIT: fixed type problem by making it VARCHAR

[Mchl]

Be aware that tinyint will hold numbers from 0 to 255 only.

 

Instead of TEXT for 'type'  you should use either VARCHAR(x) where x is the length of longest value in this column, or ENUM('WIRELESS','PCS','anyOtherTypes')

[vexusdev]

Be aware that tinyint will hold numbers from 0 to 255 only.

 

Instead of TEXT for 'type'  you should use either VARCHAR(x) where x is the length of longest value in this column, or ENUM('WIRELESS','PCS','anyOtherTypes')

 

Thanks I did do that, weird thing is when I use your code.. it actually makes the queries slower?

 

Do I have any other solutions maybe code wise? I'm really stumped on how I can speed this up its really way too slow for a practical solution.

[Mchl]

Could you run something like this and post results

 

EXPLAIN SELECT * FROM provider WHERE areacode = putSomethingHere AND exchange= putSomethingHere AND firstdigit= putSomethingHere AND type='WIRELESS'

 

[vexusdev]

Could you run something like this and post results

 

EXPLAIN SELECT * FROM provider WHERE areacode = putSomethingHere AND exchange= putSomethingHere AND firstdigit= putSomethingHere AND type='WIRELESS'

 

 

1 SIMPLE provider ref areacode_exchange_firstdigit_type areacode_exchange_firstdigit_type 32 const,const,const,const 10 Using where

 

 

I Noticed if i execute the code in phpmyadmin with your code set it is .1 faster without key its .2  so is there a way to maybe have both of my sql queries combined forexample:

 

where areacode=631 AND exchange=374 AND (firstdigit='5' OR firstdigit='') AND (type='WIRELESS' OR type='PCS')

[vexusdev]

Fixed it.. its fast as hell now! 2k processed in 1 second.

 

 

THANKS SO MUCH GUYS!

 

 

SELECT * FROM provider WHERE areacode='".$area."' AND exchange='".$exchange."' AND (firstdigit='".$firstdigit."'

 

OR firstdigit=' ') AND (type='WIRELESS' OR type='PCS') LIMIT 1

 

thats the new query.. also i used your key alter