MySQL Queries and learning a little more…

Once in a while I actually get to remember enough to help someone - with something I’m actually interested in - not often, but just sometimes. πŸ™‚ Generally I do as much learning as helping along the way.

Someone asked about a slow MySQL query on #sage-au tonight. They had been asked to look into a small e-commerce site that’s been having issues for a few years, slow page loads and the like.

It’s always fun working blind (no access to schema or the server), but if there’s anything K-T has taught me it’s that nothing is impossible to solve by asking questions.

The thing with SQL queries is that the system can do weird and wonderful things, especially when you’re doing subqueries and the like.

The query was as follows:

DELETE FROM vendor_shipping
WHERE vid NOT IN
( SELECT distinct(vendorid) FROM products WHERE pid IN
( SELECT pid FROM orders_content WHERE oid='663') ) and oid='663';

Fairly simple, delete something if it’s not in a list of vendor id’s which are related to product orders (ish?)

The first things suggested were to throw more hardware at it, which had most definitely been done - for a small online store it had 8GB of ram and a quad core processor of some sort.

Running the selects by themselves were fast (alone or in the sub-query state), and nothing major seemed out of the ordinary as shown by the output of explain.

For ~9k orders, there’s ~9k rows, that makes sense.

There were triggers which could cause other horrible things to happen with deletes. Profiling wasn’t turned on (yet) so that wasn’t slowing it down. No stored procedures either.

The fun part came when profiling was turned on. Long story short, it was chugging through the database thousands of times over:

SlowDB: 2910567 rows in set (12.87 sec) before i ctrl+c

Well, that’s no right! ~9k orders, ~170 vendors. 3 million rows queried? Silly MySQL. Ok, silly schema, but you get the idea.

Something I should probably have asked about were indexes. A lack of indexing’s a sure-fire way to slow down any queries, especially if there’s subselects and joins and all sorts of other horrible things going on.

It seems there wasn’t an index on the oid column in _vendorshipping. This meant that every time it filtered the query (with a where) for something in the _vendorshipping table it would have to scan through it row by row. The subselects only made this exponentially worse!

I’ll leave it to the code to show the difference - remember that 17 seconds was the normal time for this query to run.

We double checked our assumptions and confirmed that index creation load shouldn’t be too high. 167 rows, a single INT column, and it was already slow - should be OK. (This is something you have to think about when messing with FULLTEXT indexes.)

Here the index on the table is created:

mysql> create index oid_index on vendor_shipping (oid);  
Query OK, 167 rows affected (0.02 sec) 
Records: 167 Duplicates: 0 Warnings: 0

That was quick!

And now, to test running the DELETE command:

mysql> DELETE FROM vendor_shipping WHERE vid NOT IN(SELECT distinct(vendorid) 
FROM products 
WHERE pid 
IN (select pid from orders_content WHERE oid='663')) 
AND oid='663'; 
Query OK, 0 rows affected (0.00 sec)

Suffice to say, the issue was closed. πŸ™‚

The original chat log is below, mainly so I can remember how it went. Other people’s names have been changed, but the content’s the same. πŸ™‚

SlowDB: So I've got a slow SQL query which is out of my control to fix. Is there any tweaks to mysql to help these? 
otherguy: SlowDB: does more RAM count as a tweak? 
SlowDB: in this case yes.. 
SlowDB: i've bumped the values in mysql 
SlowDB: http://pastebin.com/A5kRw09B SlowDB: I've asked to check if the crappy shopping cart DB will support innodb as it is currently myisam 
SlowDB: this is the query that I've found to be slow 
SlowDB: DELETE FROM vendor_shipping WHERE vid NOT IN(SELECT distinct(vendorid) FROM products where pid in(select pid from orders_content WHERE oid='663')) and oid='663';
SlowDB: the nested SQLs all return quick. 
SlowDB: but run as a whole it takes 17secs 
yaleman: SlowDB: have you run mysqltuner? 
SlowDB: yes 
SlowDB: it's a shitty DB schema, so I know I am limited in what I can actually do to help 
yaleman: hrm, so how big are the returns from the nested queries? 
SlowDB: 1 field for each which return in 0.00sec 
yaleman: more cpu/ram in the box, if you can't change the query 
SlowDB: 8GB of ram, quad core. Think I'm stuck then hey.
yaleman: probably not a terrible idea, if you can rewrite the query move the oid check before the other? πŸ˜› 
yaleman: geez, how big is vendor_shipping 
SlowDB: 167 rows, not big 😐 
yaleman: and it's taking 17 seconds 
SlowDB: indeed - i can't understand it 
yaleman: explain? (as in run explain on the query) 
SlowDB: i've never done that before 
SlowDB: http://www.sql.org/sql-database/postgresql/manual/sql-explain.html ? 
yaleman: should be able to go into mysql, then just do "explain [your query]" 
yaleman: http://dev.mysql.com/doc/refman/5.0/en/explain.html
yaleman: 8gb, quad core, somehow it's taking 17 seconds to run across 170 rows... there's something seriously wrong there 
yaleman: hell, I process hundreds of thousands of rows in similarly horrible ways in less than that with much less resources 
yaleman: it's a delete, unless the indexes are horribly corrupt (in some truly f***ed way) it shouldn't matter on the row size 
yaleman: oh, check for on delete/on x procedures maybe? :S 
yaleman: but explain should tell you 
otherguy: are any of the other tables big? maybe a full table scan going on. its beyond my skillz tho. 
SlowDB: yes, product table has over 10K rows 
SlowDB: which is nothing still for a box of this size. 
SlowDB: It's russian software for joomla..... 
SlowDB: explain seems only for SELECT statements from the mysql page 
otherguy: are both 'oid' integers? 
SlowDB: yes
yaleman: do an explain on the selects, as the are to see if there's something odd (as in just strip the outer delete off) 
SlowDB: yer, just did it. 
SlowDB: nothing stands out
SlowDB: http://pastebin.com/HagpNGXX 
otherguy: any triggers, or auditing enabled? 
SlowDB: Unsure about triggers or auditing sorry. 
yaleman: do a "show triggers in [database]" 
SlowDB: empty 
yaleman: O_o 
SlowDB: mysql> show triggers in [database]; 
SlowDB: Empty set (0.00 sec) 
yaleman: http://dev.mysql.com/doc/refman/5.1/en/show-profile.html 
yaleman: try profiling the query 
SlowDB: ok 
SlowDB: wow, lots of output. 
yaleman: yeah, it'll do that 
SlowDB: still scrolling through show profile; 
yaleman: ah, I'd be reading that whole page before doing profiling πŸ™‚ 
SlowDB: i show profile 4, and it scrolls through a bunch of | Sending data | 0.000013 | 0.000000 | 0.000000 | 
SlowDB: 2910567 rows in set (12.87 sec) before i ctrl+c 
SlowDB: indexing it comes down to is my guess.... 
yaleman: show index? 
yaleman: if there's no index on vid that'll be less fun for it 
SlowDB: there is three: id, uid and status 
SlowDB: so you are correct. 
yaleman: create index on [table] oid using hash 
yaleman: actually ignore the using hash bit 
SlowDB: ok 
SlowDB: there is no hazard in doing that command? 
otherguy: good luck with i!. 
yaleman: "create index oid_index on [table] oid" 
yaleman: is what I can make out of my fuzzy memory of creating indexes and referring to [http://dev.mysql.com/doc/refman/5.0/en/create-index.html](http://dev.mysql.com/doc/refman/5.0/en/create-index.html) 
otherguy: do you have disk space and io headroom while it creates? 
yaleman: what version of mysql are you running? πŸ™‚ 
SlowDB: will, yes 
SlowDB: yaleman: 5.5 
otherguy: does mysql support hints? 
yaleman: well that's unlikely to be it πŸ™‚ 
SlowDB: the index u mean yaleman ? 
yaleman: wait, what's the full version code 
SlowDB: Server version: 5.5.27-cll MySQL Community Server (GPL) 
yaleman: k 
SlowDB: index still? 
yaleman: saw some weirdass issues with a recent ubuntu one 
yaleman: if you're not terribly worried about the possible performance hit I'd go for it 
yaleman: there's only 10k rows, and oid's an int isn't it? 
SlowDB: yes 
yaleman: then it's just doing a "select oid from [table]" and creating an index on it 
yaleman: did you remember to turn profiling back off 
SlowDB: yup! yaleman: πŸ™‚ 
yaleman: ip accounting's on our edge routers at work atm 
yaleman: I giggle to myself when I see it 
otherguy: at 17 secs, i dont think one index will fix, but lets see if it goes part way 
yaleman: how much traffic's on this server 
SlowDB: not too much 
SlowDB: it is a [snip] shop 
SlowDB: mysql> create index oid_index on vendor_shipping (oid); 
SlowDB: Query OK, 167 rows affected (0.02 sec) 
SlowDB: Records: 167 Duplicates: 0 Warnings: 0 
otherguy: cant see that helping at all 
yaleman: 8gb of ram and a quad core for a [snip] shop 
SlowDB: it fixed it 
SlowDB: mysql> DELETE FROM vendor_shipping WHERE vid NOT IN(SELECT distinct(vendorid) FROM products where pid in(select pid from orders_content WHERE oid='663')) and oid='663'; 
SlowDB: Query OK, 0 rows affected (0.00 sec) 
yaleman: πŸ˜€


#coding #Kepner-Tregoe #learning #Linux #MySQL #Programming #Queries #SQL