Am 13.01.2011 22:13, schrieb Steve Staples:
> the only writes that happen, is when a customer has their "status" > changed, password updates, or they change their name, OR when a new > customer is created. I would say a new customer get created (insert > into) about 30 times per day, and a customer gets updated (update) > prolly about 20 times per day, and customers getting deleted or > suspended about 20 (there are rough guesses), That should not make any problem > and then customer searches > are about 200-300 times per day, and then the other part that does all > the searching, prolly about 100-200 per minute (or so). I am really > guessing here, but i am prolly pretty close. This is nothing, normally hundrets of querys per second should not be a prolem, even thousands in most cases Are you sure that the lags are really the query and not the connection? I have seen on a windows server with ipv7 large lags because mysql treid by every connect to make a dns-reverse-lookup first on ipv6 and after fail ipv4 "skip-name-resolve" in the mysql-config did the trick, but make sure that there are up-addresses instead of hostnames in the permissions-tables before try this > the query that does the most, is: > mysql> EXPLAIN SELECT customer_id FROM customer_account WHERE > `full_login`='<email address>' ORDER BY `status` DESC LIMIT 1 \G > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: customer_account > type: ref > possible_keys: NewIndex5,NewIndex4 > key: NewIndex5 > key_len: 202 > ref: const > rows: 2 > Extra: Using where > 1 row in set (0.00 sec) > > NewIndex5 is 'full_login', 'status' > NewIndex4 is 'full_login' (fulltext) "NewIndex4" is useless but not used, it only should waste space and making inserts/updates slower How many rows have the table? if the table is large "order by" often makes things really slow and sometimes the optimizer from mysql can not benefit from keys while sorting - Do you really need the sort because you only fetch one row and if `full_login` is unique you should not need it Anyways: is query_cache in use? this can improve performance really and in your case where most querys are reads the cache will not be invalid most time, but that should be optimizing and not the prolem!
signature.asc
Description: OpenPGP digital signature
