Speed up mysql statement,

i have a mysql statement within my social networking site that shows a members friendslist. the problem is that when a member gathers  anywhere near 1000 friends the entire site slows down trying to gather the right information.  I hardly know anything about mysql but i have been reading http://www.experts-exchange.com/articles/Database/MySQL/3-Ways-to-Speed-Up-MySQL.html 

so i now understand that this statement is not using all indexes and does a number of table scans for the code.

I have attcahed a file to this question that has the table in question viewing its structure, browsing it and the mysql statement i am refering to and the statement with "explain" results

If you need more details feel free to ask me for them
Friends-List-Query.doc
driver_xAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flob9Commented:
You need an index on the "Check" field, and maybe you could try to add an index on the 3 fields (ID+Profile+Check).

Try both, and show us the EXPLAIN result.

You can also use EXPLAIN EXTENDED and SHOW wanrings (http://www.mysqlperformanceblog.com/2006/07/24/extended-explain/)
0
theGhost_k8Database ConsultantCommented:
'$id' << single quote around id value??
I hope its not going like...

...`FriendList`.`ID` = '1234'...
0
Kevin CrossChief Technology OfficerCommented:
theGhost_k8 makes a nice point there.  The '1234' will be cast/converted to integer if FriendList.ID is an integer (numeric) field, so your query will run; however, that might add slight overhead to have to do the conversion each time.

Your WHERE criteria with the OR and AND in the same parenthesis, you may be less efficient.  Try instead:

$id IN (`FriendList`.`Profile`, `FriendList`.`ID`) AND `FriendList`.`Check` = 1

-- OR --

((`FriendList`.`Profile` = $id OR `FriendList`.`ID` = $id) AND `FriendList`.`Check` = 1)

Arranging the parenthesis as needed to get the right logical separation if for example, it is id = profile OR id = id but only when check = 1.

You also have the same criteria in the LEFT JOIN as in the WHERE clause.  See a sample code correction below in MySQL form that you can test then put back into your PHP code if gets results you want.
SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture` 
FROM `FriendList` 
-- all your section here is based on profiles,
-- so left join inappropriate as well.
-- with where criteria on FriendsList, the query would act as an INNER JOIN.
-- if want a LEFT JOIN, then swap tables or use RIGHT JOIN and put where criteria in JOIN clause.
INNER JOIN `Profiles` ON `Profiles`.`ID` = `FriendList`.`Profile` 
WHERE $id IN (`FriendList`.`Profile`,`FriendList`.`ID`)
AND `FriendList`.`Check` = 1
ORDER BY `Profiles`.`Picture` DESC 
LIMIT 12 

Open in new window

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

driver_xAuthor Commented:
I think when you look at the attachment correctly i need to alter the friendslist table so that we use indexes properly like discussed here: http://www.experts-exchange.com/articles/Database/MySQL/3-Ways-to-Speed-Up-MySQL.html  but im not sure exactly how to alter the table and how to better call this mysql query
0
flob9Commented:
Using phpmyadmin, select the columns you want to add to the index (structure tab), then clic on the icon "Index".
0
Kevin CrossChief Technology OfficerCommented:
Having proper indexing is always a good idea, but before you go down the path too far, please take a good look at my comments on the query itself.  You were definitely querying in a manner that will cause you issues even with good index.  If you don't fix the query, you will beat your head against the wall trying optimization tricks on the database that will not address the root of the problem IMHO.
0
driver_xAuthor Commented:
Hi mwvisa1: yes i agree with your coments i ran the explain command with your suggestions and came back with error messgaes like  #1054 - Unknown column '$id' in 'where clause'

Thats why i thought i would have to alter the table like shown in the link i posted so that the information requested could be better sorted

0
Kevin CrossChief Technology OfficerCommented:
The issue is the $id is from PHP, so just pick an ID value you know.

e.g., if 1 is a valid id.

These are just comments on code:
-- all your section here is based on profiles,
-- so left join inappropriate as well.
-- with where criteria on FriendsList, the query would act as an INNER JOIN.
-- if want a LEFT JOIN, then swap tables or use RIGHT JOIN and put where criteria in JOIN clause.
SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture` 
FROM `FriendList` 
INNER JOIN `Profiles` ON `Profiles`.`ID` = `FriendList`.`Profile` 
WHERE 1 IN (`FriendList`.`Profile`,`FriendList`.`ID`)
AND `FriendList`.`Check` = 1
ORDER BY `Profiles`.`Picture` DESC 
LIMIT 12 

Open in new window

0
driver_xAuthor Commented:
i tried your alternatives and got some of them to work the problem with the code you have just written is that it only returns the original member as their own friend. i rean it through mysql and it was ok as long as it knows exactly what ID to search for
0
Kevin CrossChief Technology OfficerCommented:
Think I may just be confused over which column holds the ID and which is the self joining reference to friend ID.  Can you post some sample  of the data and will update the query.  Even better if you have create statement for table and brief insert of sample data.  Then can test before re-posting.
0
Kevin CrossChief Technology OfficerCommented:
Need to also understand if Check = 1 is required for both conditions or what as that may need to be grouped with one of the other conditions to work correctly.
0
driver_xAuthor Commented:
ok i ran this query.

SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`
FROM `FriendList`
INNER JOIN `Profiles` ON `Profiles`.`ID` = `FriendList`.`Profile`
WHERE $id IN (`FriendList`.`Profile`,`FriendList`.`ID`)
AND `FriendList`.`Check` = 1
ORDER BY `Profiles`.`Picture` DESC
LIMIT 12 "

I attached a picture of how the friendlist is being displayed at the moment using that code.
11.JPG
0
driver_xAuthor Commented:
you can see the members profile that the friendlist either shows only their own profile as their friend or friends and theirs.... what data do you need?
0
driver_xAuthor Commented:
And when i try to open a profile with 4800 friends it still takes about 4 minutes to open the page
0
flob9Commented:
According to the explain query, there is a full table scan for both FriendList and Profile (8828 * 22471 row scanned). Did you try to add the indexes ?
0
driver_xAuthor Commented:
no i have'nt tried that cause i dont know how
0
flob9Commented:
Under phpmyadmin, go to structure tab, and click on index button for line "Check".

0
Kevin CrossChief Technology OfficerCommented:
Let's say ID is the column containing the ID of the person being queried (e.g., coolguymn) and Profile is the ID of the friend (e.g., shameeka), then you would only want to query where ID = $id and not Profile = $id.  So possibly this:

SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`
FROM `Profiles`
INNER JOIN `FriendList` ON `Profiles`.`ID` = `FriendList`.`Profile`
   AND `FriendList`.`ID` = 1 /* $id */
   AND `FriendList`.`Check` = 1 /* is this needed? */
ORDER BY `Profiles`.`Picture` DESC
LIMIT 12

--may be better to ORDER BY `Profiles`.`ID` DESC also since picture is most likely the image location text.  Using the ID should take advantage of the index and numeric data type for the sort.

Again, since you are not using anything from FriendList in the selection (results), you can try this; however, I would suspect the JOIN to be more efficient.

SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`
FROM `Profiles`
WHERE EXISTS (
   SELECT 1
   FROM `FriendList`
   WHERE `Profiles`.`ID` = `FriendList`.`Profile`
   AND `FriendList`.`ID` = 1 /* $id */
   AND `FriendList`.`Check` = 1 /* is this needed? */
)
ORDER BY `Profiles`.`Picture` DESC
LIMIT 12

You definitely want proper indexes added as well.  So once we get the query returning the correct data, then you can go from there.  And if I understood you and the picture, the above fixes should help as should not pick up coolguymn as his own friend unless your data has rows where ID and Profile are equal.  You could then add to filter "AND Profile <> ID".
0
driver_xAuthor Commented:
I have tried all the above scenarios and with no success. I stand by my original thought that the table needs to be altered so that indexes are better used instead of complete table scans. it should be achieved similarly to the table change found here http://www.experts-exchange.com/articles/Database/MySQL/3-Ways-to-Speed-Up-MySQL.html 
 Once that is done then i agree that the mysql statement can ONLY then BE changed to suit the new table
0
Kevin CrossChief Technology OfficerCommented:
Not sure what 'no success' means.  An error?  Slowness?  You indicated earlier you were getting incorrect results and you had incorrect / inefficient code syntax.  All I am trying to get you to is the correct set of results, then you need to do as has been said all along in this thread is to set index on Check as your diagram already shows there is an index on ID and Profile already.  If you have not set one on the ID column in the Profiles table, then you need to do that also.  But based on your attachment, you have indexes in FriendList table already which is why I chose to address your bad syntax first. :) Since you have read the article which I have seen in great length in the past and know it to be very detailed, I suggest you read through it to understand what you need.  Not sure what else to provide you outside that article if syntax is not an issue for you.
0
Kevin CrossChief Technology OfficerCommented:
Anyway, hope you find what you need. :) Have a great weekend; have to go cook for the family as we have been fasting all day.
0
driver_xAuthor Commented:
maybe i dont understand but i was already getting the correct results with the original statement its just so slow cause i am doing two complete table scans
0
Kevin CrossChief Technology OfficerCommented:
Your comments that the query only returned the ID as its own friend, so maybe I misunderstood.  My point was that your query's syntax issues were causing the additional table scans as your attached document shows indexes on the fields involved except for Check.  If you don't want to go through the process of fixing the query, then just do as instructed earlier by other experts to add the index on Check.  They offered how as well, so just take a look.
0
Kevin CrossChief Technology OfficerCommented:
Really read what I am saying here -- http:#25158807 -- I discuss utilizing the indexes you already have.  If that truly didn't help, we need more detail than 'no sucess'.  Was it slower, faster but still slow, wrong results...

Try doing an EXPLAIN on your original query and then the ones that were provided that also return the right results (which is what I was trying to do -- in order to help I had to understand which alteration was on track -- then we can speed up from there).
0
Kevin CrossChief Technology OfficerCommented:
Did that really solve the issue?  If not, then instead of accepting you should use the Request Attention to get a moderator to request additional experts review your question.  Hopefully, you went back and reviewed and found the fix in what I said, but if not please post back so I can have this unaccepted so you can get the appropriate help instead of accepting a post that doesn't answer your question.
0
Richard DavisSenior Web DeveloperCommented:
I looked at your attachment and would really like to see your profiles table structure also. At a glance though, I would assume you have a primary key in your profiles table where a member's profile ID has a one to many relationship with your friends list table.

If that is true, then you should only need to perform a simple join rather than something complicated.

For instance, in the query I provided below, that results should be that it would return all profiles that have a matching friendlist ID and reject all others.

I just don't see any reason why the query needs to be any more complex than that personally.

Hope that helped.

~A~
SELECT `Profiles`.`ID`, `Profiles`.`NickName`, `Profiles`.`Pic_0_addon`, `Profiles`.`Picture` 
FROM `FriendList` 
LEFT JOIN `Profiles` ON ( `Profiles`.`ID` = `FriendList`.`Profile` ) 
WHERE `FriendList`.`Profile` = '$id'
ORDER BY `Profiles`.`Picture` DESC 
LIMIT 12

Open in new window

0
flob9Commented:
The procedure I gave you on phpmyadmin generates the ALTER query.

ALTER TABLE `FriendList` ADD INDEX `Check`(`Check`);

To add an index to the 3 fields :

ALTER TABLE `FriendList` ADD INDEX `FriendTriple`(`ID`,`Profile`,`Check`);

Then, show us the explain results again.

Note, to drop the indexes above :

ALTER TABLE `FriendList` DROP INDEX `CheckIndex`;
ALTER TABLE `FriendList` DROP INDEX `FriendTriple`;
0
driver_xAuthor Commented:
I Have attached a file with the contents of the profiles table structure for you to view. I also added your mysql statement to the friendslist, i guess the best way to describe the outcome would be to show you guys at first hand. the demo site is at ticket4one.com/tdemo
0
driver_xAuthor Commented:
profile structure
PROFILE-STRUCTURE.doc
0
Richard DavisSenior Web DeveloperCommented:
First of all, there is NO reason to have so many fields all set to be indexes.

I would start by deleting all the the indexes on every field in your profiles table except for the primary index. That is simply overkill.

Secondly, I would do the same with the friendslist table also. Remove the indexes off everything except the ID field, which is your primary key.

~A~
0
driver_xAuthor Commented:
No these indexes are used with other queries i have more then one mysql query within my site
0
driver_xAuthor Commented:
@flob9: HAVE  LOOK AT THE INDEXES AGAIN I ALREADY HAVE A CHECK COLUMN IN THE INDEX
0
flob9Commented:
I checked again, didn't see the index on the Check field ... You had FriendPair (ID + Profile), ID, and Profile.

If you have an index on "check" field now, check the result of the explain query again.
0
driver_xAuthor Commented:
I added a file to this comment refering to the "check" column that i am refering to. This is the column that the statement is refering to.
Friends-List-CHECK.doc
0
Kevin CrossChief Technology OfficerCommented:
Driver_x,

The article by gr8gonzo has a link to this question in it, so hopefully he will come by and offer you some insight.

Until then, if you are indicating that you already have the filters in place that flob9 is recommending and you are still having troubles with troubles being slow performance, then why not really take a look at what others have been saying about the unnecessary complexity of the query.  If your query is not properly formed, you will not take advantage of all those indexes anyway.

Instead of creating more indexes, you may just need to utilize the ones you have more efficiently.  I made a point on that earlier myself on the order by Picture DESC to get a limit of 12 when dealing with 4800 rows.  MySQL has to sort the entire 4800 AFAIK by the possibly poor indexed column just to get you the 12 records.  If you order by a field with a strong index like ID (primary key) then may yield quicker response.

--isa
0
Kevin CrossChief Technology OfficerCommented:
Some others are simple like passing INT values as INT instead of wrapping with single quotes which make MySQL have to convert the value before comparing.

i.e., Check = '1'
should be: Check = 1
0
driver_xAuthor Commented:
@mwvisa1: i do look at your professionalism with a great deal of respect and i appreciate the levels of which you try to help. I may not be a programmers little toe but i can see that doing two full table scans on tables that are massive is what is causing the main problem.

I understand that the mysql statement maybe written a little crazy like but the truth no matter what i have tried is this. if i have a pile of dog dirt infront of me, nomatter from what direction i try to look at it at the end of the day it will always be dogs dirt.

if the structure of the friendslist is still going to be scanned twice for complete results then why would i continue to try and look at it from another direction? it makes no logical sense
0
Kevin CrossChief Technology OfficerCommented:
Because structuring the query different to uiltize the indexes you already have in place and to not cause additional scans accidentally will solve the issue.

I wasn't trying to insult your programming skills. Instead of dogs dirt, I am suggesting right now you have a bag of flour in front of you that you want to be cupcakes. If you never put the right ingredients into the flour it will remain flour. :) Difference between dogs dirt and flour is the latter still has a chance to be transformed to something useful which is all I am saying.

But anyway, the moderators will hopefully see your secondary request to get more experts involved as today is the date the RFA thread is marked for follow-up.

Regards,
--isa
0
driver_xAuthor Commented:
i have followed what you suggested i.e., Check = '1'
should be: Check = 1 i did this to the entire query (im not sure if thats what you meant) the differance is minimal.

this part of the query is where the slowness is created. we all agree to that.

LEFT JOIN `Profiles` ON (`Profiles`.`ID` = `FriendList`.`Profile` AND `FriendList`.`ID` = '$id' OR `Profiles`.`ID` = `FriendList`.`ID` AND `FriendList`.`Profile` = '$id') WHERE (`FriendList`.`Profile` = '$id' OR `FriendList`.`ID` = '$id' AND `FriendList`.`Check` = '1')

However in order to get these exact results. two table scans is unavoidable
0
Kevin CrossChief Technology OfficerCommented:
Why do you have the criteria in the WHERE and in the LEFT JOIN statement?  That is the part I think a lot of us are struggling with.  That is avoidable.

And you are not grouping logical expressions together with parenthesis so what you think may be happening is not necessarily.

For example,
(`FriendList`.`Profile` = '$id' OR `FriendList`.`ID` = '$id' AND `FriendList`.`Check` = '1')

Not sure, but you may want this to be true if
Profile = $id
OR
ID = $id AND Check = 1 -- <<< meaning both have to be true

Alternatively this could mean this is true if
Profile = $id OR ID = $id -- <<< meaning one or the other can be true
AND
Check = 1 -- <<< Check must be true in both cases

Not sure if you can see my point, but look at the ON statement for the join.  The same logical ambiguity can be seen.
0
driver_xAuthor Commented:
To be blatantly honest i dont know why its written like that, i think its cause it needs both to be true. this particular code was written by the original developers of this site...

I think its because the friendslist id and the id from the profile table are different
0
Kevin CrossChief Technology OfficerCommented:
See if a UNION query will satisfy the need for it to exist that way.  Still not sure where Check = 1 comes into the mix, but you can throw that in to one or both of the queries where appropriate by removing the comment (/* */) markers from around text "AND `FriendList`.`Check` = 1" in the appropriate JOIN clause.
/* get records where ID = $id */
SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`
FROM `Profiles`
INNER JOIN `FriendList` ON `Profiles`.`ID` = `FriendList`.`Profile` AND `FriendList`.`ID` = $id
/* AND `FriendList`.`Check` = 1 */
UNION /* use union to get distinct values */
/* get records where Profile = $id */
SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`
FROM `Profiles`
INNER JOIN `FriendList` ON `Profiles`.`ID` = `FriendList`.`ID` AND `FriendList`.`Profile` = $id
/* AND `FriendList`.`Check` = 1 */
/* order the result of the union by the `Picture` column */
ORDER BY `Picture` DESC
LIMIT 12

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Meant to give you this reference for UNION as well:
http://dev.mysql.com/doc/refman/5.0/en/union.html
0
driver_xAuthor Commented:
WOW, now we are cooking with gas, i can tell physically that is much faster for 99% of profiles.  I changed the script for the demo site tdemo and its easily visible. i then gave the $id an actual value and did an EXPLAIN in my phpmyadmin. i attached the results in this comment.

the value that i gave your code is that of the member with the largest friendslist on the entire site approx 4818 friends... that may seem like a hell of alot of friends but when taken into consideration that other websites have members with over 1000000 friends its nothing.

thie profile in question is the main admin profile kinda like tom from myspace. So its fair to say its only going to get even more popular. Hence the need to further optimize
WID.JPG
0
Kevin CrossChief Technology OfficerCommented:
You can look at possibly adding query hint.  See the reference on MySQL manual for greater details:
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
"You can provide hints to give the optimizer information about how to choose indexes during query processing."

The EXPLAIN plan shows much better results but that the index being used is the paired one which may not be a problem and may not improve going to single field; however, it may be slightly more efficient and so worth the shot.  Although, I guess each JOIN does use the pair as only one field is needed to join to the Profiles table, but the other is needed to match to $id.  I would try though.  It would optimize the query to use just the index for field ID when that is being used and Profile when that is being used instead of always using both together.
0
Kevin CrossChief Technology OfficerCommented:
Looks like we have eliminated the full table scan on Profile table though and that might do the trick in the long run for you, but won't hurt you to keep optimizing as you said.
0
Kevin CrossChief Technology OfficerCommented:
And nevermind on the FriendPair index.  I see now that the row in explain with 3951 rows is using Profile key.  I was looking at the possible keys column.  Too early in the morning I guess.  The first one is using FriendPair that returned 11 rows.
0
driver_xAuthor Commented:
i agree, but where the member has 3951 friends it must be taking too much data from their profiles? i dont understand how larger sites with over 1000000 friends can possibly handle such a query
0
driver_xAuthor Commented:
i guess its now about using the right index hints somewhere in the query
0
Kevin CrossChief Technology OfficerCommented:
Yep.

Probably going to be heavy as you are returning data from the Profile table.

You can try this for grins:

/* get records where ID = $id */
SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`
FROM `Profiles`
INNER JOIN `FriendList` ON `Profiles`.`ID` = `FriendList`.`Profile` AND `FriendList`.`ID` = 1
/* AND `FriendList`.`Check` = 1 */
UNION /* use union to get distinct values */
/* get records where Profile = $id */
SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`
FROM `Profiles`
INNER JOIN `FriendList` ON `Profiles`.`ID` = `FriendList`.`ID` AND `FriendList`.`Profile` = 1
/* AND `FriendList`.`Check` = 1 */
/* order the result of the union by the `Picture` column */
ORDER BY ID DESC
LIMIT 12

And also this:

/* get records where ID = $id */
SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`
FROM `Profiles`
WHERE EXISTS (SELECT 1 FROM `FriendList` WHERE `Profiles`.`ID` = `FriendList`.`Profile` AND `FriendList`.`ID` = 1)
/* AND `FriendList`.`Check` = 1 */
UNION /* use union to get distinct values */
/* get records where Profile = $id */
SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`
FROM `Profiles`
WHERE EXISTS (SELECT 1 FROM `FriendList` WHERE `Profiles`.`ID` = `FriendList`.`ID` AND `FriendList`.`Profile` = 1)
/* AND `FriendList`.`Check` = 1 */
/* order the result of the union by the `Picture` column */
ORDER BY ID DESC /* can try w Picture also */
LIMIT 12

BTW, how much faster did the query improve with the change I made using UNION? The response times don't show on the picture.
--isa
0
driver_xAuthor Commented:
i cant tell from the explain query cause it uses an exact value on the $id but i can tell from the actual site its about 50% faster. im going to try the code you suggested
0
driver_xAuthor Commented:
i ran the first query you suggested did'nt work as well. it only showed members had one friend, the main admin one.

i tried the second and it just returned the main admin as only friend
0
driver_xAuthor Commented:
actually it has'nt worked that well at all, you see i had a mod-rewrite installed that changed a members url from ie, site.com/profile.php?ID=member to site.com/member when i use your code it changes the url back to its original form
0
Kevin CrossChief Technology OfficerCommented:
Ok, well I guess stick with the UNION and see if the query hints help.
0
Kevin CrossChief Technology OfficerCommented:
Sorry meant, the original UNION query.  Not sure I follow you as my change was to the MySQL code.  It should not impact your URL or how PHP is handling your URL.  You probably need to open a question in the PHP zone with that code to check on that OR a question in your web servers zone (e.g., apache) to see if there is an issue with the mod-rewrite statement.

Regards,
--isa
0
driver_xAuthor Commented:
i dont know what happened but its working fine again....... so working on the original UNION statement
0
Kevin CrossChief Technology OfficerCommented:
driver_x, your last post was this is working fine.  Is there anything else you need relating to this question?
0
driver_xAuthor Commented:
sorry i actually meant that the original speed up code with union was working fine again. I'm actually still trying to figure out how to speed up the profiles so that the ones with very large friendslists are a lot quicker too
0
Kevin CrossChief Technology OfficerCommented:
Not sure how much faster it can get than the 50%, but would need the exact speed you are seeing now that the full table scan went away on the one table.  What is the current speed like?
0
Kevin CrossChief Technology OfficerCommented:
Can try this suggestion again for giggles as it is the only thing I see that can cause you further issues as you would have to sort all the data by a field holding Picture information just to pick the 12 values.  The numerical ID field is probably more efficient.
/* get records where ID = $id */
SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`
FROM `Profiles`
INNER JOIN `FriendList` ON `Profiles`.`ID` = `FriendList`.`Profile` AND `FriendList`.`ID` = 1
/* AND `FriendList`.`Check` = 1 */
UNION /* use union to get distinct values */
/* get records where Profile = $id */
SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`
FROM `Profiles`
INNER JOIN `FriendList` ON `Profiles`.`ID` = `FriendList`.`ID` AND `FriendList`.`Profile` = 1
/* AND `FriendList`.`Check` = 1 */
/* order the result of the union by the `Picture` column */
ORDER BY ID DESC
LIMIT 12

Open in new window

0
driver_xAuthor Commented:
thats the exact same code that i am already running. it did speed up the profiles with smaller friendslists its the ones with very large friendslsts that is still lagging behind
0
Kevin CrossChief Technology OfficerCommented:
You will notice the ORDER BY is on the ID column.  The EXPLAIN plan you posted is with ORDER BY on Picture column.  This could make a difference per my statement(s).  If you are indeed running the code with ID column in the order by as I suggested, then we would need to see the EXPLAIN plan and speed details on that query and not the other one.  Can't help with incorrect information. ;)
0
driver_xAuthor Commented:
tried and tested it made no change to speed
0
driver_xAuthor Commented:
RESULT
untitled.JPG
0
flob9Commented:
Try without to order by to see if the bottleneck is the filesort.
0
driver_xAuthor Commented:
made the change but to no avail, returned same result really. i think it maybe because its drawing so much information from the 3951 rows
0
flob9Commented:
You should check the execution time under phpmyadmin, when you execute the query, it shows something like this :

Showing rows 0 - 29 (6,116 total, Query took 0.0642 sec)

Tell us how much time you get.
0
driver_xAuthor Commented:
Showing rows 0 - 11 (12 total, Query took 0.2057 sec)
0
flob9Commented:
Ok, this is not *so* slow, check difference with or without order by, etc ...

Do not execute the query twice too fast, you will get query cache result (very fast).
0
driver_xAuthor Commented:
Showing rows 0 - 11 (12 total, Query took 0.2071 sec
0
flob9Commented:
4000 rows scan should not take 200ms ... what is your hardware ?

Looks like you have apache 1.3 ... this seems pretty old.

You also have mod_bwlimited, wich could slow down pages.
0
driver_xAuthor Commented:
Apache 2.0 and 2.2
0
flob9Commented:
Hmmm ... I meant hardware, we have the apache (software) version in the response header :

Server: Apache/1.3.41 (Unix) mod_fastcgi/2.4.2 PHP/5.2.3 mod_deflate/1.0.21 mod_jk/1.2.14 mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 PHP/4.4.9 FrontPage/5.0.2.2634a mod_ssl/2.8.31 OpenSSL/0.9.7a
X-Powered-By: PHP/4.4.9

Do you know what server (hardware) you have running this ?
0
driver_xAuthor Commented:
no idea
0
driver_xAuthor Commented:
so i take their is no way to speed it up properly
0
Kevin CrossChief Technology OfficerCommented:
Probably not beyond the speed up technique I have already given you and the indexing you have already done after reading the article.  Sometimes you just have a slower query than desired if you can't control environment any further as it sounds as though you don't have ability to know or change hardware specifications in this case. :)  That could be your main issue as a result set of the size you are talking about would need ample memory and good CPU wouldn't hurt to process the request faster.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
flob9Commented:
nice :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.