optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb
am 16.09.2002 18:45:24 von rafarifeDescription:
Hi Heikki,
I'm Lourdes and I work with Rafa in the MySQL project.
We have done what you have told us. We have run SHOW STATUS before
the query and we have obtained the next result:
Aborted_clients = 0
Aborted_connects = 0
Bytes_received = 0
Bytes_sent = 0
Com_admin_commands = 0
Com_alter_table = 0
Com_analyze = 0
Com_backup_table = 0
Com_begin = 0
Com_change_db = 7
Com_change_master = 0
Com_check = 0
Com_commit = 0
Com_create_db = 0
Com_create_function = 0
Com_create_index = 0
Com_create_table = 0
Com_delete = 0
Com_delete_multi = 0
Com_drop_db = 0
Com_drop_function = 0
Com_drop_index = 0
Com_drop_table = 0
Com_flush = 0
Com_grant = 0
Com_ha_close = 0
Com_ha_open = 0
Com_ha_read = 0
Com_insert = 0
Com_insert_select = 0
Com_kill = 0
Com_load = 0
Com_load_master_data = 0
Com_load_master_table = 0
Com_lock_tables = 0
Com_optimize = 0
Com_purge = 0
Com_rename_table = 0
Com_repair = 0
Com_replace = 0
Com_replace_select = 0
Com_reset = 0
Com_restore_table = 0
Com_revoke = 0
Com_rollback = 0
Com_select = 1
Com_set_option = 0
Com_show_binlog_events = 0
Com_show_binlogs = 0
Com_show_create = 0
Com_show_databases = 1
Com_show_fields = 0
Com_show_grants = 0
Com_show_keys = 0
Com_show_logs = 0
Com_show_master_status = 0
Com_show_new_master = 0
Com_show_open_tables = 0
Com_show_processlist = 2
Com_show_slave_hosts = 0
Com_show_slave_status = 0
Com_show_status = 4
Com_show_innodb_status = 0
Com_show_tables = 6
Com_show_variables = 2
Com_slave_start = 0
Com_slave_stop = 0
Com_truncate = 0
Com_unlock_tables = 0
Com_update = 0
Connections = 5
Created_tmp_disk_tables = 0
Created_tmp_tables = 0
Created_tmp_files = 0
Delayed_insert_threads = 0
Delayed_writes = 0
Delayed_errors = 0
Flush_commands = 1
Handler_commit = 0
Handler_delete = 0
Handler_read_first = 1
Handler_read_key = 0
Handler_read_next = 0
Handler_read_prev = 0
Handler_read_rnd = 0
Handler_read_rnd_next = 10
Handler_rollback = 0
Handler_update = 0
Handler_write = 0
Key_blocks_used = 0
Key_read_requests = 0
Key_reads = 0
Key_write_requests = 0
Key_writes = 0
Max_used_connections = 3
Not_flushed_key_blocks = 0
Not_flushed_delayed_rows = 0
Open_tables = 52
Open_files = 4
Open_streams = 0
Opened_tables = 58
Questions = 25
Qcache_queries_in_cache = 0
Qcache_inserts = 0
Qcache_hits = 0
Qcache_not_cached = 1
Qcache_free_memory = 2088520
Qcache_free_blocks = 1
Qcache_total_blocks = 1
Rpl_status = NULL
Select_full_join = 0
Select_full_range_join = 0
Select_range = 0
Select_range_check = 0
Select_scan = 0
Slave_open_temp_tables = 0
Slave_running = OFF
Slow_launch_threads = 0
Slow_queries = 0
Sort_merge_passes = 0
Sort_range = 0
Sort_rows = 0
Sort_scan = 0
Table_locks_immediate = 57
Table_locks_waited = 0
Threads_cached = 0
Threads_created = 4
Threads_connected = 4
Threads_running = 1
Uptime = 157
The next step was to execute the query in cuestion which took us 0.11 secs.
Then, we ran SHOW STATUS again and we obtained the next result:
Aborted_clients = 0
Aborted_connects = 0
Bytes_received = 0
Bytes_sent = 0
Com_admin_commands = 3
Com_alter_table = 0
Com_analyze = 0
Com_backup_table = 0
Com_begin = 0
Com_change_db = 13
Com_change_master = 0
Com_check = 0
Com_commit = 0
Com_create_db = 0
Com_create_function = 0
Com_create_index = 0
Com_create_table = 0
Com_delete = 0
Com_delete_multi = 0
Com_drop_db = 0
Com_drop_function = 0
Com_drop_index = 0
Com_drop_table = 0
Com_flush = 0
Com_grant = 0
Com_ha_close = 0
Com_ha_open = 0
Com_ha_read = 0
Com_insert = 0
Com_insert_select = 0
Com_kill = 0
Com_load = 0
Com_load_master_data = 0
Com_load_master_table = 0
Com_lock_tables = 0
Com_optimize = 0
Com_purge = 0
Com_rename_table = 0
Com_repair = 0
Com_replace = 0
Com_replace_select = 0
Com_reset = 0
Com_restore_table = 0
Com_revoke = 0
Com_rollback = 0
Com_select = 3
Com_set_option = 0
Com_show_binlog_events = 0
Com_show_binlogs = 0
Com_show_create = 0
Com_show_databases = 2
Com_show_fields = 0
Com_show_grants = 0
Com_show_keys = 0
Com_show_logs = 0
Com_show_master_status = 0
Com_show_new_master = 0
Com_show_open_tables = 0
Com_show_processlist = 3
Com_show_slave_hosts = 0
Com_show_slave_status = 0
Com_show_status = 7
Com_show_innodb_status = 0
Com_show_tables = 12
Com_show_variables = 3
Com_slave_start = 0
Com_slave_stop = 0
Com_truncate = 0
Com_unlock_tables = 0
Com_update = 0
Connections = 7
Created_tmp_disk_tables = 0
Created_tmp_tables = 0
Created_tmp_files = 0
Delayed_insert_threads = 0
Delayed_writes = 0
Delayed_errors = 0
Flush_commands = 1
Handler_commit = 0
Handler_delete = 0
Handler_read_first = 2
Handler_read_key = 2
Handler_read_next = 1929
Handler_read_prev = 0
Handler_read_rnd = 0
Handler_read_rnd_next = 10
Handler_rollback = 1
Handler_update = 0
Handler_write = 0
Key_blocks_used = 0
Key_read_requests = 0
Key_reads = 0
Key_write_requests = 0
Key_writes = 0
Max_used_connections = 3
Not_flushed_key_blocks = 0
Not_flushed_delayed_rows = 0
Open_tables = 52
Open_files = 4
Open_streams = 0
Opened_tables = 58
Questions = 46
Qcache_queries_in_cache = 0
Qcache_inserts = 0
Qcache_hits = 0
Qcache_not_cached = 3
Qcache_free_memory = 2088520
Qcache_free_blocks = 1
Qcache_total_blocks = 1
Rpl_status = NULL
Select_full_join = 0
Select_full_range_join = 0
Select_range = 0
Select_range_check = 0
Select_scan = 1
Slave_open_temp_tables = 0
Slave_running = OFF
Slow_launch_threads = 0
Slow_queries = 0
Sort_merge_passes = 0
Sort_range = 0
Sort_rows = 0
Sort_scan = 0
Table_locks_immediate = 110
Table_locks_waited = 0
Threads_cached = 0
Threads_created = 6
Threads_connected = 4
Threads_running = 1
Uptime = 436
After doing this, we decided to create a new table like in your example with
the same records. Then, we execute the query:
Select Cod,Nom,Nif,Nombre_comercial from clientes Where (Nom =
'a1000' And Cod>'061642') Or Nom >'a1000' Order by Nom ASC, Cod ASC Limit 50;
and it only took 0.02 secs. that is considerably less time if we execute the query
with our table 'Clientes' which have only 3800 records.
We thought that the problem could be in the table field number (52), so we deleted all
the table fields except Cod,Nom,Nif,Nombre_Comercial fields and ran the query again.
This time, it only took 0.03 secs.!! After that, I could say that the problem could
be in the number of fields. Moreover, I don´t understand why it take so much time
with 52 fields if we only select 4 of them.
The last test we made was to change the table type to MyISAM and it went very fast
(0.02 secs.) with our table with all the fields.
The table definition is:
CREATE TABLE `clientes` (
`Cod` varchar(6) NOT NULL default '',
`Nom` varchar(40) NOT NULL default '',
`Nif` varchar(15) NOT NULL default '',
`Ctc` varchar(9) NOT NULL default '',
`Dir` varchar(35) NOT NULL default '',
`CoP` varchar(6) NOT NULL default '',
`Tel` varchar(18) NOT NULL default '',
`Fax` varchar(10) NOT NULL default '',
`Ven` char(3) NOT NULL default '',
`Rpr` char(3) NOT NULL default '',
`FoP` char(2) NOT NULL default '',
`Rec` char(1) NOT NULL default '',
`Ctd` char(1) NOT NULL default '',
`Cfa` char(1) NOT NULL default '',
`Dp1` char(2) NOT NULL default '',
`Dp2` char(2) NOT NULL default '',
`Cba` varchar(8) NOT NULL default '',
`Umv` date default NULL,
`Dct` char(2) NOT NULL default '',
`RNF` tinyint(4) unsigned NOT NULL default '0',
`Cta` varchar(10) NOT NULL default '',
`Iva` char(1) NOT NULL default '',
`Ful` date default NULL,
`Nts` text,
`DPP` float NOT NULL default '0',
`TTa` varchar(6) NOT NULL default '',
`Val` char(1) NOT NULL default '',
`Ret` float NOT NULL default '0',
`CuR` varchar(9) NOT NULL default '',
`EMA` varchar(50) NOT NULL default '',
`Nombre_comercial` varchar(255) NOT NULL default '',
`Ruta_Comercial` char(3) NOT NULL default '',
`Bloqueado` tinyint(4) unsigned NOT NULL default '0',
`Fecha_ultima_visita` date default NULL,
`Procedimiento` text,
`Portes` decimal(20,4) NOT NULL default '0.0000',
`Cliente_central` varchar(6) NOT NULL default '',
`Codigo_agencia` char(2) NOT NULL default '',
`Imprime_albaranes` tinyint(4) unsigned NOT NULL default '0',
`Tarifa_especial` varchar(6) NOT NULL default '',
`Procedimiento_Facturacion` varchar(255) NOT NULL default '',
`Interno` tinyint(4) unsigned NOT NULL default '0',
`Compensar_Facturas` tinyint(4) unsigned NOT NULL default '0',
`Generar_Efectos` tinyint(4) unsigned NOT NULL default '0',
`Condiciones_Facturacion` tinyint(4) unsigned NOT NULL default '0',
`Copias_Albaran` tinyint(4) unsigned NOT NULL default '0',
`Forma_Pago_Minima` tinyint(4) unsigned NOT NULL default '0',
`NotasBloqueo` varchar(255) NOT NULL default '',
`TipoAsignado` char(1) NOT NULL default '',
`TipoCalculado` char(1) NOT NULL default '',
`Act` varchar(255) NOT NULL default '',
`ExentoOfertas` tinyint(4) unsigned NOT NULL default '0',
PRIMARY KEY (`Cod`),
UNIQUE KEY `Por_Nombre_Comercial` (`Nombre_comercial`,`Cod`),
UNIQUE KEY `DNI` (`Nif`,`Cod`),
UNIQUE KEY `Nombre` (`Nom`,`Cod`),
KEY `Ruta_comercial` (`Ruta_Comercial`)
) TYPE=InnoDB;
Thanks in advance,
Lourdes
How-To-Repeat:
Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'GARCIA MANCILLA,S.L' And Cod>'061642')
Or Nom>'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50;
Fix:
-
Synopsis:optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb
Submitter-Id:
Originator: Rafa
Organization: Pecomark
MySQL support: none
Severity: non-critical
Priority: medium
Category: mysqld-max-nt
Class: sw-bug
Release: mysqld 4.0.3 beta(InnoDB)
Exectutable: mysqld-max-nt
Environment: Pentium III-MMX, 500 MHZ, 540 MB
System: Windows 2000
Compiler: -
Architecture: i
____________________________________________________________ ______
The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp
Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/
------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php