Jump to content


Photo

optimizing a query


  • Please log in to reply
34 replies to this topic

#21 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 23 February 2006 - 09:49 PM

I made that update and it is still checking all the rows in the table...

#22 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 23 February 2006 - 10:02 PM

MYSQL may have decided that it's faster not to use the index in this case
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html[/a]

That could change once more rows are in the table. If you try a different date range you also may see a change in whether or not MYSQL uses the index.

Someone more knowledgeable may have more insight.

#23 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 23 February 2006 - 10:16 PM

I did try another range and it seemed to stay pretty much the same... If this becomes an issue I will most likely just prune the table since we don't really need to access the information after the order has been shipped.


#24 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 23 February 2006 - 11:22 PM

Have to tried running ANALZYE TABLE on the table in question? MySQL assumes an even key distribution for indexing, which is rarely the case.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#25 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 27 February 2006 - 10:03 PM

Well I had to change a few things up, but I did get it working.

It did bring up another question (since Im looking at optimizing reports that used to use multiple queries), when I want to apply billing information for line item, there are 5 different structures of billing set up in an order of precedence (ex standard billing is the lowest and specific order billing is the highest).

Can I use a query like below to pull out the highest precedent price for billing?


$sql="SELECT price FROM bill_type BT LEFT JOIN billing_low BL ON (BL.bill_type_id=BT.bill_type_id) LEFT JOIN bill_medium BM ON (BM.bill_type_id=BT.bill_type_id) LEFT JOIN bill_high BH ON (BH.bill_type_id=BT.bill_type_id) where ..... "




#26 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 February 2006 - 11:24 PM

I don't understand what you mean by precedence. The JOINs will pull them out, sure, but you won't be able to order them that way.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#27 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 27 February 2006 - 11:28 PM

by precedence I simply mean that it should check bill_low then bill_medium, then bill_high. If there are any rows in bill_high, it should be taken over the other two, then bill_medium, etc.

Each of the tables has a price column so what I wrote works fine if there is a value in the bill_high, but if there is no record in bill_high it returns one row but no price.

Is there a btter way to word a query like that to obtain the results or should I just stick with 3 seperate queries and comparing them afterword?

#28 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 February 2006 - 03:04 AM

That might just work -- though I've never tried it. Usually, I would UNION all the bill_ tables together, add a field to mark which the source table, group by bill, and order by precedence. I'm sure someone else has another (probably better) way to do it.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#29 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 February 2006 - 06:37 AM

I don't think I've interpreted what you're asking accurately. Perhaps you could post the current queries and any programming logic you're using to get the result you're looking for.

Is this a test for one line item or is this to generate a result for all line items? Do you have a column in the bill tables to specify which line item this bill is for?

I'm sure it's clear from the questions that I don't understand how everything is related or what the query is trying to achieve.

#30 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 February 2006 - 12:20 PM

I should mention that you can try this if everything else is fine though.
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] IFNULL(BH.price,IFNULL(BM.price, BL.price)) FROM bill_type [!--sql2--][/div][!--sql3--]

#31 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 28 February 2006 - 03:39 PM

after screwing around with it for a bit I did get it to work using the IFNULL suggestion, although I had to change it to say

SELECT IFNULL(BH.price,IFNULL(BM.price, BL.price)) AS price

Or else it wouldn't return any price results.

is there a way to return some string variable as well to let me know which table was selected to be used? That would be really helpful for testing and debugging. If not I can always just select BH.id as BHid, BM.id as BMid, etc and then just use php to check for precedence and print out a string based on that.

#32 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 February 2006 - 04:04 PM

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span]
IFNULL(BH.price,IFNULL(BM.price, BL.price)) AS price,
IF (ISNULL(BH.price), IF(ISNULL(BM.price), 'BL', 'BM'), 'BH') AS tbl [!--sql2--][/div][!--sql3--]
It may be better to use the same method for choosing the price as when choosing the table. Although it shouldn't matter theoretically. I'd still do it.
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span]
IF (ISNULL(BH.price), IF(ISNULL(BM.price), BL.price, BM.price), BH.price) AS price
IF (ISNULL(BH.price), IF(ISNULL(BM.price), 'BL', 'BM'), 'BH') AS tbl [!--sql2--][/div][!--sql3--]
Concatenating the name to the price is also an option instead of using seperate columns

#33 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 28 February 2006 - 04:10 PM

Thanks a lot for all the help!

After thinking about it a bit I will probably be pulling the vaules for all the tables as well because it would be pretty useful for debugging if I can post a small note on the report with the table used highlighted and all the other tables listed with prices they have. At least until we get all the bugs worked out.

#34 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 February 2006 - 05:04 PM

After thinking about it a bit I will probably be pulling the vaules for all the tables as well

Just keep in mind that I don't think I know enough about the tables to consider problems .You'll have to make sure that if there are any errors that you'll find them.

#35 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 28 February 2006 - 06:29 PM

[!--quoteo(post=350281:date=Feb 28 2006, 11:04 AM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Feb 28 2006, 11:04 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Just keep in mind that I don't think I know enough about the tables to consider problems .You'll have to make sure that if there are any errors that you'll find them.
[/quote]

Believe me, there will be more hours of my time put to testing this than I care to think about at the moment ;)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users