homepage Welcome to WebmasterWorld Guest from 54.145.183.190
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

This 62 message thread spans 3 pages: < < 62 ( 1 2 [3]     
Writing an Efficient Query
Help with an issue with SQL query
Nick_W

WebmasterWorld Senior Member nick_w us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 2910 posted 10:56 am on May 29, 2003 (gmt 0)

Hi all,

I have to select rows from a table where one of the fields is an integer. I only want records that have '1,3,4,6 and 17' as this number. Not the others.

What is the best way to write the query? Here are a couple of ways I've thought of:

SELECT * FROM tbl WHERE colum IN $array_of_acceptable_integers;

SELECT * FROM tbl WHERE column = $val1 OR $val2 etc....

As you can see, once we get past the very basics I'm none to bright so anyone has any 'authorative tutorials' on writhing these kind of things would also be welcome.

Thanks!

Nick

 

killroy

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 2910 posted 11:02 pm on Jun 13, 2003 (gmt 0)

remember that indexes are NOT used for the select part
only for the where and group by and order by parts. So in this case as there are no further wheres or joins (shown) it would be futile to index by product name

An index by productid seems to be most appropriate. If you can do a unique index, even better for the select distinct performance (but to be honest I don't believe distinct is optimized using indices, not that it would maek a big difference in your case)

But of course if you have joins and where clauses it all changes.

SN

Clark

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 2910 posted 11:58 pm on Jun 13, 2003 (gmt 0)

Thank you again killroy.

remember that indexes are NOT used for the select part only for the where and group by and order by parts.

I keep confusing that. I thought because of DISTINCT that an index would help.

So in this case as there are no further wheres or joins (shown) it would be futile to index by product name

No joins. But within my app there are other Wheres. They are unfortunately conditional.

There is an advanced search and if they put something in the field, it is searched. If it is blank then I substitute "1=1". So I can't consistently do an index on the 7 fields that can potentially be in the WHERE (although, like I said, that's for the advanced search, not on the queries I just asked about).

If you can do a unique index, even better for the select distinct performance

It's funny so many things to consider. I'm also using REPLACE INTO elsewhere, so I put in a unique index "innocently" somewhere and boom all of a sudden no records came in.


But of course if you have joins and where clauses it all changes.

Am I correct that JOINS cannot use indexes at all?

This 62 message thread spans 3 pages: < < 62 ( 1 2 [3]
Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved