Timeline for three-table JOIN query on a MySQL Cluster has become unusably slow
Current License: CC BY-SA 3.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 11, 2016 at 20:08 | comment | added | PMT | So adding that index on aliases.name did the trick. The query has gone from executing about 100X slower than it used to now executing about 10X faster than it used to. Given that there was never an index on that field, I still don't understand why the query suddenly slowed down so dramatically compared to previous use (the table does not grow quickly), but adding the index did have the desired effect. Thanks for the help! | |
| Jul 11, 2016 at 20:06 | vote | accept | PMT | ||
| Jul 9, 2016 at 7:10 | comment | added | BillThor | @DMT If you create an index (domain_id, name, route_id) or (name, domain_id, route_id) on aliases, the query should just use the index and avoid searching aliases. Having name indexed should help a lot. | |
| Jul 8, 2016 at 21:48 | comment | added | automatem | What about changing the front end to group 1000 or so names together in the user file@ | |
| Jul 8, 2016 at 17:17 | comment | added | ypercubeᵀᴹ |
Try adding an index on (domain_id, name, route_id) before going into anything else.
|
|
| Jul 8, 2016 at 15:31 | comment | added | PMT | I found this interesting check/solution yesterday, but it applies to MyISAM and InnoDB engines, and I don't know enough about MySQL Cluster to know if this can be applied as a test there too: stackoverflow.com/questions/11748629/… | |
| Jul 8, 2016 at 15:25 | comment | added | PMT |
domain_id belongs to the aliases table, so that second query actually wouldn't work without being joined to the aliases table. The first query, Select userid FROM users; took .58 seconds. The last query with the JOINs took 1'12". It sounds like adding an index would help, but I'm also wondering if I can up the memory allocated for caching tables (and if this is any different in MySQL Cluster since the tables are ndbcluster type).
|
|
| Jul 8, 2016 at 15:08 | comment | added | Lennart - Slava Ukraini | Which table does domain_id belong to? | |
| Jul 8, 2016 at 14:09 | history | edited | BillThor | CC BY-SA 3.0 |
Briefly explain performanc issue.
|
| Jul 8, 2016 at 14:05 | comment | added | BillThor | @PMT At some point you your tables grow to the point they don't fit in memory (very fast access) and you end up reading data from disk for most queries. This is a sudden switch. One query I wrote went from sub-second to five minutes when this happened. If you can compact the tables, this may recover the condition for now. Try comparing the results and timing of the first three queries I provided. | |
| Jul 8, 2016 at 14:01 | comment | added | PMT | Hi Bill, you are correct in that aliases.name could use an index and that may solve the issue (I will certainly add it), but the query has been in use for several years and has always executed in a second or so. It is called once per name from an input file (there 24000 names in the input file). So I'm wondering why it would suddenly start executing over 100X slower when no server or code changes have been made. Could it be a key_cache issue in that the indexed fields have grown too big to be held in the allocated key cache size? | |
| Jul 8, 2016 at 0:47 | history | answered | BillThor | CC BY-SA 3.0 |