henryhenry Posted June 21, 2007 Share Posted June 21, 2007 Hello!I'm trying to choose the best method to optimise my site when connecting to a mysql database.The options that I can work out are:1. persistent connection (ie mysql_pconnect())2. normal connection - open at top of page, close at end of page3. open and close connection every time a query is performedMy site is pretty normal and I think I'm running about 20 or 30 queries on every page. (maybe that's not normal though?).I have been using the second option on that list. The site seems pretty slow and I've got the queries quite well optimised with lots of indicies, so I am thinking that the slowness may be related to the connections? Either because I'm not closing after every query or because I'm not leaving it open for the whole page.Thanks as ever!Henry Link to comment Share on other sites More sharing options...
yoshida Posted June 21, 2007 Share Posted June 21, 2007 I usually use the second option too.Performance is not only affected by the number of queries, but also by the number of open connections. Your server will crash at a certain number of connections.You want as little connections open as is needed, ergo you want them closed as soon as possible. At least that's my opinion. But I never tried persistent connections. Perhaps you could try it and see what happens.I'm urged to discourage opening and closing with every query, since connecting takes time and resources. Use each connection effectively, as little as needed at a time. Link to comment Share on other sites More sharing options...
henryhenry Posted June 21, 2007 Author Share Posted June 21, 2007 Thanks Yoshida!As an add on I am wondering whether to make the effort to convert to object-style for mysql stuff. I only just learnt object-style and I'm not really looking to spend hours converting all my connection bits to object style but if it speeds things up a lot then I might. Any opinions? Link to comment Share on other sites More sharing options...
shujjah Posted June 21, 2007 Share Posted June 21, 2007 y not just put the connection code in another file and call it whenever you want it Link to comment Share on other sites More sharing options...
henryhenry Posted June 21, 2007 Author Share Posted June 21, 2007 Hi SpeedyzYeah, I do that at the moment, including a script with the mysql_connect() in it. I wondered if it's faster (in terms of performance) to use the object method? Link to comment Share on other sites More sharing options...
justsomeguy Posted June 21, 2007 Share Posted June 21, 2007 The object and procedural methods should have comparable performance, they both do the same things, just a different way. Don't create a new connection for every query, that is a huge waste of resources. Connections aren't going to slow your site down that much, the reason your site is slow is because you have 20 queries on one page. Even 5-10 on one page is getting up there, it is best to try and keep it below 5. Split the pages up if you need to, but it sounds like you aren't being efficient with the queries. I have several sites that do a lot of data processing, and even if I am doing something like updating a user, which requires 1) authenticating the logged in user, 2) checking permissions for the user, 3) getting the list of users, and 4) updating the user, that's only 4 queries best case for the entire page, maybe upwards of 5 or 6 depending on the user info involved. So, if you're using 20 queries to generate a page and the queries are efficient, then you're showing way too much info on one page. If there's not that much info, then the queries are not efficient.If you want to paste some code I'll be happy to comment on it. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.