RE: Query Problem
Ok, Thanks for all Roger. -Mensaje original- De: Roger Baklund [mailto:[EMAIL PROTECTED] Enviado el: viernes, 22 de abril de 2005 4:06 Para: Dto. Sistemas de Unitel CC: mysql@lists.mysql.com Asunto: Re: Query Problem Dto. Sistemas de Unitel wrote: You don't understand me, I refer that if in a table I use productos.prod_id and in other table indexes.id if I can use this two fields like the same index, because when I named the two equal, the index start to work fine. There should be no problem with joining two tables based on columns with different names. productos.prod_id=indexes.id should work. Both columns could be indexed, (in two separate indexes, of course, as they are in two separate tables), but only one index will be used, depending on the join order. It does not matter if you write productos.prod_id=indexes.id or indexes.id=productos.prod_id, and it does not matter if you write FROM productos,indexes or FROM indexes,productos (unless STRAIGHT_JOIN is used). In this case (se earlier posts in this thread) the table named indexes should be read first, then productos. That means an index on productos.prod_id will be used, if available. The name of the column in the productos table or the name of the related column in the indexes table does not matter. The = character in the ON clause or in the WHERE clause dictates which columns are related, not the name of the columns. I don't know why your index did not work at first. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Problem
Hi Roger, You are ok, there was an index problem in one table, they name of the rows wasn't equal and MySQL didn't recognize they as the same index. I have changed the row name and now is working fine, but I have a little question, How can I use indexes with different names in it's tables? Thanks for your help, you have been very helpful for me. Roberto -Mensaje original- De: Roger Baklund [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 20 de abril de 2005 18:30 Para: mysql@lists.mysql.com CC: Dto. Sistemas de Unitel Asunto: Re: Query Problem Dto. Sistemas de Unitel wrote: Hi Roger, That was just I need. The order isnt like you say: ++-+---++---+-+- ++---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---++---+-+- ++---+-+ | 1 | SIMPLE | t1| const | PRIMARY,uniq | uniq| 250 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t2| const | PRIMARY,uniq | uniq| 250 | const | 1 | | | 1 | SIMPLE | productos | ALL| PRIMARY,dupli | [NULL] | [NULL] | [NULL] | 16153 | | | 1 | SIMPLE | i2| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | | 1 | SIMPLE | i1| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | ++-+---++---+-+- ++---+-+ Productos is executed in the middle of the other two ones, the time of the query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a little more should be perfect. It seems as there is no index on productos.prod_id? -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Problem
You don't understand me, I refer that if in a table I use productos.prod_id and in other table indexes.id if I can use this two fields like the same index, because when I named the two equal, the index start to work fine. Thanks -Mensaje original- De: Roger Baklund [mailto:[EMAIL PROTECTED] Enviado el: jueves, 21 de abril de 2005 18:17 Para: mysql@lists.mysql.com CC: Dto. Sistemas de Unitel Asunto: Re: Query Problem Dto. Sistemas de Unitel wrote: Hi Roger, You are ok, there was an index problem in one table, they name of the rows wasn't equal and MySQL didn't recognize they as the same index. I have changed the row name and now is working fine, but I have a little question, How can I use indexes with different names in it's tables? I'm not sure if I understand the question, but in general the names of the columns and indexes are not case sensitive, which in your case means prod_id and PROD_ID should be treated equal. Table names and database names are different, it depends on the filesystem the server is using: URL: http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem
Hi, Im trying to make a querythat need to search throught a table and have to search different terms, and SUM their Rank (is for a small search engine I have to design), this is the table: Table Indexes ID bigint ID_Termino -bigint Rank double Table Terminos ID_Termino bigint Primary key Termino Varchar(50) Table productos ID bigint prod_descripcion varchar(255) I have another engine that search throught text and apply a Rank to each word and store it on table indexes and if this word doesnt exist it store it in table terminus and the id of this word is related in indexes table. Indexes is related with another table called productos that is the one that is analyzed by my engine. When any client ask to me for search computer intel y search with the next query, but the results arent 100% good because I have to use a clause OR in termino, but I want to use there something like an and (something like indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=computer and termino=intel I know that is impossible, but maybe there is another way to make that). SELECT STRAIGHT_JOIN terminos.id_termino,productos.prod_descripcion,indexes.id,terminos.termino,s um(indexes.rank) as ordenate,productos.prod_unitel_id FROM terminos,indexes,productos where indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=computer or indexes.id=productos.prod_id and indexes.id_termino=terminos.id_termino and termino=intel group by (indexes.id) order by ordenate desc Thanks for all and best regards, Roberto
Re: Query Problem
Hi Roger, That was just I need. The order isnt like you say: ++-+---++---+-+- ++---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---++---+-+- ++---+-+ | 1 | SIMPLE | t1| const | PRIMARY,uniq | uniq| 250 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t2| const | PRIMARY,uniq | uniq| 250 | const | 1 | | | 1 | SIMPLE | productos | ALL| PRIMARY,dupli | [NULL] | [NULL] | [NULL] | 16153 | | | 1 | SIMPLE | i2| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | | 1 | SIMPLE | i1| eq_ref | PRIMARY,uniq | PRIMARY | 16 | unitel.productos.PROD_ID,const | 1 | Using where | ++-+---++---+-+- ++---+-+ Productos is executed in the middle of the other two ones, the time of the query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a little more should be perfect. Thanks another time, your help is very good for me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Query-Cache Reset
Hi, Im the administrador of a MySQL Server, it have lots of queries, and now its getting high load of processor, I try to increase the size of MySQL Query Cache, but if I put more than 128 Mb the cache its reseting all the time and the performance is worst. The server have 1,2 Mb of RAM and I can assign 256 or more MB to Query Cache for reduce processor load. My actual my.cnf config is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 max_connections = 400 key_buffer = 64M myisam_sort_buffer_size = 32M join_buffer_size = 1M read_buffer_size = 4M sort_buffer_size = 4M table_cache = 1024 thread_cache_size = 128 wait_timeout = 1800 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 128M query_cache_type = 1 And my show status info: mysql show status; +--++ | Variable_name| Value | +--++ | Aborted_clients | 8378 | | Aborted_connects | 307| | Binlog_cache_disk_use| 0 | | Binlog_cache_use | 0 | | Bytes_received | 114459945 | | Bytes_sent | 1355402553 | | Com_admin_commands | 49554 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin| 0 | | Com_change_db| 178087 | | Com_change_master| 0 | | Com_check| 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db| 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql | 0 | | Com_delete | 49499 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function| 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user| 0 | | Com_execute_sql | 0 | | Com_flush| 0 | | Com_grant| 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 49288 | | Com_insert_select| 0 | | Com_kill | 0 | | Com_load | 3 | | Com_load_master_data | 0 | | Com_load_master_table| 0 | | Com_lock_tables | 96 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge| 0 | | Com_purge_before_date| 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 61263 | | Com_replace_select | 0 | | Com_reset| 0 | | Com_restore_table| 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint| 0 | | Com_select | 250306 | | Com_set_option | 2560 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets| 0 | | Com_show_collations | 0 | | Com_show_column_types| 0 | | Com_show_create_db | 102| | Com_show_create_table| 2550 | | Com_show_databases | 10 | | Com_show_errors | 0 | | Com_show_fields | 3006 | | Com_show_grants | 0 | | Com_show_innodb_status | 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_privileges | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status| 0 | | Com_show_status | 10 | | Com_show_storage_engines | 0 | | Com_show_tables | 2664 | | Com_show_variables | 0 | | Com_show_warnings| 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables| 102| | Com_update | 71170 | | Com_update_multi | 0 | | Connections | 480457 | | Created_tmp_disk_tables | 49082 | | Created_tmp_files
