MySQL forgets it has indexes?

Skip Montanaro

I'm struggling to get a handle on a slow query problem. I'm running
3.23.41. Here's an entry from the slow log file.

# Time: 031006 15:15:43
# User@Host: concerts[concerts] @ localhost []
# Time: 173 Lock_time: 58 Rows_sent: 14 Rows_examined: 361827

select events.id,events.start,events.end from cities straight_join
addresses straight_join venues,events,keywords where cities.latitude <=
39.6940816812 and cities.longitude <= -76.444647426 and cities.latitude

= 38.2448063188 and cities.longitude >= -78.328130574 and events.venue

Jul 19 '05 #1

Subscribe Post Reply

2291

swdev1

Skip - please check again ??
everything AFTER the word 'where'
and all of your columns used for joins .
are you certain you have these columns indexed ?
a manual check is helpful.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
Free Satellite Receivers and installation ->
http://www.vmcsatellite.com/?aid=58456
mySql / VFP / MS-SQL
"Skip Montanaro" <sk**@pobox.com> wrote in message
news:72*************************@posting.google.co m...

I'm struggling to get a handle on a slow query problem. I'm running
3.23.41. Here's an entry from the slow log file.

# Time: 031006 15:15:43
# User@Host: concerts[concerts] @ localhost []
# Time: 173 Lock_time: 58 Rows_sent: 14 Rows_examined: 361827

select events.id,events.start,events.end from cities straight_join
addresses straight_join venues,events,keywords where cities.latitude <= 39.6940816812 and cities.longitude <= -76.444647426 and cities.latitude
>= 38.2448063188 and cities.longitude >= -78.328130574 and

events.venue = venues.id and venues.address = addresses.id and addresses.city =
cities.id and events.end >= '2003-10-06' and events.start <=
'2003-11-30' and (keywords.keyword = 'traditional') and keywords.refid = events.id and keywords.tabletype = 1 and events.submit_date >=
'1976-05-20 15:12:50' and events.active = 1 order by
events.start,events.end limit 1000;

If I ask for an explanation, it tells me something much different (pasted in three pieces to avoid 80-column problems):

+-----------+--------+----------------------------------+---------+
| table | type | possible_keys | key |
+-----------+--------+----------------------------------+---------+
| keywords | ref | keyword,refid,tabletype | keyword |
| events | eq_ref | id,submit_date,venue,date,active | id |
| cities | range | id,latlong | latlong |
| addresses | ref | id,city | city |
| venues | eq_ref | id,address | id |
+-----------+--------+----------------------------------+---------+

+---------+----------------+------+
| key_len | ref | rows |
+---------+----------------+------+
| 32 | const | 841 |
| 4 | keywords.refid | 1 |
| 8 | NULL | 945 |
| 4 | cities.id | 16 |
| 4 | events.venue | 1 |
+---------+----------------+------+

+---------------------------------------------+
| Extra |
+---------------------------------------------+
| where used; Using temporary; Using filesort |
| where used |
| where used |
| |
| where used |
+---------------------------------------------+

Note that it examines several hundred thousand rows although the explain
output suggests it should examine fewer than 2000. It's as if MySQL
forgot it had indexes to consult. Is there some way to confirm this or
force it to rebuild its indexes?

Thanks,

Skip Montanaro
sk**@pobox.com

Jul 19 '05 #2

swdev1

I'm struggling to get a handle on a slow query problem. I'm running
3.23.41. Here's an entry from the slow log file.

# Time: 031006 15:15:43
# User@Host: concerts[concerts] @ localhost []
# Time: 173 Lock_time: 58 Rows_sent: 14 Rows_examined: 361827

select events.id,events.start,events.end from cities straight_join
addresses straight_join venues,events,keywords where cities.latitude <= 39.6940816812 and cities.longitude <= -76.444647426 and cities.latitude
>= 38.2448063188 and cities.longitude >= -78.328130574 and

events.venue = venues.id and venues.address = addresses.id and addresses.city =
cities.id and events.end >= '2003-10-06' and events.start <=
'2003-11-30' and (keywords.keyword = 'traditional') and keywords.refid = events.id and keywords.tabletype = 1 and events.submit_date >=
'1976-05-20 15:12:50' and events.active = 1 order by
events.start,events.end limit 1000;

If I ask for an explanation, it tells me something much different (pasted in three pieces to avoid 80-column problems):

+-----------+--------+----------------------------------+---------+
| table | type | possible_keys | key |
+-----------+--------+----------------------------------+---------+
| keywords | ref | keyword,refid,tabletype | keyword |
| events | eq_ref | id,submit_date,venue,date,active | id |
| cities | range | id,latlong | latlong |
| addresses | ref | id,city | city |
| venues | eq_ref | id,address | id |
+-----------+--------+----------------------------------+---------+

+---------+----------------+------+
| key_len | ref | rows |
+---------+----------------+------+
| 32 | const | 841 |
| 4 | keywords.refid | 1 |
| 8 | NULL | 945 |
| 4 | cities.id | 16 |
| 4 | events.venue | 1 |
+---------+----------------+------+

+---------------------------------------------+
| Extra |
+---------------------------------------------+
| where used; Using temporary; Using filesort |
| where used |
| where used |
| |
| where used |
+---------------------------------------------+

Note that it examines several hundred thousand rows although the explain
output suggests it should examine fewer than 2000. It's as if MySQL
forgot it had indexes to consult. Is there some way to confirm this or
force it to rebuild its indexes?

Thanks,

Skip Montanaro
sk**@pobox.com

Jul 19 '05 #3

Mikko

Skip Montanaro wrote:

I'm struggling to get a handle on a slow query problem. I'm running
3.23.41. Here's an entry from the slow log file.

# Time: 031006 15:15:43
# User@Host: concerts[concerts] @ localhost []
# Time: 173 Lock_time: 58 Rows_sent: 14 Rows_examined: 361827

select events.id,events.start,events.end from cities straight_join
addresses straight_join venues,events,keywords

where cities.latitude <= 39.6940816812 and cities.longitude <= -76.444647426
and
cities.latitude
>= 38.2448063188 and cities.longitude >= -78.328130574

Why do you ask latitude <= 39 , then something else, and then go back doing
range search with latitude?
What you are asking is ALL <= 39, then do something else. Then you ask ALL= 38. Then you ask common rows from these two big groups.

In worst case the database really does what you ask.

Jul 19 '05 #4

Mikko

Skip Montanaro wrote:

I'm struggling to get a handle on a slow query problem. I'm running
3.23.41. Here's an entry from the slow log file.

# Time: 031006 15:15:43
# User@Host: concerts[concerts] @ localhost []
# Time: 173 Lock_time: 58 Rows_sent: 14 Rows_examined: 361827

select events.id,events.start,events.end from cities straight_join
addresses straight_join venues,events,keywords

where cities.latitude <= 39.6940816812 and cities.longitude <= -76.444647426
and
cities.latitude
>= 38.2448063188 and cities.longitude >= -78.328130574

Why do you ask latitude <= 39 , then something else, and then go back doing
range search with latitude?
What you are asking is ALL <= 39, then do something else. Then you ask ALL= 38. Then you ask common rows from these two big groups.

In worst case the database really does what you ask.

Jul 19 '05 #5

Skip Montanaro

Sorry for the delay getting back to this problem. The system hasn't been
acting up, so the problem kind of drifted to the back burner.

Skip - please check again ?? everything AFTER the word 'where' and all of
your columns used for joins . are you certain you have these columns
indexed ? a manual check is helpful.

Sure. Referring to my select statement:

select events.id,events.start,events.end
from cities straight_join addresses straight_join venues,events,keywords
where cities.latitude <= 39.6940816812
and cities.longitude <= -76.444647426
and cities.latitude >= 38.2448063188
and cities.longitude >= -78.328130574
and events.venue = venues.id
and venues.address = addresses.id
and addresses.city = cities.id
and events.end >= '2003-10-06'
and events.start <= '2003-11-30'
and (keywords.keyword = 'traditional')
and keywords.refid = events.id
and keywords.tabletype = 1
and events.submit_date >= '1976-05-20 15:12:50'
and events.active = 1
order by events.start,events.end limit 1000;

here are the various index definitions (all pasted from the definitions I
used to create the tables):

cities table:
PRIMARY KEY (id),
KEY city (city),
KEY state (state),
KEY country (country),
KEY latlong (latitude,longitude)

events table:
PRIMARY KEY (id),
KEY submitter (submitter),
KEY venue (venue,active),
KEY event (event,active),
KEY date (start,active,end),
KEY active (active),
KEY end (end),
KEY start (start)

venues table:
PRIMARY KEY (id),
KEY venue (venue),
KEY address (address),
KEY contact_address (contact_address)

addresses table:
PRIMARY KEY (id),
KEY zip (zip),
KEY city (city)

keywords table:
KEY keyword (keyword),
KEY refid (refid),
KEY tabletype (tabletype)

It occurs to me looking at this that I may have a problem with the
cities.longitude column, since it's not the leftmost element of a KEY.
Still, that doesn't explain why the EXPLAIN statement expected so few rows to
be examined (a few hundred to a few thousand) and the slow log showed so
many were actually examined when I ran that query back in early October (>
360,000). I just tried the above SELECT. It returned 10 rows in 0.16
seconds. Clearly, the system didn't have to examine hundreds of thousands
of rows to return results. The database is about the same size as when I
originally posted and I haven't changed any table definitions. Like I said,
every now and then it seems MySQL forgets altogether that it has indexes
and doesn't use them.

Skip Montanaro
Got gigs? http://www.musi-cal.com/
http://www.mojam.com/
Got spam? http://spambayes.sf.net/

Jul 19 '05 #6

Skip Montanaro

Sorry for the delay getting back to this problem. The system hasn't been
acting up, so the problem kind of drifted to the back burner.

Skip - please check again ?? everything AFTER the word 'where' and all of
your columns used for joins . are you certain you have these columns
indexed ? a manual check is helpful.

Jul 19 '05 #7

Skip Montanaro

> What you are asking is ALL <= 39, then do something else. Then you ask ALL

= 38. Then you ask common rows from these two big groups.

That's an easy misread to make. Checking the select statement:

select events.id,events.start,events.end
from cities straight_join addresses straight_join venues,events,keywords
where cities.latitude <= 39.6940816812
and cities.longitude <= -76.444647426
and cities.latitude >= 38.2448063188
and cities.longitude >= -78.328130574
and events.venue = venues.id
and venues.address = addresses.id
and addresses.city = cities.id
and events.end >= '2003-10-06'
and events.start <= '2003-11-30'
and (keywords.keyword = 'traditional')
and keywords.refid = events.id
and keywords.tabletype = 1
and events.submit_date >= '1976-05-20 15:12:50'
and events.active = 1
order by events.start,events.end limit 1000;

note that I am looking for

38.2448063188 <= cities.latitude <= 39.6940816812

and

-78.328130574 <= cities.longitude <= -76.444647426

that is, I'm constraining the search geographically to a small region.
Given that the cities table has a key defined as

KEY latlong (latitude,longitude)

at least half of those four comparisons should be fast. The search
optimizer can probably arrange the comparisons to take advantage of the
latlong key.

Just to reiterate, most of the time this sort of query is very fast (well
under one second, typically less than 0.2 second). Every now and again it
balloons to dozens of seconds though. I don't think this is a case of bits
of the database being locked and the thread performing the search having to
cool its heels waiting for locks to be released either, because sometimes a
SELECT of this form is the first which appears in a whole slew of slow
queries that are logged. In addition, EXPLAINing that query disagrees
wildly with how many rows are actually examined.

Skip Montanaro
Got gigs? http://www.musi-cal.com/
http://www.mojam.com/
Got spam? http://spambayes.sf.net/

Jul 19 '05 #8

Skip Montanaro

> What you are asking is ALL <= 39, then do something else. Then you ask ALL

= 38. Then you ask common rows from these two big groups.

Jul 19 '05 #9

swdev1

Skip -
you have a compound index for latitude,longitude [latlong]
thats ok - but would suggest that you make TWO more - one for Latitude and
another for Longitude.
Try that, and see if your performance metric changes ?
regards [Bill]

--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
Free Satellite Receivers and installation ->
http://www.vmcsatellite.com/?aid=58456
mySql / VFP / MS-SQL
"Skip Montanaro" <sk**@pobox.com> wrote in message
news:72**************************@posting.google.c om...

Sorry for the delay getting back to this problem. The system hasn't been
acting up, so the problem kind of drifted to the back burner.
Skip - please check again ?? everything AFTER the word 'where' and all of your columns used for joins . are you certain you have these columns
indexed ? a manual check is helpful.
Sure. Referring to my select statement:

select events.id,events.start,events.end
from cities straight_join addresses straight_join

venues,events,keywords where cities.latitude <= 39.6940816812
and cities.longitude <= -76.444647426
and cities.latitude >= 38.2448063188
and cities.longitude >= -78.328130574
and events.venue = venues.id
and venues.address = addresses.id
and addresses.city = cities.id
and events.end >= '2003-10-06'
and events.start <= '2003-11-30'
and (keywords.keyword = 'traditional')
and keywords.refid = events.id
and keywords.tabletype = 1
and events.submit_date >= '1976-05-20 15:12:50'
and events.active = 1
order by events.start,events.end limit 1000;

here are the various index definitions (all pasted from the definitions I
used to create the tables):

cities table:
PRIMARY KEY (id),
KEY city (city),
KEY state (state),
KEY country (country),
KEY latlong (latitude,longitude)

events table:
PRIMARY KEY (id),
KEY submitter (submitter),
KEY venue (venue,active),
KEY event (event,active),
KEY date (start,active,end),
KEY active (active),
KEY end (end),
KEY start (start)

venues table:
PRIMARY KEY (id),
KEY venue (venue),
KEY address (address),
KEY contact_address (contact_address)

addresses table:
PRIMARY KEY (id),
KEY zip (zip),
KEY city (city)

keywords table:
KEY keyword (keyword),
KEY refid (refid),
KEY tabletype (tabletype)

It occurs to me looking at this that I may have a problem with the
cities.longitude column, since it's not the leftmost element of a KEY.
Still, that doesn't explain why the EXPLAIN statement expected so few rows to be examined (a few hundred to a few thousand) and the slow log showed so
many were actually examined when I ran that query back in early October (>
360,000). I just tried the above SELECT. It returned 10 rows in 0.16
seconds. Clearly, the system didn't have to examine hundreds of thousands
of rows to return results. The database is about the same size as when I
originally posted and I haven't changed any table definitions. Like I said, every now and then it seems MySQL forgets altogether that it has indexes
and doesn't use them.

Skip Montanaro
Got gigs? http://www.musi-cal.com/
http://www.mojam.com/
Got spam? http://spambayes.sf.net/

Jul 19 '05 #10

swdev1

Sorry for the delay getting back to this problem. The system hasn't been
acting up, so the problem kind of drifted to the back burner.
Skip - please check again ?? everything AFTER the word 'where' and all of your columns used for joins . are you certain you have these columns
indexed ? a manual check is helpful.
Sure. Referring to my select statement:

select events.id,events.start,events.end
from cities straight_join addresses straight_join

venues,events,keywords where cities.latitude <= 39.6940816812
and cities.longitude <= -76.444647426
and cities.latitude >= 38.2448063188
and cities.longitude >= -78.328130574
and events.venue = venues.id
and venues.address = addresses.id
and addresses.city = cities.id
and events.end >= '2003-10-06'
and events.start <= '2003-11-30'
and (keywords.keyword = 'traditional')
and keywords.refid = events.id
and keywords.tabletype = 1
and events.submit_date >= '1976-05-20 15:12:50'
and events.active = 1
order by events.start,events.end limit 1000;

here are the various index definitions (all pasted from the definitions I
used to create the tables):

cities table:
PRIMARY KEY (id),
KEY city (city),
KEY state (state),
KEY country (country),
KEY latlong (latitude,longitude)

events table:
PRIMARY KEY (id),
KEY submitter (submitter),
KEY venue (venue,active),
KEY event (event,active),
KEY date (start,active,end),
KEY active (active),
KEY end (end),
KEY start (start)

venues table:
PRIMARY KEY (id),
KEY venue (venue),
KEY address (address),
KEY contact_address (contact_address)

addresses table:
PRIMARY KEY (id),
KEY zip (zip),
KEY city (city)

keywords table:
KEY keyword (keyword),
KEY refid (refid),
KEY tabletype (tabletype)

It occurs to me looking at this that I may have a problem with the
cities.longitude column, since it's not the leftmost element of a KEY.
Still, that doesn't explain why the EXPLAIN statement expected so few rows to be examined (a few hundred to a few thousand) and the slow log showed so
many were actually examined when I ran that query back in early October (>
360,000). I just tried the above SELECT. It returned 10 rows in 0.16
seconds. Clearly, the system didn't have to examine hundreds of thousands
of rows to return results. The database is about the same size as when I
originally posted and I haven't changed any table definitions. Like I said, every now and then it seems MySQL forgets altogether that it has indexes
and doesn't use them.

Skip Montanaro
Got gigs? http://www.musi-cal.com/
http://www.mojam.com/
Got spam? http://spambayes.sf.net/

Jul 19 '05 #11

Benoit St-Jean

sk**@pobox.com (Skip Montanaro) wrote in message news:<72**************************@posting.google. com>...

What you are asking is ALL <= 39, then do something else. Then you ask ALL
= 38. Then you ask common rows from these two big groups.

That's an easy misread to make. Checking the select statement:

select events.id,events.start,events.end
from cities straight_join addresses straight_join venues,events,keywords
where cities.latitude <= 39.6940816812
and cities.longitude <= -76.444647426
and cities.latitude >= 38.2448063188
and cities.longitude >= -78.328130574
and events.venue = venues.id
and venues.address = addresses.id
and addresses.city = cities.id
and events.end >= '2003-10-06'
and events.start <= '2003-11-30'
and (keywords.keyword = 'traditional')
and keywords.refid = events.id
and keywords.tabletype = 1
and events.submit_date >= '1976-05-20 15:12:50'
and events.active = 1
order by events.start,events.end limit 1000;

note that I am looking for

38.2448063188 <= cities.latitude <= 39.6940816812

and

-78.328130574 <= cities.longitude <= -76.444647426

that is, I'm constraining the search geographically to a small region.
Given that the cities table has a key defined as

KEY latlong (latitude,longitude)

at least half of those four comparisons should be fast. The search
optimizer can probably arrange the comparisons to take advantage of the
latlong key.

Just to reiterate, most of the time this sort of query is very fast (well
under one second, typically less than 0.2 second). Every now and again it
balloons to dozens of seconds though. I don't think this is a case of bits
of the database being locked and the thread performing the search having to
cool its heels waiting for locks to be released either, because sometimes a
SELECT of this form is the first which appears in a whole slew of slow
queries that are logged. In addition, EXPLAINing that query disagrees
wildly with how many rows are actually examined.

Can you provide us with the EXPLAIN, DESCRIBE and SHOW INDEX of tables
involved for this query?

Have you tried using BETWEEN instead of >= and <= ?
Can

Jul 19 '05 #12

Benoit St-Jean

sk**@pobox.com (Skip Montanaro) wrote in message news:<72**************************@posting.google. com>...

What you are asking is ALL <= 39, then do something else. Then you ask ALL
= 38. Then you ask common rows from these two big groups.

That's an easy misread to make. Checking the select statement:

select events.id,events.start,events.end
from cities straight_join addresses straight_join venues,events,keywords
where cities.latitude <= 39.6940816812
and cities.longitude <= -76.444647426
and cities.latitude >= 38.2448063188
and cities.longitude >= -78.328130574
and events.venue = venues.id
and venues.address = addresses.id
and addresses.city = cities.id
and events.end >= '2003-10-06'
and events.start <= '2003-11-30'
and (keywords.keyword = 'traditional')
and keywords.refid = events.id
and keywords.tabletype = 1
and events.submit_date >= '1976-05-20 15:12:50'
and events.active = 1
order by events.start,events.end limit 1000;

note that I am looking for

38.2448063188 <= cities.latitude <= 39.6940816812

and

-78.328130574 <= cities.longitude <= -76.444647426

that is, I'm constraining the search geographically to a small region.
Given that the cities table has a key defined as

KEY latlong (latitude,longitude)

at least half of those four comparisons should be fast. The search
optimizer can probably arrange the comparisons to take advantage of the
latlong key.

Just to reiterate, most of the time this sort of query is very fast (well
under one second, typically less than 0.2 second). Every now and again it
balloons to dozens of seconds though. I don't think this is a case of bits
of the database being locked and the thread performing the search having to
cool its heels waiting for locks to be released either, because sometimes a
SELECT of this form is the first which appears in a whole slew of slow
queries that are logged. In addition, EXPLAINing that query disagrees
wildly with how many rows are actually examined.

Can you provide us with the EXPLAIN, DESCRIBE and SHOW INDEX of tables
involved for this query?

Have you tried using BETWEEN instead of >= and <= ?
Can

Jul 19 '05 #13

Skip Montanaro

> Can you provide us with the EXPLAIN, DESCRIBE and SHOW INDEX of tables

involved for this query?
Sure:

http://manatee.mojam.com/~skip/explain-stuff.txt
Have you tried using BETWEEN instead of >= and <= ?

Nope. The docs I have suggest they should work identically, but I'll give
BETWEEN a try.

Skip

Jul 19 '05 #14

Skip Montanaro

> you have a compound index for latitude,longitude [latlong]

thats ok - but would suggest that you make TWO more - one for Latitude and
another for Longitude.
Try that, and see if your performance metric changes ?

I'll give it a try, but note that the performance is not always bad. In fact, it is
rarely bad.

Skip

Jul 19 '05 #15

Skip Montanaro

> Can you provide us with the EXPLAIN, DESCRIBE and SHOW INDEX of tables

involved for this query?
Sure:

http://manatee.mojam.com/~skip/explain-stuff.txt
Have you tried using BETWEEN instead of >= and <= ?

Nope. The docs I have suggest they should work identically, but I'll give
BETWEEN a try.

Skip

Jul 19 '05 #16

Skip Montanaro

> you have a compound index for latitude,longitude [latlong]

thats ok - but would suggest that you make TWO more - one for Latitude and
another for Longitude.
Try that, and see if your performance metric changes ?

I'll give it a try, but note that the performance is not always bad. In fact, it is
rarely bad.

Skip

Jul 19 '05 #17

MySQL forgets it has indexes?

Similar topics