[Eclug] OT: SQL question

At 04:07 PM 10/8/2006, you wrote:
>Except it's more work for the database to consider all those rows that you 
>are going to throw out anyway! Suppose you have 50 rows in the category 
>table, that's 50 _times_ the number of rows the query has to consider.
>
>Maybe I should have gotten to the point and mentioned that avoiding that 
>is as simple are leaving out the unnecessary join:
>
>SELECT DISTINCT pivientities.Entityid FROM pivientities
>INNER JOIN pivicategories ON
>pivientities.Entityid=pivicategories.Entityid ...
>
>Also consider that INNER JOIN and "," are the same thing excepting that 
>INNER JOIN can take on "ON" clause (think of "," as shorthand notation for 
>INNER JOIN without ON). If you think of is this way, then what you wrote 
>is like writing either one of these:


I didn't know that. I always thought that SELECT table1, table2 .... simply 
indicates that I want to access fields within table1 and table2, but 
doesn't relate the tables in any way. Maybe I misunderstood you, but how is 
the db engine supposed to know on what to join when I don't specify a field?


>SELECT DISTINCT pivientities.Entityid
>FROM pivientities, pivicategories, pivicategories
>WHERE ...
>
>Or
>
>SELECT DISTINCT pivientities.Entityid FROM pivientities
>INNER JOIN pivicategories INNER JOIN pivicategories ON
>pivientities.Entityid=pivicategories.Entityid ...


As it seems, joining the tables with 
pivientities.Entityid=pivicategories.Entityid is sufficient.


> > I looked up the operator precedence and found a table that
> > lists levels from 1 through 9 and the corresponding
> > operators. OR was level 9 and AND was level 8. The
> > description stated that the "higher level operators have
> > precedence over lower level ones". It did not explain if
> > level 1 or level 9 is the highest level. So, based on what
> > you write AND is preferred over OR.
>
>Very helpful documentation, eh? In this case, level 1 is the highest 
>level, that is, without any parenthesis or other forced grouping, all the 
>ANDs will be evaluated before all the ORs.

Oh yes, it was from Microsoft. I should have known better. And even if, it 
wouldn't have helped.



> > I'm not too concerned about performance of the query, as I
> > am sure that the execution time and transfer of the page
> > data drastically exceeds the query run time. So if I am
> > looking at optimizing the system I'd take a closer look at
> > my PHP code than the SQL queries.
>
>You might reconsider that. I'm making a lot of assumptions about your 
>application here, but suppose your table grows to have thousands of rows 
>in it. Probably you are still only going to display the results 20 or 50 
>rows at a time, so once your database gets to the size that you tend to 
>produce at least that many rows per query, your PHP and transfer time will 
>not increase any as the database gets bigger, since they are still only 
>handling 20 or 50 at a time.
>
>Your SQL time will continue to go up though, because the query needs to 
>sift through all the rows every time you display 20 or 50 rows. Once your 
>indexes get bigger than will stay in memory, your SQL time will start to 
>go way up.
>
>Also, PHP time will tend to scale with CPU speed, where SQL time will tend 
>to scale with disk I/O and memory, which are generally more costly to 
>scale up enough to make a difference than CPU is.

Hmmm, this baby will run on a dual P2-333 with a 10k SCSI disk. But I 
didn't know that CPU power isn't the main factor for the db....should have 
figured that one since MySQL most likely doesn't hold all tables in memory.

So, with using IN and the necessary modifications, I get this and it 
appears to do what I expect. I also added a test recorsd for type 'vid' to 
my table that has a category identical to one of the pic records.
SELECT DISTINCT pivientities.Entityid FROM pivientities, pivicategories
         WHERE pivientities.Entityid=pivicategories.Entityid
         AND pivientities.Entitytype = 'pic'
         AND pivicategories.Category_name IN ('Alton', 'Bristol', 'Soft')
ORDER BY pivientities.Creation_date ASC

That gets me the two IDs for the records of the types pic and leaves the 
vid typed record out. I tested some more combinations with the categories 
and each time I get the expected result.


David, David, James...thank you very much for helping me out. Today is a 
good day, I learnt a lot thanks to you. :)

                 The other David