MSSQL to Mysql COUNT() possible Index Problem

[d2g]

Well we are week 3 in our conversion to mysql from mssql. This one we had to rewrite the mssql version to mysql. We are having issues with a few count statments.

this one works fine. and returns results right away.

SELECT DISTINCT iteminformation.slotid1,
iteminformation.slotid2,
iteminformation.slotid6,
iteminformation.defaultpic,
iteminformation.itemnumber,
iteminformation.stockupdated,
iteminformation.manufacturername,
iteminformation.conditionshort
FROM iteminformation, itemlocation
where iteminformation.itemnumber = itemlocation.itemnumber
and iteminformation.companyid = 26773049
and itemlocation.companyid = 26773049
and iteminformation.slottype = 'config'
and iteminformation.retired = 'false'
and (iteminformation.deleterec = 'false' or iteminformation.deleterec is Null)
and (itemlocation.deleterec = 'false' or itemlocation.deleterec is Null)
order by iteminformation.slotid1

As soon as we include our count statement to give us the total paid (as counterpaid)for each item returned performance drops to a crawl and the result takes forever to be returned.

SELECT DISTINCT iteminformation.slotid1,
iteminformation.slotid2,
iteminformation.slotid6,
iteminformation.defaultpic,
iteminformation.itemnumber,
iteminformation.stockupdated,
iteminformation.manufacturername,
iteminformation.conditionshort,
(Select Count(stockid) from itemlocation where iteminformation.companyid = itemlocation.companyid and itemlocation.itemnumber = iteminformation.itemnumber ) as counterpaid
FROM iteminformation, itemlocation
where iteminformation.itemnumber = itemlocation.itemnumber
and iteminformation.companyid = 26773049
and itemlocation.companyid = 26773049
and iteminformation.slottype = 'config'
and iteminformation.retired = 'false'
and (iteminformation.deleterec = 'false' or iteminformation.deleterec is Null)
and (itemlocation.deleterec = 'false' or itemlocation.deleterec is Null)
order by iteminformation.slotid1

I'm thinking it has something to do with the indexes setup in mysql for the itemlocation table. Please advise us to what we should be using for indexes to make this query work efficiently. right now we have the following indexes:

itemlocation indexes
PRIMARY STOCKID UNIQUE
LOTCODE LOTCODE
ASSIGNED ASSIGNED
ITEMNUMBERID COMPANYID,ITEMNUMBER

iteminformation indexes
PRIMARY ID UNIQUE
ID ID UNIQUE
ITEMNUMBERID COMPANYID,ITEMNUMBER


Any help / advice would be great! ~ this originally worked flawless and fast in MS SQL. without any additional indexes.

 

[d2g]

Whoops, the correct sql that isn't working is this, forgot some info on the above (Notice the count() AS statement)

SELECT DISTINCT iteminformation.slotid1,
iteminformation.slotid2,
iteminformation.slotid6,
iteminformation.defaultpic,
iteminformation.itemnumber,
iteminformation.stockupdated,
iteminformation.manufacturername,
iteminformation.conditionshort,
(Select Count(*) from itemlocation where iteminformation.companyid = 26773049 and itemlocation.companyid = 26773049 and itemlocation.itemnumber = iteminformation.itemnumber and itemlocation.sold = 'true' and itemlocation.holderrec = 'false' and itemlocation.paid = 'true' and (itemlocation.deleterec = 'false' or itemlocation.deleterec is Null) and itemlocation.shipped = 'false' ) as counterpaid
FROM iteminformation, itemlocation
where iteminformation.itemnumber = itemlocation.itemnumber
and iteminformation.companyid = 26773049
and itemlocation.companyid = 26773049
and iteminformation.slottype = 'config'
and iteminformation.retired = 'false'
and (iteminformation.deleterec = 'false' or iteminformation.deleterec is Null)
and (itemlocation.deleterec = 'false' or itemlocation.deleterec is Null)
order by iteminformation.slotid1

 

[r937]

select distinct 
       II.slotid1
     , II.slotid2
     , II.slotid6
     , II.defaultpic
     , II.itemnumber
     , II.stockupdated
     , II.manufacturername
     , II.conditionshort
     , (select count(*) 
          from itemlocation 
         where companyid = II.companyid 
           and itemnumber = II.itemnumber 
           and sold = 'true' 
           and holderrec = 'false' 
           and paid = 'true' 
           and coalesce(deleterec,'false')
                       = 'false' 
           and shipped = 'false' ) 
          as counterpaid
  from iteminformation as II
inner
  join itemlocation as IL
    on II.itemnumber = IL.itemnumber 
   and II.companyid =  IL.companyid
   and coalesce(IL.deleterec,'false')
                 = 'false' 
 where II.companyid = 26773049 
   and II.slottype = 'config' 
   and II.retired = 'false' 
   and coalesce(II.deleterec,'false') 
                 = 'false'
order 
    by II.slotid1

try it with and without the DISTINCT

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[d2g]

The iteminformation table contains the description of the item,
then the itemlocation table contains a row for each piece of stock for that item. If I dont use distinct the query returns a row
for each piece of stock not just 1 for each item if im using the inner join.

If I take out the inner join and dont use distinct i get the best performance.. Unfortunatly i have alternate
verions of this query that will need to use information from the itemlocation table in order to know what items to display.
(this completed in 31ms and was exactly the performance I was looking for, except it doesn't give me the ability to dispaly only items that have stock that
are not sold, paid, shipped, etc in the itemlocation table) (I also included all of the counters with this one and it sill calculated in 31ms)

Select II.slotid1,
II.slotid2,
II.slotid6,
II.defaultpic,
II.itemnumber,
II.stockupdated,
II.manufacturername,
II.conditionshort,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and assigned = 0 and holderrec = 'false' and sold = 'false' and paid = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as counter1,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and assigned = 100 and holderrec = 'false' and sold = 'false' and paid = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as counter2,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and assigned = 1010 and holderrec = 'false' and sold = 'false' and paid = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as counter3,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and assigned = 1020 and holderrec = 'false' and sold = 'false' and paid = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as counter4,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and holderrec = 'false' and sold = 'true' and paid = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as countersold,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and holderrec = 'false' and sold = 'true' and paid = 'true' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as counterpaid,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and holderrec = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as countertotal
FROM iteminformation as II
where II.companyid = 26773049
and II.slottype = 'config'
and II.retired = 'false'
and coalesce(II.deleterec,'false') = 'false'
order by countertotal


Here is one without the distinct, it returns one row for each piece of stock (this isn't goign to work because im trying to display one of each item that
contains stock that has been sold, paid, etc) This showed in my query history as taking only 312ms but it didn't display it that quickly.. My thoughts are
it finished the query in that time, but took alot longer to display all of the information in my query browser)

Select II.slotid1,
II.slotid2,
II.slotid6,
II.defaultpic,
II.itemnumber,
II.stockupdated,
II.manufacturername,
II.conditionshort,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and holderrec = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as countertotal
FROM iteminformation as II
inner join itemlocation as IL
on II.itemnumber = IL.itemnumber
and II.companyid = IL.companyid
and coalesce(IL.deleterec,'false') = 'false'
where II.companyid = 26773049
and II.slottype = 'config'
and II.retired = 'false'
and coalesce(II.deleterec,'false') = 'false'
order by II.slotid1

This next one is the same, but with distinct.. The reason for the distinct is to limit the results returend to just unique items.
when using the inner join. This query took 39500 ms to complete which wont work in my application..

Select distinct II.slotid1,
II.slotid2,
II.slotid6,
II.defaultpic,
II.itemnumber,
II.stockupdated,
II.manufacturername,
II.conditionshort,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and holderrec = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as countertotal
FROM iteminformation as II
inner join itemlocation as IL
on II.itemnumber = IL.itemnumber
and II.companyid = IL.companyid
and coalesce(IL.deleterec,'false') = 'false'
where II.companyid = 26773049
and II.slottype = 'config'
and II.retired = 'false'
and coalesce(II.deleterec,'false') = 'false'
order by II.slotid1

 

[d2g]

Also wanted to add.. i used the above queries and removed the count() with the distinct i received 328ms and without i received 31 ms..

So it seems there is a problem with combining the distinct innerjoin with the count()thats when everything seems to die.

 

[d2g]

Ok OK, i spoke too soon.. Solved the issue i was having by avoiding the inner join all together and just using a sub query.. Let me know if there is a more efficient way of doing this, but this query worked by allowing me to show only the items that havn't been sold and have atleast 1 stock quantity. (without distinct, and without using the innerjoin) Completed in 234ms.

Select II.slotid1,
II.slotid2,
II.slotid6,
II.defaultpic,
II.itemnumber,
II.stockupdated,
II.manufacturername,
II.conditionshort,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and assigned = 0 and holderrec = 'false' and sold = 'false' and paid = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as counter1,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and assigned = 100 and holderrec = 'false' and sold = 'false' and paid = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as counter2,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and assigned = 1010 and holderrec = 'false' and sold = 'false' and paid = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as counter3,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and assigned = 1020 and holderrec = 'false' and sold = 'false' and paid = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as counter4,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and holderrec = 'false' and sold = 'true' and paid = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as countersold,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and holderrec = 'false' and sold = 'true' and paid = 'true' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as counterpaid,
(Select Count(*) from itemlocation where companyid = II.companyid and itemnumber = II.itemnumber and holderrec = 'false' and shipped = 'false' and coalesce(deleterec,'false') = 'false' ) as countertotal
FROM iteminformation as II
where II.companyid = 26773049
and II.slottype = 'config'
and II.retired = 'false'
and coalesce(II.deleterec,'false') = 'false'
and II.itemnumber IN (Select itemnumber from itemlocation where companyid = 26773049 and holderrec = 'false' and sold = 'false' and coalesce(deleterec,'false') = 'false')
order by countertotal

I used the "And II.Itemnumber IN (select ..." at the end which will allow me to put my where variables for the itemlocation table in.

thanks for your help and again if there is a better way im all ears.. Only way to learn for me is to dive right into it.