List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 25 2003 4:26am
Subject:Re: performance
View as plain text  
In the last episode (Apr 24), Ryan R. Tharp said:
> IO bound I believe, I've seen the controller hit 74MBps (averages
> around 30MBps) where the load average is always under 1.
> Here the queries from the quick benchmark test I put together:
> "seq 1000 row Select"=>"select * from <table> limit 1000",
> "seq 1000 row Select starting at random point"=>"select * from <table> limit
> $rand,1000",
> "ran 1000 row Select"=>"select * from <table> order by rand() limit 1000",
> "seq 1 row Select"=>"select * from <table> limit 1",
> "starting at ran 1 row Select"=>"select * from <table> limit $rand,1",
> "ran 1 row Select"=>"select * from <table> order by rand() limit 1",

None of the above queries can use indexes afaik.  Query 4 doesn't need
them :)  I bet you are doing a MASSIVE amount of full table scans. 
Order by your autoincrement field on queries 1, 2, and 5.  5 and 6 are
the same query, btw, so don't use 6.  I have never used order by
rand(), so I don't know if mysql is able to optimize query 3.  What I
have done when customers request "random" ordering is create an integer
field, fill it with random data on insert, then "select * from table
order by randfield limit offset,xx".  I start 'offset' at zero, then
increment it by the number of records requested every time a query is
made.  That lets mysql use an index to position to the correct record
immediately, and prevents dupes.

> "1 row Insert Indexed columns"=>"insert into <table>
> (encCC,username,hitreferrerid) values ('deleteme','deleteme',".rand(1,100).")",
> "1 row Insert NonIndexed columns"=>"insert into <table>
> (city,state,intoprice) values ('deleteme','deleteme',".rand(1,100).")",

These should be fast.

> "1 row Update Indexed columns"=>"update <table> set encCC='DELETME',
> username='USERNAME', hitreferrerid=".rand(1,100)." where encCC='deleteme'",
> "1 row Update NonIndexed columns"=>"update <table> set city='DELETME',
> state='STATE', intoprice=".rand(1,100)." where city='deleteme' and state='deleteme'",
> "1 row Delete Indexed columns"=>"delete from <table> where encCC='DELETME'
> and username='USERNAME' limit 1",
> "1 row Delete NonIndexed columns"=>"delete from <table> where city='DELETME'
> and state='USERNAME' limit 1"

These should be fast, as long as you have the appropriate indexes on
your WHERE clause fields.

Are any queries showing up in your slow query log?

	Dan Nelson
performanceRyan R. Tharp25 Apr
  • Re: performanceDan Nelson25 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • Re: performancecolbey25 Apr
    • Re: performanceDan Nelson25 Apr
  • Re: performancecolbey25 Apr
  • Re: performanceRyan R. Tharp25 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • Re: performancecolbey25 Apr
      • RE: performanceAdam Nelson25 Apr
        • Re: performanceJeremy Zawodny15 May
  • Re: performanceJonathan Disher25 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • Re: performancecolbey25 Apr
    • Re: performanceChristian Jaeger28 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • RE: performanceAdam Nelson28 Apr