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
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!
Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc