« Already fixed SMF bug
MySQL Optimization question
Bootstrap, from Twitter »

Dragooon

  • I can code! Really!
  • polygon.com has to be one of the best sites I've seen recently.
  • Posts: 1,841
MySQL Optimization question
« on September 7th, 2011, 07:11 PM »
I got a simple table having the following fields

Code: [Select]
id int(11) primary key auto increment
wpm_total int(11)
wpm_effective int(11)
time int(11)
duration int(11)
device varchar(20)

Now if I got a few statistical SQL queries
Code: [Select]
SELECT COUNT(*) AS num, device
FROM scores
GROUP BY device
ORDER BY device_total DESC
Code: [Select]
SELECT (AVG(wpp_effective) / AVG(wpp_total)) AS average
FROM scores
WHERE device = :device
GROUP BY :device
Code: [Select]
SELECT AVG(wpp_effective) AS average
FROM scores
WHERE device = :device
GROUP BY :device

What'd be the best way to optimize it(Indexes, etc) so that it doesn't cause heavy lags(Apart from caching)?


The way it's meant to be

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: MySQL Optimization question
« Reply #1, on September 7th, 2011, 07:19 PM »
What's the content of device? I know it's a varchar (20) but more info on its content would really help.
When we unite against a common enemy that attacks our ethos, it nurtures group solidarity. Trolls are sensational, yes, but we keep everyone honest. | Game Memorial

Dragooon

  • I can code! Really!
  • polygon.com has to be one of the best sites I've seen recently.
  • Posts: 1,841

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: MySQL Optimization question
« Reply #3, on September 7th, 2011, 07:37 PM »
Need more specifics. How variable are the field contents? Or is it literally "iPad", "Desktop" and so on?

Reason for asking is that the datatype seems wrong, namely that if the contents aren't that variable and are reasonably discrete, you should switch it to a smallint (if there's more than 255 variations) or tinyint (if there's less) because it'll save space per row, then you just index that field, which will be a lot faster and more useful in terms of indexing (indexing varchar fields are hard work based on content)

Dragooon

  • I can code! Really!
  • polygon.com has to be one of the best sites I've seen recently.
  • Posts: 1,841

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: MySQL Optimization question
« Reply #5, on September 7th, 2011, 08:55 PM »
Once you've got that numeric field, with an index, you should find life gets easier. After that, the next thing to do is to EXPLAIN the queries to see how MySQL attempts to process the query.
« Already fixed SMF bug
Bootstrap, from Twitter »