database question..

[Grooverider]

Damn, I already redesigned the whole database and backend software. It's running a test right now.

3 Tables; KW ID, Company ID, A Data. So when I search for kw list, it's a bunch of integers rather than strings..

Waiting for the database to get bigger so I can test speed..

[Grooverider]

Right - I need help constructing the query

Thanks for all the advice so far, the database is constructed almost exactly how you guys have told me to make it.

Right here it is:

Table 1: a-data
Fields: [a-ID] - [CompID] - [KeywordID] - [Pos] - [Log1] - [Log2] - [TF] - [url] - [date]

Table 2: company
Fields: [companyID] - [CompanyName]

Table 3: keyword
Fields: [keywordID] - [keyword]

(keywordID in Table 3 = keywordID in table 1)
(companyID in table 2 = compID in table 1)

So i'm doing the following query


SELECT distinct keywordID from a-data where CompID like "%10%"

So this replies the keyword ID's that CompID containing 10 is related to.

Now, to take it to the next step:

I want to search for MBNA (for example), and 'join' the CompID to the CompanyID table. I then want to join the keywordID's to their text equivilent in the Keyword table..

Can anyone help me with this query.. I'm googling but no joy.

[Tenks]

Are you seriously slamming an inline SELECT statement against a 1.8million row data table?


Stored procedures my man....

And to increase simplicity just make views that have all your JOIN statements written already and just query (WITH A STORED PROCEDURE) the view.

-edit-

Theres no magic one or two paragraph reply that could fix all the problems I see just browsing this thread. There is a good reason DBA's get paid a shitload. Since the database was built with obvious lack of knowledge of PK/FK relationships, stored procedures, indexing, and domain tables I'd suggest a complete teardown and reconstruct under proper database normalization theories.

[Grooverider]

I'm guessing you haven't read the thread, as it has been completely reconstructed, i.e. my previous post.

[gremlinz273]

Why are you using LIKE on a numeric key?

[Grooverider]

Because it was an example.

For those that care, the correct answer was:

select distinct a.keywordid, k.keyword from adata a join keyword k using (keywordid) join company c using(companyid) where c.companyurl like '%variable%'

[lendarios]

If you keep using the DISTINCT keyword you are goning to kill your Performance.
When you use DISTINTC, you are forcing SQL to arrange all the data and then to selecT the ones that are difernet. Its a huge overhead and the performance will suffer a lot. Do you have duplicated data than forces you to use Distinct? And if you do have duplicated data then since its duplicated you dont need to store it.
Use indexes and keys to make sure you are not storing duplicated information.
Avoid using DISTINCT as much as possible.

[Tripamang]

DISTINCT shouldn't be used for common queries as the previous poster mentioned.

I use it mostly for creating temporary tables to perform large operations on where I don't need repeating record information.

Too bad you're not using oracle.. PL/SQL developer is a great tool for gauging how efficient your queries are as well as learning to build better ones. (<3 cost analyzer).

[Grooverider]

I don't see a way to get around the use of distinct, as the keyword ID is erpeating. Incidentally, the execution time of the above query is 0.01s on a 1m row database, so the performance is acceptable.

[lendarios]

Originally Posted by Grooverider

as the keyword ID is repeating.

Then you have not choosen a good KeywordID.

Going by the first post in your page The relationship in your table 1 to table 3 its not a one to many. But a many to Many relationship

modify Table 3.
make it

with the columns
CompanyID
KeywordID
Keyword

Modify table 1 and remove KeywordID

Basically make table two your center table.

So table 2
Fields: [companyID] - [CompanyName]

Table one
ields: [a-ID] - [CompanyID] - [Pos] - [Log1] - [Log2] - [TF] - [url] - [date]

Table 3
[keywordID] - [keyword] [Company ID]
Place an index on [Company ID] with include columns keyword and keywordID

SELECT keywordID from [table-3] where [Company ID] like "%10%"

A much cleaner design. Place foreign keys on the table 1 and 3 referring to table 2

[Grooverider]

That doesn't work.

How do you determined which ad displayed for which keyword? With your design, you could not determine the keyword id related to each log entry. The only logical relationship is log1 -> companyID, which is useless. You need to reference Log 1 to Company ID and Keyword ID. In this instance, keyword ID could be any of over 1,000,000 entries.

Yes it allows you to reference keywordID faster, but it completely limQits the other uses of the database.

[Kargon]

You would query for the keyword used for the particular company, and then use that to query for the log entry...

SELECT log1 FROM table_3 NATURAL JOIN table_1 WHERE keywordID = $key; ($key is what you just selected)

or something along those lines. It could be a left or right join, natural join is just the easy opt out as I just woke up and can't think =/ It joins the two tables where
the CompanyIDs match, so you have a list of different keyword IDs for the same companyIDs and can just pick off the data from there. Additionally, since the SELECT statement
used to find the keywordID will probably return multiple entries, this query should be executed in a while loop. (for each keywordID). Hopefully this wasn't too retarded.


p.s. the definition of a primary key is that it is unique (i.e. it does not repeat)

[Grooverider]

Company's can use more than one keyword. Therefore you logic fails.

Additionally, the non-repeating primary key is the Ad ID.

[lendarios]

Sorry groover. Im having problems understanding the relationships.

An ad object has the following elements, a sinlge company attached to it, multiple keyword and other miscelanous properties.
Im assuming one add only applies to one company, one company can have multiple keywords and a keyword can repeat among several companies. Multiple keywords can be attached to a single ad..

Separate the entities

Table 1
CompanyID
CompanyName

Table2
Ad-Id
Add unique information, No company, no Keyword data on this table)

table 3
Keyword ID
keyword text

TAble 4 ( Mapping Keywords to companys
keywordId
companyID

TAble5 ( Mapping Adds to companies)
company-id
Ad-id

table6 (mapping keywords to adds)
ad-id
keywordid

Tables 4 5 6 are integer tables and place indexes on them.
This way your data is normalized. It does require a bit more of upkeep in the application level, but the end result is your data is nicely structure and any query will be served fast. This way you can query how many adds per company, how may adds per keyword. Basically any combination of add/company/keyword you wish.

[Grooverider]

Yes that is actually superior to my current set up. The queries will be extremely complicated though.. ugh. :

I may adopt this set up actually, to make the queries rapid when the database becomes really huge.

I have a separate, smaller but similar project to trial first as a proof of concept

Printing your design out now..