jaymc

I have a query which is causing problems on my server. I have used pastebin to provide the information http://pastebin.com/m45cba245 If I remove the ORDER BY p.timestamp DESC the query is practically instant With the ORDER BY, it uses file sore and tempory table on disk. The query can take up to 60 seconds sometimes The hub_posts table contains 100,000 rows Any ideas how I can re write this query or maybe add an index to take the presure off when doing the ORDER

Structure CREATE TABLE `msn` ( `username` varchar(20) collate latin1_general_ci NOT NULL default '', `memberemail` varchar(90) collate latin1_general_ci NOT NULL default '', `password` varchar(90) collate latin1_general_ci NOT NULL default '', `contactemail` varchar(90) collate latin1_general_ci NOT NULL default '', `date` int(15) NOT NULL default '0', UNIQUE KEY `username` (`username`,`contactemail`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci Query SELECT username FROM msn WHERE contactemail IN('[email protected]', '[email protected]') EXPLAIN "id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra" "1","SIMPLE","msn","index","","username","114","","373209","Using where; Using index"

5.0.54 How can I restructure this type of query where I have 1000 records I need to search for in one go?

I have a query which is something like SELECT username FROM members WHERE email IN('[email protected]','[email protected]','[email protected]','[email protected]','[email protected]') There are actually 1000 emails The query is taking around 5 seconds, there are 80,000 records in members. I created an index on the email but it wont use it. I even tried to FORCE INDEX but when using EXPLAIN it tells me its just using WHERE without any index How can I optimise this query using an INDEX or something else.. EDIT Ive just change SELECT username to SELECT email and EXPLAIN now says its using the index. However, the query took 20 seconds even with the index

Im having some load issues at the moment, I notice 100 queries in the process list with the majority being in "SENDING DATA" state The queries them selves arnt the problem. I have capture the query and run after the load issues and its fine, with no cache too I believe sending data is the moment when mysql is passing the result back to the caller (PHP) Is there a reason why sometimes I see 80 queries hanging around for 20+ seconds in SENDING DATA state

Cheers, I think I'll just scrap base ref Will have to add "/" at the start of all my links though, yawn Thanks

I used base ref in my html documents, however, this renders anchor links useless e.g. <a href="#top">Top</a> That will just go to the document root and add #top after the URL Searched around on google and seems to be a known problem but cant seem to find anyone with a solution where as I can use both successfully Any ideas?

Yeh I would like to say goodbye to ie6 but 25% of my IE users are still on IE6!

I have a CSS menu which drops down a floatig menu when you hover Works fine, however, in IE 6 the drop down menu sits behind any HTML SELECT drop down menus in the way I think this is a known issue, it cant be fixed with z-index due to the way ie6 renders form elements i think How can I fix this? I think there is some javascript that will hide all selects on a page when the css menu is called, then display them again But I'd rather not use javascript.. is there a better more universal solution for this?

Cheers

I want to add numbered page links to jump to pages rather than having NEXT / BACK e.g. << < 1 2 3 4 5 6 7 8 9 10 > >> I can do that as I can just devide total number of mysql matches by number of rows I am displaying on the page, however, here is the catch Lets say there are 80 pages, I am not going to have 1 2 3 4 5 6 ........78 79 80 So when they are on page 9, the display changes to something like this 7 8 9 10 11 12 13 14 15 16 17 Any tutorials or pointers on how I can tackle this

If I want this to be an all singing all dancing dedicated mysql box is it a case of the more memory the better As in, will I ever realistically need more than 12GB?

My database accumlates 1.5GB of data Here are the stats taken of the last 50 days ############################# Query statistics: Since its startup, 395,295,392 queries have been sent to the server. Total ø per hour ø per minute ø per second 395 M 325.21 k 5.42 k 90.34 Select = 42.21% Update = 21.16% Insert = 2% Change db = 18.2% ############################# I have 32GB of ram at my disposal and 4x single core Xeon 2.7GHZ 2MB cache, however I am using some of it for Virtual machines but only for webservers so wont use too much! The max amount of memory I have ever seen a dedicated mysql box have was 12GB and if I remember correctly it never used more than 8GB What would you recomend the minimum memory and CPU to give to MYSQL based on those stats above. Remember this is the central database so anything MYSQL related will go to this! All my tables are InnoDB At current I have 1 server (3GB memory, 2 single core CPU, which handles mysql, apache, lighttpd and the load does suffer at peak

If I dont need to pay extra money, why would I Hence, asking the question.. is what I have suggested good enough

I have 2 servers Server 1 = Database and application code (php) (100GB) Server 2 = File server for static files such as images and large files (2TB) I have no issue backing up Server 1 as its only around 10GB of data so I just gzip and xfer to Server 2 However, Server 2 is using 600GB of data so I cant xfer a backup to Server 1 I dont really want to add a Server 3 into the mix as these are all colocated hence $$$. My question is, do you think I can get away with adding another drive to Server 2 used soley for backup? I want to protect myself from data loss e.g. hard drive blow out. The problem with that though is if anything physically happens to Server 2 it could kill 2 drives Am I being over careful? The data isnt FBI or anything but data loss would not be good, trust me If So... Would anyone reccomend using the likes of rsync to backup so it only copies new/updated files as the IO to gzip or do a flat copy of 600TB would not be nice either

ok

Whatever man the more content for phpfreaks the better. You keep reffereing people to google and the may realise they dont need to come on here as there are always other websites in the long list of google that will give them answers

Yeh I have googled but I couldn't get a specific rock solid answer Hence I have come onto PHP phreaks for a rock solid answer from someone who has expert knowledge on this You could potentually reffer 80% of questions on here to google hence what is the point of a forum if the administrators do that? The idea is to source information on here so other members can find the answer to this from within PHPFREAKS, otherwise, why use this forum at all if they can get everything from google

Am I missing something? Im assuming that 42 is relivent in some sarcastic way to my thread

I need to buy a couple of CPU's for a server The server documentation states Xeon MP (multi processor) processors I have found some really cheap Xeon DP (Dual processor) processors, I'm just wondering if they will work Whats the difference? They are the same socket right.. Does any one know if this could cause an issue

I have 4 CPU sockets on a motherboard Currently there are 2x Xeon 2.5Ghz Would there be an issue if I added two more Xeon 2.8Ghz? Would this cause an issue? Would I get the 2.8Ghz out of those CPU's or will they all be clocked to 2.5Ghz Does the L2/L3 cache matter if they are different on the other two CPU's?

Yeh, Paypal website pro seems to do the job £20 a month | 20p + 3.4% per transactions Im looking for alternitives on the market for better rates, Nochex merchant account seems similar Anyone know of any others?

I need to accept credit cards online, however, I only want to use a 3rd party such as PAYPAL etc to verify credit card details. I want this done in the background via a SOAP link or something, so in essance they see nothing of paypal, just a form on my website which when they click submit it verifies via 3rd party and basically sends back YES/NO if credit card details are ok What are the best services to do this, perhaps with the cheapest fee per transaction Thanks.

I am not using 8 disks, just thats what I have available Why 5 disks for RAID 5? Why not just 3?

That's the answer I was looking for So basically in all situations if you can afford it go with RAID 5