homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

Complex queries
for the linear mind...

 2:53 am on Sep 10, 2005 (gmt 0)

Writing complex queries has never been my forte. I'm passable with simpler queries. Now I have some data that will require either a complex query, and/or multiple tables. Please let me explain.

I capture (non-identifiable) visitor data, including referral strings. The referal gives me search terms, SE's, and advertising codes. My desire is extract specific data based on all three of those criteria. I'm sure that it's possible, but is it more efficient to form a single query on the same field using multiple criteria? Right now I've created multiple tables, populating them with subsets of the original table, and I run my simpler queries over those tables. But.. I know that I can get better results than what I have now. The idea of creating another batch of tables doesn't appeal to me. If I can get what I want from one table I'd be happy.

(There are about 400,000 rows in this table.)



 9:36 am on Sep 10, 2005 (gmt 0)

One idea (depends on the database you are using): think about creating views rather than separate tables.

That way, your mental model remains the same, and the query optimizer has a chance to improve yhe way it tackles the queries.


 12:37 pm on Sep 10, 2005 (gmt 0)

mental model remains the same
there's a scary thought..

I've spent the night working cleaning up the data. Cleaned data is going into separate tables for search terms, SE's, and codes. The record id field is common for each table. I can't think of any better way to do it.

I was ahead of myself trying to write any kind of query until I can work with clean records. ..and I really wanted to be out of town this weekend :(

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
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