Mysql – Top 10 in MySQL

MySQL

Following a load test of my website I am trying to find the slowest performing web objects of my application. I have a field called 'path' with the applications object path along with a field called 'total_active' which gives the load time of the object. I have thousands of these records and I was wondering how I could just get the top 10 slowest along which what the objects slowest load time was.

I've tried the following, but it just gives me the objects in order of load time and doesnt group them. Can anyone advise?

select 
    path,
    time_active
from lb.object
order by time_active desc
limit 10;

Best Answer

SELECT path, sum(time_active) as summed FROM lb.object
group by path
order by summed desc limit 10

Related Solutions

Can You Use a WHERE Clause with Greater Than Operator on a Composite Key in MySQL?

You can use

SELECT *
FROM `bbcmapimp_pc_raw`
WHERE (`pc_area`,`suffix`) > ('FO', 'BAR')
ORDER BY `pc_area`,`suffix`

Though actually this seems to have a better explain plan

SELECT *
FROM `bbcmapimp_pc_raw`
WHERE `pc_area` >= 'FO' AND (`pc_area` > 'FO' OR `suffix` > 'BAR')
ORDER BY `pc_area`,`suffix`

Mysql int vs varchar as primary key (InnoDB Storage Engine

I follow the following rules for primary keys:

a) Should not have any business meaning - they should be totally independent of the application you are developing, therefore I go for numeric auto generated integers. However if you need additional columns to be unique then create unique indexes to support that

b) Should perform in joins - joining to varchars vs integers is about 2x to 3x slower as the length of the primary key grows, so you want to have your keys as integers. Since all computer systems are binary, I suspect its coz the string is changed to binary then compared with the others which is very slow

c) Use the smallest data type possible - if you expect your table to have very few columns say 52 US states, then use the smallest type possible maybe a CHAR(2) for the 2 digit code, but I would still go for a tinyint (128) for the column vs a big int which can go up to 2billion

Also you will have a challenge with cascading your changes from the primary keys to the other tables if for example the project name changes (which is not uncommon)

Go for sequential auto incrementing integers for your primary keys and gain the inbuilt efficiencies that database systems provide with support for changes in the future

Best Answer

Related Solutions

Can You Use a WHERE Clause with Greater Than Operator on a Composite Key in MySQL?

Mysql int vs varchar as primary key (InnoDB Storage Engine

Related Question