Re: How to speed up querries in a humangous db?

Hello, Andy wrote: > > this is my sql statement: > > $stmt= " > SELECT * > from $geo_T3 > WHERE country_code = '$country' AND province = '$province' > order by city ASC > "; You can do two things to speed up the query: - Never use * for selecting columns in production code. You may end up selecting more information than you want and columns may not return in the order that you expect. In your case, country_code and province do not need to be selected because they are constant for all selected rows to satisfy the search condition. - Avoid using ORDER BY unless you absolutely need it. Using ORDER BY requires the database server to buffer all selected rows in its memory space to have them sorted after the table scan ends. You did not mention if you used indexes in that table. Depending if and how your table indexes are defined the query may perform a table full scan, which is a very inefficient thing. Just tell the exact definition of your indexes so we can tell you if the query will execute a full scan or not. Alternatively, if you are using MySQL, you may use the EXPLAIN SQL command to tell if the query will use any indexes or not. Regards, Manuel Lemos > cheers Andy > > "Miles Thompson" <milesthompson@ns.sympatico.ca> schrieb im Newsbeitrag > news:5.0.2.1.0.20011225222605.01cc2870@pop1.ns.sympatico.ca... > > > > 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: php-db-unsubscribe@lists.php.net > > >For additional commands, e-mail: php-db-help@lists.php.net > > >To contact the list administrators, e-mail: php-list-admin@lists.php.net > >