I am having an issue with performance on one of my tables.. the table `file` has 30,000 rows in it, I need to find the files that have a certain type so I came up with this query:
SELECT COUNT(`file`.file_id) as 'PDF Count' FROM `order`
JOIN `pkg_order_agg` ON (`order`.order_id = `pkg_order_agg`.order_id AND `order`.order_number = 'SEA8002')
JOIN `file_pkg_agg` ON `pkg_order_agg`.pkg_id = `file_pkg_agg`.pkg_id
JOIN `file` ON (`file_pkg_agg`.file_id = `file`.file_id AND `file`.type = 'application/pdf')
The query gets executed multiple times while generating a report for the site, so its accessing and sifting through 30,000 rows multiple times and is wayyy to slow. So I decided to use a temporary table - I create it before looping and access its rows.
CREATE TEMPORARY TABLE `artfiles` SELECT file_id FROM file WHERE type = 'application/pdf';
SELECT COUNT(`artfiles`.file_id) as 'PDF Count' FROM `order`
JOIN `pkg_order_agg` ON (`order`.order_id = `pkg_order_agg`.order_id AND `order`.order_number = 'SEA8002')
JOIN `file_pkg_agg` ON `pkg_order_agg`.pkg_id = `file_pkg_agg`.pkg_id
JOIN `artfiles` ON (`file_pkg_agg`.file_id = `artfiles`.file_id);
Well come to find out the temp table has 29,000 rows (apparently almost all of the files are of this type) so performance is not improved to any amount that helps..
Any one have any suggestions on how I can improve this..?
I think KEY partitioning will be useful in this case.
Also, I tend to use *char typed fields for view purposes only (i.e. they are found only in the SELECT clause) and avoid using them in conditions (where, join on, etc.) and order/group clauses.
There are 10 types of people in this world, those who understand binary and those who don't
I will check the MySQL config files - I always find that the default values for almost everything are set to too small values - like it's supposed to run on 486DX with 2MB of RAM
The partioning thing I mentioned is to show to msurabbott how to implement in the right way what he is trying to do with temporary tables.
You are right - 40K of rows is nothing
PS: Make sure that all ID fields are numeric and not *char.
There are 10 types of people in this world, those who understand binary and those who don't
I think a lot of the processing time of this task is the "writing" of the xml.. but if I have say 4 orders.. this takes 5-10 minutes easy, and if I comment out the two queries (which they are in the posted code) it runs in about 5-10 seconds