> * Mysql, after reading the query, decides wether using an index would be > better than just a table row scan. Hence, it's MySql's decision
MySQL is making the wrong decision. As stated below, it's doing a table scan when it only needs to look at some 2,800 rows out of 970,000 rows. In addition, the "real" query I've been trying to make work (a join) gets nonsense output from EXPLAIN -- some 50k rows from the left table are involved in the join, yet MySQL estimates that 1 row will be! (The query produces correct results, it just takes a very very long time to do so .) > * You can force MySql to use indexes using the 'using index' > option with the > select query.(MySql 'might' still reject the force, not sure of the > circumstances). I have never managed to get MySQL to obey the "using index" option. It produces no effect here. > * The first 2 queries pretty much won't use index because it is not a > specific value that you are asking MySql to search for but just a > NOT NULL, > very different for '=' or even ranges. Using sequence_log_id > 0 produces the same result. Using any particular value in place of 0 produces the same effect. Using sequence_log_id > foo AND sequence_log_id < bar does seem to use the index... explain SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id > 100000 and sequence_log_id < 4000000000; +-------+-------+-----------------+-----------------+---------+------+------ +------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+-----------------+-----------------+---------+------+------ +------------+ | click | range | sequence_log_id | sequence_log_id | 5 | NULL | 10 | where used | +-------+-------+-----------------+-----------------+---------+------+------ +------------+ > * The final query is a Group By and hence it will need to use the index to > group by the Values that it is going to show you. However it seems to be doing a table scan across the actual *data*. It does not seem to be using the index to eliminate rows. > That should use indexes since you are searching for something > specific. You > can also try ranges and it should still use indexes ( a rule of thumb is > that if the return result is more than 30% of the total number of records, > it's faster to do just a full table scan so it still might not > use it if you > specify a very big range) The return result is about 0.3% -- that is a far cry from 30%. Using ranges may work (I'll just use 0 and maxint), but this is definitely not desirable behavior from the MySQL optimizer if I have to resort to such a trick. Using *a* specific value is somewhat inapplicable here because my ultimate goal is to use this table in a *join*. It's not a good thing when the left table *must* do a table scan across 50k rows, and MySQL acts brain dead when scanning the right table and looks at all 970k rows even though it should be getting 1..10 rows out of click for each row from the left table... -JF --------------------------------------------------------------------- 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
