List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:October 6 2011 9:07pm
Subject:Re: 4 minute slow on select count(*) from table - myisam type
View as plain text  
I've sent this email a few times now, mysql list kept rejecting it due to
size, sorry for any duplicates....


I think you need to examine this query in particular:

| 2567 | p_092211 | localhost | p_092211 | Query   |   11 | Sending
data | select oldurl, newurl, id, dateadd from  w6h8a_sh404sef_urls
where newurl <> "" AND soundex(oldurl)  |
| 2568 | p_092211 | localhost | p_092211 | Query   |   69 | Sending
data | select oldurl, newurl, id, dateadd from  w6h8a_sh404sef_urls
where newurl <> "" AND soundex(oldurl)
| 2582 | p_092211 | localhost | p_092211 | Query   |   69 | Locked
 | update `w6h8a_sh404sef_urls` set cpt=(cpt+1) where `oldurl` =
'Camargo-Illinois-Holiday_Light_Tour-H |

One of those has been running for over a minute. Doing a show full
processlist will give the entire query, you could then paste it into your
SQL editor prefixed with explain and see what the heck is taking so long.
Pretty sure it has to do with using a function on on oldurl in the where
clause -- can't use an index when you do this; therefore, the entire table
has to be scanned. Also, since this table doesn't fit into memory, its disk
bound. If you have the ability to modify the table structure and the
software, a column could be added to the table that is the result of
SOUNDEX(oldurl) and then an index added to that column. The where clause
could then use soundex_column=whatever instead and utilize the index.

I haven't used MYISAM in a long time, so i'm not sure about this but.. is
the INSERT locked due to the SELECT queries that have been running for so
long? And are the rest of the selects (with <8s running time) locked by the
INSERT?

-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

Thread
4 minute slow on select count(*) from table - myisam typeJoey L2 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore2 Oct
    • Re: 4 minute slow on select count(*) from table - myisam typeJoey L2 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeBruce Ferrell2 Oct
Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore2 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
    • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
      • Re: 4 minute slow on select count(*) from table - myisam typeBruce Ferrell3 Oct
        • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
          • Re: 4 minute slow on select count(*) from table - myisam typeAndrĂ©s Tello3 Oct
            • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
              • Re: 4 minute slow on select count(*) from table - myisam typeEric Bergen3 Oct
                • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                    • Re: 4 minute slow on select count(*) from table - myisam typeJohnny Withers6 Oct
                      • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                        • Re: 4 minute slow on select count(*) from table - myisam typeJohnny Withers6 Oct
                        • Re: 4 minute slow on select count(*) from table - myisam typeJohan De Meersman6 Oct
                        • Re: 4 minute slow on select count(*) from table - myisam typeRik Wasmus6 Oct
                          • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                            • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore6 Oct
                              • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore6 Oct
                                • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                                  • Re: 4 minute slow on select count(*) from table - myisam typeMichael Dykman6 Oct
Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore6 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
Re: 4 minute slow on select count(*) from table - myisam typeJohnny Withers6 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJohan De Meersman7 Oct
Re: 4 minute slow on select count(*) from table - myisam typeJan Steinman7 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L7 Oct
    • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore7 Oct