Re: Still struggling witn like 'CTV%' over varchar.... I simple cannot understand..
- Original Message - > From: "Andrés Tello" > > showed the usage of the index, then, some time later, it show, for > the same query, the usage of no index... Look at the "rows" field. It's obvious that this table is live and rather on the active side; and the data has changed in such a way that on the second explain, the optimizer estimates that there would be little benefit from using that key - most likely due to cardinality. Make a copy of the table so your data is static, and you'll get the same explain every time. That will, however, apparently not simulate the real world for you. If this is a MyISAM table you may need to run ANALYZE TABLE to update the statistics; or you may just have to accept that the same query on different data may benefit from a different execution plan - just as a different query on the same data would. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Still struggling witn like 'CTV%' over varchar.... I simple cannot understand..
mysql> explain select * from cuenta where rutaCuenta like 'CTV%'; ++-++---+---++-+--++-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-++---+---++-+--++-+ | 1 | SIMPLE | cuenta | range | rutaCuenta| rutaCuenta | 258 | NULL | 876824 | Using where | ++-++---+---++-+--++-+ 1 row in set (0.00 sec) mysql> explain select * from cuenta where rutaCuenta like 'CTV%'; ++-++--+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-++--+---+--+-+--+-+-+ | 1 | SIMPLE | cuenta | ALL | rutaCuenta| NULL | NULL| NULL | 5274306 | Using where | ++-++--+---+--+-+--+-+-+ 1 row in set (0.00 sec) Any ideas? I'm creating running a process which populates the rutaCuenta field with some codification to retrieve some hierarchical based in that field... I did the explain meanwhile doing the populating process, and it showed the usage of the index, then, some time later, it show, for the same query, the usage of no index... Why? I really appreciate some guidance... I find no logic at all...
