Hi,
don't have much time at the moment. But would like to give you some hints for going on
If you have queries where you can't find out what to do send me a PM. I will also try to follow this. But I have got lots of work and three sick kids, so not much time.
If you would like to find out which are the slow queries, activate this (remove the #) in /etc/mysql/my.cnf :
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 10
#log-queries-not-using-indexes
Note : "long_query_time" means the time that a query should run before it gets into the logfile. A good start is probably 10 seconds, but for some things you can also go lower
And do a
/etc/init.d/mysql restart
After that use try to show the video datagrid and take a look in the "/var/log/mysql/mysql-slow.log" logfile.
You can the take the query from there an execute it on it's own.
I di not have time to look at the UpdateMedia query after the optimizing, but before it use 215 millions rows to get it done. That will prbably clear the query_cache
You should not run you system permanently with this turned on though. The log will grow quite big.
-------
To find out what is actually done use this :
EXPLAIN EXTENDED <query>;
SHOW WARNINGS;
In the Update Media query that looked like this. As you can see in "before" it uses temp tables (using temporary) and "after" it is using indexes. That is a lot faster
So if a query uses temp tables , we can probably improve performance with indexes.
BEFORE :
+----+--------------+----------------+--------+-----------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+----------------+--------+-----------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 35553 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | File | ALL | Path,Filename | NULL | NULL | NULL | 33257 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | Bookmark | ALL | NULL | NULL | NULL | NULL | 170 | |
| 3 | UNION | File | ALL | Path,Filename | NULL | NULL | NULL | 33257 | Using where; Using temporary; Using filesort |
| 3 | UNION | File_Attribute | ref | PRIMARY,FK_File | FK_File | 4 | pluto_media.File.PK_File | 2 | Using index |
| 3 | UNION | Attribute | eq_ref | PRIMARY | PRIMARY | 4 | pluto_media.File_Attribute.FK_Attribute | 1 | |
| 4 | UNION | File | ALL | Path,Filename | NULL | NULL | NULL | 33257 | Using where; Using temporary; Using filesort |
| 4 | UNION | LongAttribute | ALL | NULL | NULL | NULL | NULL | 2424 | |
| 5 | UNION | File | ALL | Path,Filename | NULL | NULL | NULL | 33257 | Using where; Using temporary; Using filesort |
| 5 | UNION | Picture_File | ref | FK_File | FK_File | 4 | pluto_media.File.PK_File | 1 | |
| NULL | UNION RESULT | <union2,3,4,5> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+----------------+--------+-----------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
11 rows in set, 1 warning (1 min 10.85 sec)
AFTER :
+----+--------------+----------------+--------+-----------------+------------+---------+-----------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+----------------+--------+-----------------+------------+---------+-----------------------------------------+-------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 60213 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | File | index | Path,Filename | FatGroupBy | 546 | NULL | 32007 | Using where |
| 2 | DERIVED | Bookmark | ref | FK_File | FK_File | 5 | pluto_media.File.PK_File | 1 | |
| 3 | UNION | File | index | Path,Filename | FatGroupBy | 546 | NULL | 32007 | Using where |
| 3 | UNION | File_Attribute | ref | PRIMARY,FK_File | FK_File | 4 | pluto_media.File.PK_File | 2 | Using index |
| 3 | UNION | Attribute | eq_ref | PRIMARY | PRIMARY | 4 | pluto_media.File_Attribute.FK_Attribute | 1 | |
| 4 | UNION | File | index | Path,Filename | FatGroupBy | 546 | NULL | 32007 | Using where |
| 4 | UNION | LongAttribute | ref | FK_File | FK_File | 5 | pluto_media.File.PK_File | 1 | |
| 5 | UNION | File | index | Path,Filename | FatGroupBy | 546 | NULL | 32007 | Using where |
| 5 | UNION | Picture_File | ref | FK_File | FK_File | 4 | pluto_media.File.PK_File | 1 | |
| NULL | UNION RESULT | <union2,3,4,5> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+----------------+--------+-----------------+------------+---------+-----------------------------------------+-------+----------------------------------------------+
11 rows in set, 1 warning (7.92 sec)
--------
Some more things to try.
mysql>set profiling=1
mysql> Do things ( run qurey)
mysql>show profiles;
and get some more details for what we did :
mysql>show profile [all|block io|context switches|ipc|page faults|source|swaps] for query X;
Documentation:
http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html--------
Last, iIf we use temp tables, we could try getting the to fit into memory - but better would be not to need that :
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_sizehttp://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_sizeThat did not help anything on the UpdateMedia query - i tried it.
Greetings
Viking
