Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query that doesn't use any indexes:

FROM   test
WHERE  level >= 20
       AND ( ( fun_GetIndexValue(data, 354) >> 16 ) + 
             ( fun_GetIndexValue(data, 355) >> 16 ) + 
             ( fun_GetIndexValue(data, 356) >> 16 ) + 
             ( fun_GetIndexValue(data, 357) >> 16 ) + 
             ( fun_GetIndexValue(data, 358) >> 16 ) + 
             ( fun_GetIndexValue(data, 359) >> 16 ) ) >= 1; 

The level column has only about 80-90 distinct values, the table test has about million rows, and the data column is passed to the function, so I think the query can not use any indexes. But I found that if I put the level condition in the end, the query performs slower. Why is that?

share|improve this question
Can you add the execution plan of both queries (level column at the start of the where clause and at the end of it), using the EXPLAIN statement (dev.mysql.com/doc/refman/4.1/en/using-explain.html) ? –  KookieMonster Apr 15 '13 at 8:33
add comment

closed as unclear what you're asking by Paul White, Mark Storey-Smith, RolandoMySQLDBA, Kin, Michael - sqlbot Feb 27 at 20:20

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

Maybe because the results from the first "form" of the query are already cached ? As lyxing said, post the execution plan. It will be interesting to see how the optimizer chooses the execution plan in both cases.

share|improve this answer
The execution plan of both queries are sameness: id|select_type|table|type|possible_keys|key |key_len|ref |rows |Extra 1 |SIMPLE |test |ALL |NULL |NULL|NULL |NULL|221405|"Using where" –  lyxing Apr 17 '13 at 3:09
and,in my session,when i set profiling=1;then execute the statement.but when i execute "show profiles" command,no sql profile can be found. –  lyxing Apr 17 '13 at 7:33
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.