Hi Sean - thanks for the info, unfortunately, I can't use force index ...too old a version of mysql
I wonder if there is some way to "trick" mysql into using an index....? -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, June 25, 2004 11:16 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Why won't mysql use the index? WAS: strange table speed issue Here is what the manual says about the table scan threshold: (http://dev.mysql.com/doc/mysql/en/Where_optimisations.html) Each table index is queried, and the best index is used unless the optimizer believes that it will be more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table. Now the optimizer is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size, so a fixed percentage no longer determines the choice between using an index or a scan. I thought of asking you to try adding the ORG column to your index (to create a "covering index") but then I read this: In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query. Since ORG is a varchar , the blurb says it would do a table seek anyway. Oh, well.... from (http://dev.mysql.com/doc/mysql/en/How_to_avoid_table_scan.html): Use FORCE INDEX for the scanned table to tell MySQL that table scans are very expensive compared to using the given index. I can't remember if you tried that yet or not. Sorry I couldn't be more help, Shawn Green Database Administrator Unimin Corporation - Spruce Pine, [EMAIL PROTECTED] om To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> cc: 06/25/2004 01:52 Fax to: PM Subject: RE: Why won't mysql use the index? WAS: strange table speed issue The general rule of thumb (in Sybase not sure for MySQL), is if using an index hits more than 10% of the tables total volume it is faster/cheaper/less evasive on the drive to just table scan the table opposed to read an index row, get the representing table row pointer and seek the table for each qualifying row. Your aproximate ration is .6m/3=20%, again 10% is just a rule of thumb, many other things come into play... The reason your "count(*)" used the index is because it doesn't request/result any data table columns. Since the index and the data table has the same number of rows and your "where" clause only uses indexed columns it faster to read/scan the index row because it is "org varchar(80)" bytes shorter and each disk i/o and can read/buffer more index rows that table rows in the same size disk read. My free humble opinion, Ed -----Original Message----- From: MerchantSense [mailto:[EMAIL PROTECTED] Sent: Friday, June 25, 2004 10:56 AM To: [EMAIL PROTECTED] Subject: Why won't mysql use the index? WAS: strange table speed issue This is crazy. If someone can help me out, I'll pay them....! A table: +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | ip_start | bigint(20) | | MUL | 0 | | | ip_end | bigint(20) | | | 0 | | | org | varchar(80) | | | | | +----------+-------------+------+-----+---------+-------+ Populated with numbers for the 1st 2 fields, about 2.9 million records Indexes as such: mysql> show index from ip2org; +--------+------------+----------+--------------+-------------+--------- --+- ------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--------+------------+----------+--------------+-------------+--------- --+- ------------+----------+--------+---------+ | ip2org | 1 | dex | 1 | ip_start | A | 2943079 | NULL | NULL | | | ip2org | 1 | dex | 2 | ip_end | A | 2943079 | NULL | NULL | | +--------+------------+----------+--------- -----+-------------+-----------+-------------+----------+--------+------ ---+ I do this query: mysql> explain SELECT org from ip2org use index (dex) where ip_start<=1094799892 and ip_end>=1094799892; +--------+------+---------------+------+---------+------+---------+----- ---- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------+------+---------------+------+---------+------+---------+----- ---- ---+ | ip2org | ALL | dex | NULL | NULL | NULL | 2943079 | where used | +--------+------+---------------+------+---------+------+---------+----- ---- ---+ And it will not use the index, but if I do this ( a count): mysql> explain SELECT count(*) from ip2org use index (dex) where ip_start<=1094799892 and ip_end>=1094799892; +--------+-------+---------------+------+---------+------+--------+----- ---- ----------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------+-------+---------------+------+---------+------+--------+----- ---- ----------------+ | ip2org | range | dex | dex | 8 | NULL | 594025 | where used; Using index | +--------+-------+---------------+------+---------+------+--------+----- ---- ----------------+ It will use the index. WHY can't I get it to use the index on a query with siple firlds with numeric values?? The query takes about 12 seconds.... in fact when I do the count, it still takes that long (maybe it just *thinks* it's using the indexes !)..... this should return a value in less than sec.... I've used tables this big without this problem before... what's up? No matter how a screw around with the indexes, I can't make it work... Help! :) -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Thursday, June 24, 2004 11:41 PM To: Marc Slemko Cc: MerchantSense; [EMAIL PROTECTED] Subject: Re: strange table speed issue I'm not certain, but I don't think a multi-column index will help here. The manual is unclear on how a multi-column index is used when you are comparing the first key part to a range rather than to a constant, but I get the impression it doesn't use the second key part in that case. For you, that would mean your multi-column index is no better than your single column indexes. The problem is that with either column, the range of matches is large enough that the optimizer judges a table scan will be quicker than all those key lookups. You can see this in the EXPLAIN output, type = ALL and rows = the size of your table. Both indicate a full table scan. You may be able to do better if you know something about the ranges defined by ip_start and ip_end, particularly if ip2org is relatively static. You can find the size of the largest range with SELECT MAX(ip_end - ip_start) FROM ip2org; Suppose that comes back with 1500. Then the matching row will have ip_start no less than your ip (1094799892) - 1500, and it will have ip_end no more than your ip + 1500. Then SELECT org FROM ip2org WHERE ip_start BETWEEN 1094799892-1500 AND 1094799892 AND ip_end BETWEEN 1094799892 AND 1094799892 + 1500; specifies a small range on each column, enabling use of one index or the other for fast lookups. Note that this will break for ip < 1500 or ip > max(ip) - 1500, but those should already use one or the other index with your original query. Michael Marc Slemko wrote: > On Thu, 24 Jun 2004, MerchantSense wrote: > > >>Seems ok to me... >> >>It seems to be checking all the rows in the explain for some reason too... >> >>mysql> show index from ip2org; >>+--------+------------+----------+--------------+-------------+------- ---- +- >>------------+----------+--------+---------+ >>| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | >>Cardinality | Sub_part | Packed | Comment | >>+--------+------------+----------+--------------+-------------+------- ---- +- >>------------+----------+--------+---------+ >>| ip2org | 1 | ip_start | 1 | ip_start | A | >>2943079 | NULL | NULL | | >>| ip2org | 1 | ip_end | 1 | ip_end | A | >>2943079 | NULL | NULL | | >>+--------+------------+----------+--------------+-------------+------- ---- +- >>------------+----------+--------+---------+ > > > mysql can only use one index from a particular table in any one > query. So if you want to do a query that uses both ip_start and > ip_end, you would need to create a multicolumn index on ip_start,ip_end > or vice versa. > > What you have is one index on ip_start, and another on ip_end. So > it can use one of the indexes, but then it has to scan each row that > matches. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
