Optimising (My)SQL Query

Posted by Simon on Stack Overflow See other posts from Stack Overflow or by Simon
Published on 2010-04-03T18:16:01Z Indexed on 2010/04/03 18:23 UTC
Read the original article Hit count: 256

Filed under:

zend-db

|

zend-framework

|

mysql

|

mysql-query

I usually use ORM instead of SQL and I am slightly out of touch on the different JOINs...

SELECT `order_invoice`.*, `client`.*, `order_product`.*, SUM(product.cost) as net 
FROM `order_invoice` 
LEFT JOIN `client` ON order_invoice.client_id = client.client_id 
LEFT JOIN `order_product` ON order_invoice.invoice_id = order_product.invoice_id 
LEFT JOIN `product` ON order_product.product_id = product.product_id 
WHERE (order_invoice.date_created >= '2009-01-01') AND (order_invoice.date_created <= '2009-02-01') 
GROUP BY `order_invoice`.`invoice_id`

The tables/ columns are logically names... it's an shop type application... the query works... it's just very very slow...

I use the Zend Framework and would usually use Zend_Db_Table_Row::find(Parent|Dependent)Row(set)('TableClass') but I have to make lots of joins and I thought it'll improve performance by doing it all in one query instead of hundreds...

Can I improve the above query by using more appropriate JOINs or a different implementation? Many thanks.

© Stack Overflow or respective owner

Related posts about zend-db

ZF2 Zend\Db Insert/Update Using Mysql Expression (Zend\Db\Sql\Expression?)

as seen on Stack Overflow - Search for 'Stack Overflow'
Is there any way to include MySQL expressions like NOW() in the current build of ZF2 (2.0.0beta4) through Zend\Db and/or TableGateway insert()/update() statements? Here is a related post on the mailing list, though it hasn't been answered: http://zend-framework-community.634137.n4.nabble.com/Zend-Db-Expr-and-Transactions-in-ZF2-Db-td4472944… >>> More
Zend DB returning NULL value

as seen on Stack Overflow - Search for 'Stack Overflow'
I have the following query that extends from Zend_DB_Table_Abstract $select = $this->select() ->from('expense_details', array('SUM(expense_details_amount) AS total')) ->where('YEAR(expense_details_date) = ?', '2010') ->where('MONTH(expense_details_date)… >>> More
Zend DB MYSQL Wrapper

as seen on Stack Overflow - Search for 'Stack Overflow'
All, I have a PHP application written in Zend Framework with MVC style. I plan to use Zend_DB to connect to the MySQL database and process queries. I am looking for a wrapper class which makes it easy to use Zend_DB class. This wrapper class will have a constructor that connects to the Mysql db using… >>> More
Deleting Multiple Rows with Zend DB Table Problem

as seen on Stack Overflow - Search for 'Stack Overflow'
I have this data in my db Col1 Col2 DA Data1 DA Data2 DA Data3 DA Data4 DA Data5 I would like to delete all the values WHERE col1 = DA using my Zend DB Table adapter. The code below does not seem to work for multiple rows public function delete($key) { … >>> More
Selecting arbitrary strings with Zend DB Select?

as seen on Stack Overflow - Search for 'Stack Overflow'
I am using the fluent interface to create a Zend DB Select object/query. As part of the query, I would like to select an arbitrary string, like "SELECT 'foo' AS 'type' FROM ...". foo is not a column, it's just a string literal. When I select an arbitrary number, the query works as expected. When… >>> More

Related posts about zend-framework

Zend framework Zend_Controller_Action_HelperBroker::addPath does not work

as seen on Stack Overflow - Search for 'Stack Overflow'
I get this message regardless of the path used and even if I place the class file in the default directory location. Zend_Controller_Action_HelperBroker::addPath('./Plugins/Helpers','Helper'); Sorry, An error has occured: Application Error:exception 'Zend_Loader_PluginLoader_Exception' with message… >>> More
Problem setting up Zend Framework to run with Netbeans

as seen on Server Fault - Search for 'Server Fault'
I am trying to get Zend Framework working with netbeans, but every time there is the error "php.exe"' is not recognized as an internal or external command, operable program or batch file. I am using WAMP server 2.0 it is installed in e:\wamp My Zend framework is inside e:\wamp\www\ZendFramework-1… >>> More
How do I use PHPUnit with Zend Framework?

as seen on Stack Overflow - Search for 'Stack Overflow'
I would like to know how to write PHPUnit tests with Zend_Test and in general with PHP. >>> More
Code to plug into a Zend Framework project

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello everyone, Im currently working on a website in the Zend Framework and finding it very useful indeed. I want both a blog and a forum in this website and wondered if there are any open-source projects of this nature that I would be able to simply copy and paste into my modular project. I was… >>> More
Displaying locale percentage number with Zend Framework

as seen on Stack Overflow - Search for 'Stack Overflow'
Does anyone know how to display a percentage format based on the locale using the zend framework? I've tried this below and it doesn't work for some reason: $xx = Zend_Locale::getTranslation(null, 'PercentNumber'); // This outputs: "#,##0%" for locale 'en_us' $percentage = Zend_Locale_Format::getNumber(133… >>> More