michaelshavel
asked on
Single quotes in a query
Hi,
I have a table full of phone numbers in the format 1112223333. The table has about 4 million records in it and I update the numbers in it once a month. (In this case, updating means deleting all records and inserting new records from a file via LOAD DATA INFILE)
I did a test query using the command line mysql utility, before I dumped the records with a record I knew was in there. The query was:
mysql>SELECT phone from phonelist WHERE phone='2124451234';
This query brought back 2124451234
Then I dumped my records and loaded my updated set.
I did the same query above (I know that the number 2124451234 is in the table.
This time though the query brought back nothing. I triple checked to make sure that number was actually in the table, and it was.
So, I tried doing the query without the single quotes:
mysql>SELECT phone from phonelist WHERE phone='2124451234';
This query brought back 2124451234 however it took much longer to bring it back.
A few other things of note, I have an normal index on the phone field.
The phone field is a char field.
I'm scratching my head here wondering why I can't get a result when using the single quotes. I have always been able to before.
So, I guess my first question is what is the difference between running the query with the quotes and without them?
The only thing I can think that is causing this problem is the data file because it's the only thing that has changed. The data file I'm loading has one phone record per row. If I open the datafile in a Hex editor, each row is terminated by an 0A character. I don't have an old datafile to compare this with so I'm not sure if all my datafiles in the past have had this character at the end of each row. My LOAD DATA INFILE command using the default record separator (\n). Viewing the contents of my table after loading this data file in, shows one record per row. I'm wondering though if this 0A character could be causing this problem with my single quoted query not returning correctly.
Any suggestions are welcome.
Thanks,
mike
I have a table full of phone numbers in the format 1112223333. The table has about 4 million records in it and I update the numbers in it once a month. (In this case, updating means deleting all records and inserting new records from a file via LOAD DATA INFILE)
I did a test query using the command line mysql utility, before I dumped the records with a record I knew was in there. The query was:
mysql>SELECT phone from phonelist WHERE phone='2124451234';
This query brought back 2124451234
Then I dumped my records and loaded my updated set.
I did the same query above (I know that the number 2124451234 is in the table.
This time though the query brought back nothing. I triple checked to make sure that number was actually in the table, and it was.
So, I tried doing the query without the single quotes:
mysql>SELECT phone from phonelist WHERE phone='2124451234';
This query brought back 2124451234 however it took much longer to bring it back.
A few other things of note, I have an normal index on the phone field.
The phone field is a char field.
I'm scratching my head here wondering why I can't get a result when using the single quotes. I have always been able to before.
So, I guess my first question is what is the difference between running the query with the quotes and without them?
The only thing I can think that is causing this problem is the data file because it's the only thing that has changed. The data file I'm loading has one phone record per row. If I open the datafile in a Hex editor, each row is terminated by an 0A character. I don't have an old datafile to compare this with so I'm not sure if all my datafiles in the past have had this character at the end of each row. My LOAD DATA INFILE command using the default record separator (\n). Viewing the contents of my table after loading this data file in, shows one record per row. I'm wondering though if this 0A character could be causing this problem with my single quoted query not returning correctly.
Any suggestions are welcome.
Thanks,
mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Routinet.
Your right the two queries are identical. It was a typo in this posting.
The second should read:
mysql>SELECT phone from phonelist WHERE phone=2124451234;
This query brought back 2124451234 but it took a few seconds for mysql to bring back a result.
Thanks,
mike
Your right the two queries are identical. It was a typo in this posting.
The second should read:
mysql>SELECT phone from phonelist WHERE phone=2124451234;
This query brought back 2124451234 but it took a few seconds for mysql to bring back a result.
Thanks,
mike
ASKER
FYI -- this is my index. Anything look out of the ordinary here?
mysql> show index from phonelist;
+-----------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+
| phonelist | 1 | phonenumbers | 1 | phone | A | 4167773 | NULL | NULL | YES | BTREE | |
+-----------+------------+
mysql> show index from phonelist;
+-----------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+
| phonelist | 1 | phonenumbers | 1 | phone | A | 4167773 | NULL | NULL | YES | BTREE | |
+-----------+------------+
ASKER
Thanks. I found the problem.
My Input file was modified by the sender. Each record had 0D0A as a record separator. The first file I was using just had 0A as the record separator.
My Input file was modified by the sender. Each record had 0D0A as a record separator. The first file I was using just had 0A as the record separator.
please cross check what differences you have on the table specs an indexes...