Thank you to those of you who have responded to my inquiry about
indexing.
Here is more complete information about the tables involved.
++++++++++++++++++++++++++++++++++
describe Tiger_main
Field Type Null Key Default Extra
tlid varchar(10) PRI
rdid varchar(10) MUL
rdidseg smallint(6) 0
name varchar(30) MUL
symid varchar(30)
fips varchar(5) MUL
cfcc char(3)
fromlat decimal(13,6) MUL 0.000000
fromlong decimal(13,6) MUL 0.000000
tolat decimal(13,6) MUL 0.000000
tolong decimal(13,6) MUL 0.000000
shapepts text YES
length decimal(10,4) YES
show index from Tiger_main
Table Non_unique Key_name Seq_in_index
Column_name Collation Cardinality Sub_part
Packed Comment
Tiger_main 0 TLID 1 tlid A
Tiger_main 1 fips 1 fips A
Tiger_main 1 tolong 1 tolong A
Tiger_main 1 tolat 1 tolat A
Tiger_main 1 fromlong 1 fromlong A
Tiger_main 1 fromlat 1 fromlat A
Tiger_main 1 name 1 name A
Tiger_main 1 speed 1 rdid A
Tiger_main 1 speed 2 symid A
explain select * from Tiger_main where symid > '' and rdid = '' limit 1
table type possible_keys key key_len ref rows
Extra
Tiger_main ref speed speed 10 const 5376
where used
+++++++++++++++++++++++++++++++++
The explain output indicates that the speed index will be used, as it
should. But the actual query performance is very slow, taking over
5 seconds. For comparison, other queries on the same table using
the tolong and tolat fields (also indexed) take well under one second.
I would appreciate either insight into how the optimizer is choosing
whether or not to use the index, or information on how to further
investigate actual server performance and diagnose the problem.
Thank you
David
----
David Keeney [EMAIL PROTECTED]
Travel By Road http://www.travelbyroad.net
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
