[SOLVED] Solution

[jaymc]

I am trying to find a solution to help take the load off a query

 

I have a friends table which has 1.2Million rows in it

 

On there profile page, I have a query that pulls out there top 5 friends, usually ordered by date added. Aswell as this, it also joins a field from the gallery table to add there friends profile picture

 

The profile page is heavily accessed, that query alone takes around 0.3 seconds which doesnt seem long but combined with the other stuff, it really could be better

 

 

I am working on a solution, I have created a table called CACHE. in that table, any time a friend is added or deleted a script runs to rebuild there top 5 friends and dumps it into a field as a serialised array

 

Then, when viewing there profile rather than query 1.2 mill rows, it just pulls out the compiled cache from the cache table, unserialises it and then runs 1 query to add there profile picture to the array

 

Is this the best way to avoid querying huge tables

[fenway]

I guess you could run an SP that populates a summary table... this is similar to what you have done with serializing magic.

[jaymc]

SP?

[toplay]

SP is Stored Procedures.

 

Before you go and design a new table and such, have you done an "EXPLAIN" on your query that's taking a while? I'm sure adding indexes if some don't already exist will help immensely (and won't matter how many rows you have in your table).

 

Let us see the table structures (with indexes defined), the query and the explain of it if you like us to help further. Sometimes changing the query helps. For instance using "OR"'s in "WHERE" clauses on older version of MySQL slows the query, and can be converted to using "UNION" instead. Tell us your version of MySQL too.

 

 

[jaymc]

Here is the structure with indexes and sample data

 

[attachment deleted by admin]

[jaymc]

Here is the query, takes around 0.3 seconds which is too long

 

$queryx = "SELECT SQL_CALC_FOUND_ROWS friends.user, friends.friend, friends.gender, friends.x, friends.timestamp, gallery.timestamp as image
FROM friends
LEFT JOIN gallery ON friends.friend = gallery.user AND gallery.valid = 2
WHERE friends.gender = 'Male' AND friends.user = '$_GET[user]'
ORDER BY friends.x DESC, friends.timestamp DESC LIMIT 0,5";

$doqueryx = mysql_query($queryx);	
$totalmalefriends = mysql_result(mysql_query('SELECT FOUND_ROWS()'),0);

[fenway]

I'd lke to see an EXPLAIN, too.

[jaymc]

Here

 

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra 

1 SIMPLE friends ref user user 25 const 621 Using where; Using filesort

1 SIMPLE gallery ref user,valid user 27 jaydio_jaydio.friends.friend 2

 

[fenway]

Filesort?

[jaymc]

Thats the explain for the query above?

 

No idea what that file sort is..

[toplay]

You didn't supply the info on the gallery table but I can see from the explain that it's using the "user" column key on that table so that should be fine. The main friends table is getting about 621 row out of the million plus, and then doing a sort off that. Again, that shouldn't be too bad.

 

Run the query in SQLyog because it tells you how many milliseconds the query takes (at the bottom in the status bar). They have a trial download.

 

You may want to try running it without the "SQL_CALC_FOUND_ROWS" option just to see if it makes a difference in speed. It might be actually faster just running the below query separately to find out how many "Males" since we're leaving out the join of the gallery table:

 

SELECT

          COUNT(`id`) AS total_male_friends

  FROM `friends`

WHERE `user` = '$_GET[user]' AND `gender` = 'Male'

 

If you put an index on friends.timestamp column it may speed up the sorting.

 

[fenway]

And you can drop that index of "x".