Just now realized I answered to Mike only oops.
So posting it again... forcing the use of the use_id index didn't really
improve things, unfortunately.
Cheers,
Leonardo Borges
www.leonardoborges.com
On Sat, Jul 9, 2011 at 7:24 AM, mos wrote:
> Leonardo,
>What happens when you use "fo
Leonardo,
What happens when you use "force index(user_id)" ?
See http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
Mike
At 09:19 AM 7/8/2011, you wrote:
Same as before, but with the new index listed in the possible keys:
++-+---+---+--
Same as before, but with the new index listed in the possible keys:
++-+---+---+--+--+-+++-+
| id | select_type | table | type | possible_keys
| key | key_len | ref
What did the explain output look like after the new index?
On Fri, Jul 8, 2011 at 8:53 AM, Leonardo Borges wrote:
> Hi Johnny,
>
> I just gave that a try but it didn't help as I suspected.
>
> I still believe the problem is in mysql not being able to handle set
> subtractions. Therefore, it has
Hi Johnny,
I just gave that a try but it didn't help as I suspected.
I still believe the problem is in mysql not being able to handle set
subtractions. Therefore, it has to perform the work harder to return the
rows that represent a "no match" with NULL values in place so they can then
be filtere
Leonardo,
I think a new compound key on email_id and activity in the activities table
may help.
I'm not sure if this will help or not, Its hard to test w/o having a large
data set to test against.
On Thu, Jul 7, 2011 at 9:07 PM, Leonardo Borges wrote:
> Sure can:
>
> show create table activit
Sure can:
show create table activities;
CREATE TABLE `activities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`country_iso` varchar(2) DEFAULT NULL,
`tags` varchar(255) DEFAULT NULL,
`postcode` int(11) DEFAULT NULL,
`act
Can you post show create table for activity and explain output of the
problem query?
On Jul 7, 2011 8:51 PM, "Leonardo Borges"
wrote:
Hello everyone,
I have an increasingly popular web application running on top of mysql and
due to its popularity, I'm running into performance issues. After care
Hello everyone,
I have an increasingly popular web application running on top of mysql and
due to its popularity, I'm running into performance issues. After carefully
examining database indexes and tuning queries I was able to pin down the
slowest part of the system.
The app's got a user segmenta
