This stuff is beyond me unfortunately so... DP SQL guru's to the rescue! I have a seemingly simple query which takes progrssively lionger and longer to execute; the bigger the database growse the slower it gets. And unfortunately, it's on the most accessed admin page so it gets really annoying having ot wait for it for 40+ seconds. This is the query: select distinct o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on o.orders_id = ot.orders_id left join orders_status s on o.orders_status = s.orders_status_id left join orders_products op on o.orders_id = op.orders_id where (s.orders_status_id < 10 OR (op.products_quantity > (op.products_quantity_processed + op.products_quantity_cancelled))) and s.language_id = '1' and ot.class = 'ot_total' order by o.orders_id desc Code (markup): I cut bits out to see which part is the culprit but none have made a significant impact. I tried moving some stuff to the right so it gets evaluated first in the hope it would cut the size of data it's copying to memory or whatever but that didn't help either. Indexes etc: The parts in the WHERE clause that have indexes or otherwise special attributes are orders_status_id = Primary and language_id = Primary (they're in the same table). The rest are regular fields. Out of the tables involved, orders_total has 18K rows and is the largest dataset of them all. Would that be enough info to see where the bottleneck is? Any tips & ideas? Thanks.
With some digging around and some studying on how indexes etc. work I got to know the EXPLAIN command. At first, I thought the mathematics were slowing it down so I changed my code, the table and the query: SELECT DISTINCT o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text AS order_total FROM orders o LEFT JOIN orders_total ot ON o.orders_id = ot.orders_id LEFT JOIN orders_status s ON o.orders_status = s.orders_status_id LEFT JOIN orders_products op ON o.orders_id = op.orders_id WHERE op.done = '1' AND s.language_id = '1' AND ot.class = 'ot_total' ORDER BY o.orders_id DESC LIMIT 0 , 20 Code (markup): Note the done = '1' instead of the quantity calculations. But... It hasn't made any difference. Here's the EXPLAIN results, I hope it pastes well enough: table type possible_keys key key_len ref rows Extra o ALL NULL NULL NULL NULL 4423 Using temporary; Using filesort ot ref idx_orders_total_orders_id idx_orders_total_orders_id 4 o.orders_id 12 where used s ref PRIMARY PRIMARY 4 o.orders_status 1 where used op ALL NULL NULL NULL NULL 5201 where used; Distinct Code (markup): The "Using temporary" and "Using filesort" on the "o" table are known to slow stuff down but not so in this case. When I take done = '1' out of the where clause, it executes in under half a second again. So the issue is the "op" table. It has no Keys to use and MySQL has to evaluate all 5201 rows (and that's just my test dataset, the real one is 5 times larger at least). The 'distinct' part isn't it either. So knowing which table and which part of the where clause it is, here's the table structure: -- -- Table structure for table `orders_products` -- CREATE TABLE `orders_products` ( `orders_products_id` int(11) NOT NULL auto_increment, `orders_id` int(11) NOT NULL default '0', `products_id` int(11) NOT NULL default '0', `products_code` varchar(12) default NULL, `products_name` varchar(64) NOT NULL default '', `products_price` decimal(15,4) NOT NULL default '0.0000', `final_price` decimal(15,4) NOT NULL default '0.0000', `products_tax` decimal(7,4) NOT NULL default '0.0000', `products_quantity` int(2) NOT NULL default '0', `products_quantity_processed` int(2) NOT NULL default '0', `products_quantity_cancelled` int(2) NOT NULL default '0', `done` tinyint(1) NOT NULL default '0', PRIMARY KEY (`orders_products_id`) ) TYPE=MyISAM AUTO_INCREMENT=5202 ; Code (markup): An index on done makes no difference, it's just a boolean 1 or 0 and indexing it has not sped up a thing. So, why is it that adding that WHERE done = '1' adds 50-116 seconds and what can I change to speed it up?
My knowledge on this is limited to the fact that you should put the most likely where clauses to the left so it finds done=1 and then evaluates the next where clause from the results of the first one rather than the entire table. I think.
That's contrary to what I've read. I read that MySQL executes queries form right to left so if you put stuff that eliminates a lot of data more to the right then it speeds things up. But I've tried all that anyway and it doesn't seem to be the issue here. The problem is only the inclusion of that op.done = '1' - as soon as I add something from that (large-ish) table to the WHERE, it all slows down to unacceptable speeds. My knowledge of this isn't enough to know where to add an index, key or whatever to solve this.
98% of them, the other 2% = 0. Haha, just realized I hould use done='0' for this code. So, it should retrieve about 20-50 done='0's. BTW swapping 1 to 0 has no effect on the speed of this query. It doesn't change the EXPLAIN results, it still looks through all records terribly slowly.
No, still 90 seconds. It doesn't matter what value is on the right hand side of the = sign. op.done = 1 or op.done = 0 means in either case, it still has to look through all the records and then fetch the matching one. How many of them are 1 or 0 doesn't make a difference unless there's an index or key on them - that's how I understand it. But an index won't make a difference (probably too many the same value - it's like indexing a library where all the books have the same name) and a Key won;t work because I have an Auto Increment column. At least that's what phpMyAdmin is complaining about. If I remove the op.done = '0' It's down to a split second again. This is doing my head in.
ok ok, what rows in what tables are indexed? whats the latest query u using? put a index on o.orders_id .orders_status_id o.orders_status what are table definitions? what types are these tables? INNODB is good for loads of inserts, updates, deletes but has no fulltext MYiSAM is fast at selects but no transactions 90seconds! something seriously wrong indexing PROPERLY will make a difference, i brougt down a 30second query before to 0.3 secs also invest in Navicat or get trial at least its an excellent program that wipes floor with phpmyadmin
I completely rewrote the scripts that interacts with these tables so I solved it though it's still a mystery and for the sake of my own education I will find out how it could have been sped up. BTW those fields you pointed out weren't the cuplrit. As soon as I got done='0' from the op table out of the where clause, it went down to split second. Anyway, shame you weren't around a few days back Thanks anyway.
having too many indexes doesnt usually do any harm (well database will be bigger but better than having any indexes at all) i dont know structure of your tables but "done" seems like a flag you use right? so maybe have 2 tables unfinished orders, and finished orders waht i usually do is normalise to 3rd normal form, then if speed is required i create temporary denormalised tables, for example lets say i have tables: users threads subscriptions now to present a typical forum view ull have to join all 3, which i do but if the forum grows big and speed becomes more important than acurracy il create a third denormalised table (yes denormalising is allowed and might be frowned upon by some and it is practised, its a trade off between data integrity and speed) that will contain all 3 of the above anyways thats my 2 cent, i use to love my database lecturers..
Does mySQL support cursors?? If so, all this processing can be done server side and then will return only the results that are needed.