Is this the same question posted about 10 days ago regarding countries and
provinces?
Is the query any faster if you run it at the MySQL console?
Does the query work if you remove the single quotes surrounding $province,
assuming the province filed corresponds to "field3" in your original message?
Check the MySQL docs for performance issues surrounding the use of the
varchar type. You may be saving on storage space, which is relatively
cheap, but paying a price on extraction time, performance, which is dear.
Finally, even though you are using numbers to identify provinces, that
doesn't mean they should be integers. You have mixed types in your
condition, and some databases don't like that. MySQL may accept it, but do
the type conversion internally, but on the left hand side of the equal
sign, convertings the internal tinyints to char, then comparing.
You could also try this
$stmt= "
SELECT *
from $geo_T3
WHERE country_code = '$country' AND province = intval($province)
order by city ASC
";
This is a simple query. If you can get console access to ./mysql, I'd
really like to know if there is a striking difference in performance
between that and the PHP script.
Regards and Merry Christmas - Miles Thompson
http://www.cqagroup.ca
At 09:41 AM 12/26/2001 +0100, Andy wrote:
>this is my sql statement:
>
> $stmt= "
> SELECT *
> from $geo_T3
> WHERE country_code = '$country' AND province = '$province'
> order by city ASC
> ";
>
>
>cheers Andy
>
>
>"Miles Thompson" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
>[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> >
> > To help with that we'd have to know how your queries are phrased. Please
> > show us.
> >
> > Are you having to do a type conversion on the tinyint field?
> >
> > Miles Thompson
> >
> > At 02:07 AM 12/26/2001 +0100, Andy wrote:
> > >Hi there,
> > >
> > >I have a huge table with over 2 million entries. It takes to long to make
>a
> > >querry. No I am thinking about the datatypes. Maybe there is a better
>way. I
> > >have already indexed the search fields ( which made the table 75 MB
>big!!!)
> > >
> > >My table looks like that:
> > >
> > >field1 : varchar100
> > >field2 : char2
> > >field3 : tinyint2
> > >
> > >I have an index on field 2 and 3
> > >
> > >The SQL Querry is searching for: field 2 AND field 3
> > >
> > >Does anybody have an Idea how to spead this up?
> > >
> > >Thanx in advance,
> > >
> > >Andy
> > >
> > >
> > >
> > >--
> > >PHP Database Mailing List (http://www.php.net/)
> > >To unsubscribe, e-mail: [EMAIL PROTECTED]
> > >For additional commands, e-mail: [EMAIL PROTECTED]
> > >To contact the list administrators, e-mail: [EMAIL PROTECTED]
> >
>
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
