Mike,
> Ronan,
> You haven't given us much to go on.
I´m sorry.
> Is this application running on a web server?
Yes, but in a diferent machine connecting via TCP/IP.
> 1) How many rows are your queries returning? (on average) and how long
does
> it take?
Hmmm... Most of the queries are limited in 10 rows.
> 2) How many connected users do you have?
mysql> show status;
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 64 |
| Aborted_connects | 2 |
| Bytes_received | 212069034 |
| Bytes_sent | 262377934 |
| Com_admin_commands | 29410 |
| Com_alter_table | 8 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 100 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 2 |
| Com_create_table | 11 |
| Com_delete | 1583 |
| 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 | 2563489 |
| 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_savepoint | 0 |
| Com_select | 138419 |
| Com_set_option | 58392 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 4 |
| Com_show_fields | 450 |
| Com_show_grants | 0 |
| Com_show_keys | 9 |
| 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 | 42 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 59 |
| Com_show_variables | 43 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 38089 |
| Connections | 61 |
| Created_tmp_disk_tables | 3572 |
| Created_tmp_tables | 26901 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 16 |
| Handler_delete | 141469 |
| Handler_read_first | 1138 |
| Handler_read_key | 58809799 |
| Handler_read_next | 46576628 |
| Handler_read_prev | 430340 |
| Handler_read_rnd | 413436 |
| Handler_read_rnd_next | 166792550 |
| Handler_rollback | 1 |
| Handler_update | 3691731 |
| Handler_write | 12902461 |
| Key_blocks_used | 93763 |
| Key_read_requests | 240598220 |
| Key_reads | 82544 |
| Key_write_requests | 21429673 |
| Key_writes | 16389083 |
| Max_used_connections | 36 |
| Not_flushed_key_blocks | 6 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 346 |
| Open_files | 375 |
| Open_streams | 0 |
| Opened_tables | 405 |
| Questions | 2902623 |
| Qcache_queries_in_cache | 22701 |
| Qcache_inserts | 100527 |
| Qcache_hits | 101913 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 37892 |
| Qcache_free_memory | 35086904 |
| Qcache_free_blocks | 2804 |
| Qcache_total_blocks | 48436 |
| Rpl_status | NULL |
| Select_full_join | 1339 |
| Select_full_range_join | 2 |
| Select_range | 7383 |
| Select_range_check | 0 |
| Select_scan | 5684 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 2897 |
| Sort_merge_passes | 0 |
| Sort_range | 6598 |
| Sort_rows | 423007 |
| Sort_scan | 24793 |
| Table_locks_immediate | 2759606 |
| Table_locks_waited | 26700 |
| Threads_cached | 6 |
| Threads_created | 37 |
| Threads_connected | 16 |
| Threads_running | 4 |
| Uptime | 14876 |
+--------------------------+-----------+
132 rows in set (0.00 sec)
> 3) Are these queries using multi-table joins?
Yes.
> 4) Can you provide us with the table structure and query for the slow
queries?
Yes.
Here it is a slowly query, it take about 15 seconds:
SELECT f.dtrelease AS fdtrelease, f.cod AS fcod, f.title AS title,
vf.price AS vfprice, vf.cod AS vfcod
FROM film AS f, rent_film AS r, film_format AS ff, film_sale AS vf
WHERE r.codrent = 1123
AND ff.codfil = f.cod
AND r.film_format = ff.cod
AND vf.codrf = l.cod
AND ff.media = 2
GROUP BY f.cod
ORDER BY fdtrelease desc
LIMIT 0,4
Another one:
SELECT f.cod AS fcod, f.title AS title
FROM film AS f, rent_film AS r, film_format AS ff
WHERE r.codloc = 1123
AND ff.codfil = f.cod
AND r.film_format = ff.cod
AND f.type <> 21
AND ( ( TO_DAYS( CURDATE() ) - TO_DAYS( f.dtrelease ) <= 180
AND TO_DAYS(CURDATE()) - TO_DAYS(f.dtrelease) >= 0 )
OR ( r.release = 1 AND f.dtrelease <= CURDATE() ) )
GROUP BY f.cod
ORDER BY f.dtrelease DESC
LIMIT 0,5
Table film:
=======
Name: film
Type: MyISAM
Row format: Dynamic
Rows: 20514
Avg_row_lenght: 386
Data_lenght: 7921984
Max_data_lenght: 4294967295
Index_lenght: 9746432
Data_free: 0
Auto_increment: 21291
mysql> show columns from film;
+----------+----------------------+------+-----+------------+---------------
-+
| Field | Type | Null | Key | Default | Extra
|
+----------+----------------------+------+-----+------------+---------------
-+
| cod | int(11) unsigned | | PRI | NULL | auto_increment
|
| title | varchar(250) | | MUL | |
|
| titorig | varchar(250) | | MUL | |
|
| year | smallint(6) unsigned | YES | | NULL |
|
| time | smallint(6) unsigned | YES | | NULL | |
| sinopse | longtext | YES | MUL | NULL |
|
| type | int(11) unsigned | | MUL | 0 | |
| origem | int(11) unsigned | | MUL | 0 |
|
| coddistr | int(11) unsigned | | MUL | 0 |
|
| dtrelease | date | | MUL | 0000-00-00 |
|
| trailler | char(1) | YES | MUL | NULL |
|
| site | varchar(200) | YES | | NULL |
|
+----------+----------------------+------+-----+------------+---------------
-+
12 rows in set (0.01 sec)
Indexes (9):
------------
1. cod
2. type
3. origem
4. coddistr
5. dtrelease
6. trailler
7. title
8. titorig
9. sinopse
Table rent_film:
===========
Name: rent_film
Type: MyISAM
Row format: Fixed
Rows: 615059
Avg_row_lenght: 21
Data_lenght: 36820623
Max_data_lenght: 90194313215
Index_lenght: 84508672
Data_free: 2904384
Auto_increment: 2581442
mysql> show columns from rent_film;
+---------------+----------------------+------+-----+---------+-------------
---+
| Field | Type | Null | Key | Default | Extra
|
+---------------+----------------------+------+-----+---------+-------------
---+
| cod | int(11) unsigned | | PRI | NULL |
auto_increment |
| codrent | int(11) unsigned | | MUL | 0 |
|
| film_format | int(11) unsigned | | MUL | 0 |
|
| category | int(11) unsigned | | MUL | 0 |
|
| qtd | smallint(5) unsigned | | | 0 |
|
| release | tinyint(3) unsigned | | MUL | 0 |
|
| deploied | tinyint(3) unsigned | | MUL | 1 |
|
+---------------+----------------------+------+-----+---------+-------------
---+
7 rows in set (0.02 sec)
Indexes (6):
------------
1. cod
2. film_format
3. release
4. deploied
5. category
6. codrent, film_format
Table film_format:
===========
Name: film_format
Type: MyISAM
Row format: Dynamic
Rows: 45315
Avg_row_lenght: 41
Data_lenght: 1862868
Max_data_lenght: 4294967295
Index_lenght: 3154944
Data_free: 0
Auto_increment: 45854
mysql> show columns from film_format;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| cod | int(11) unsigned | | PRI | NULL | auto_increment |
| codfil | int(11) unsigned | | MUL | 0 | |
| media | int(11) unsigned | | MUL | 0 | |
| format | int(11) unsigned | | MUL | 0 | |
| language | int(11) unsigned | YES | MUL | 0 | |
| sbutitle | int(11) unsigned | YES | MUL | 0 | |
| audio_type | int(11) unsigned | YES | MUL | 0 | |
| area | char(1) | YES | | NULL | |
| inform | longtext | YES | | NULL | |
| dtrelease | date | YES | MUL | NULL | |
+------------+------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)
Indexes (8):
------------
1. cod
2. codfil
3. media
4. format
5. language
6. subtitle
7. audio_type
8. dtrelease
Table film_sale:
===========
Name: film_sale
Type: MyISAM
Row format: Fixed
Rows: 5908
Avg_row_lenght: 35
Data_lenght: 244755
Max_data_lenght: 150323855359
Index_lenght: 216064
Data_free: 37975
Auto_increment: 9059
mysql> show columns from film_sale;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| cod | int(11) unsigned | | PRI | NULL | auto_increment |
| codrent | int(11) unsigned | | MUL | 0 | |
| codrf | int(11) unsigned | | MUL | 0 | |
| price | decimal(20,2) | | | 0.00 | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
Indexes (3):
------------
1. cod
2. codrent
3. codrf
> 5) Can you provide us with a "Show Status"
Yes. Right above
6) Are you using MyISAM or InnoDb tables?
MyISAM
> The more information you can provide, the more accurate the answer will
be.
> The MySQL manual has plenty of articles on optimization:
> http://www.mysql.com/doc/en/MySQL_Optimisation.html
Thank you Mike, I´ve already read it, but my main problem is
it´s a system on production and I cant change the whole site struct
quickly... :-/
I need a temporary solution at first.
Thank you very much,
Ronan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
