> i have a simple query > > select u.*,p.* from users u, profiles p > where u.uname = p.uname > and u.level != 0 > > Is there any tricks to make this use an index. If i do level=0 is uses an > index , but != does not.
MySQL only uses an index if it will return less than approx. 30% of the records. It tries to guess this by looking at the cardinality of the index (the estimated number of different entries). In your case level=0 returned less than 30% of the records, so obviously level!=0 will return more than 70% of the records and MySQL desides to do a table scan (which is probably more efficient than first looking in the index and then searching for almost every single record). You can do a SELECT u.*,p.* FROM users AS u FORCE INDEX (u_level_index) JOIN profiles AS p USING (uname) WHERE u.level != 0 but this will only slow the query down. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
