Wedge
Public area => The Pub => Off-topic => Topic started by: Dragooon on September 7th, 2011, 07:11 PM
-
I got a simple table having the following fields
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
SELECT COUNT(*) AS num, device
FROM scores
GROUP BY device
ORDER BY device_total DESC SELECT (AVG(wpp_effective) / AVG(wpp_total)) AS average
FROM scores
WHERE device = :device
GROUP BY :device 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)?
-
What's the content of device? I know it's a varchar (20) but more info on its content would really help.
-
Device's name. desktop, iphone, ipod, ipad etc.
-
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)
-
There are about 10 fields, I can do that I believe.
-
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.