Return to Question

select count(*) from xx where xxx = '12' and xxxxx like 'df' and xx like 'yyy' and xxxx like 'yyyy' and xxxx like '%' and xxx=0 and xxx like '%server%' and xxx = 'yyyy' and xxx like '%yyyy%' and xxx like '%yyy%'

select  count(*)
    from  xx
    where  xxx = '12'
      and  xxxxx like 'df'
      and  xx like 'yyy'
      and  xxxx like 'yyyy'
      and  xxxx like '%'
      and  xxx=0
      and  xxx like '%server%'
      and  xxx = 'yyyy'
      and  xxx like '%yyyy%'
      and  xxx like '%yyy%' 

mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 3939  |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 3939  |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec) 



mysql -e "show variables;" |grep myisam
myisam_data_pointer_size        6
myisam_max_sort_file_size       9223372036853727232
myisam_mmap_size        18446744073709551615
myisam_recover_options  BACKUP,FORCE
myisam_repair_threads   1
myisam_sort_buffer_size 67108864
myisam_stats_method     nulls_unequal
myisam_use_mmap OFF

Infact the tables are of myisam engine and not Innodb and the select is being done from a View.

********** UPDATE ***************

mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 3939  |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)