Ernest Semerda wrote:
> > How does the EXPLAIN look now?
>
> tableA ALL datestamp NULL NULL NULL 434839 Using where; Using temporary; Using filesort
> tableB ref pid_id pid_id 25 tableA.pid 1
Well it seems MySQL decided to use a full tablescan (ALL) instead of a range join type on datestamp.
Considering your earlier output of SHOW INDEX FROM tableA:
tableA 0 PRIMARY 1 ID A 434836 NULL NULL BTREE
tableA 0 ID 1 ID A 434836 NULL NULL BTREE
tableA 1 ID_2 1 ID A 434836 NULL NULL BTREE
tableA 1 datestamp 1 datestamp A 37 NULL NULL BTREE
This seems logical since datestamp's cardinality is only 37 and you want 31 of them - which is almost all.
The only thing that bothers me is the "Using temporary; Using filesort".
It's a long shot, but did you try to GROUP BY tableB.pid_id instead of tableB.pid_fullname (if they are 1 to 1 of course)?
I still have the feeling that your hardware should be capable of better performance. Next step should be the tuning of your server's parameters. But that's not really my cup of tea (yet).
Someone else maybe?
--
felix
Please use
BBCode to format your messages in this forum.