Write a better SELECT query

fabiuz

Hallo,

is there any way to write in a best way a query like this:

SELECT c.id, SUM(c.click) AS AddClick
FROM clicks_a AS c
WHERE c.date = CURDATE()
GROUP BY c.id
ORDER BY AddClick DESC
LIMIT 10

If I run an EXPLAIN SELECT for it, I get for the Extra item:

where used; Using temporary; Using filesort

This query is often used on my DB so... is it possible to write it better ?

Thank you !

millesm

As you are RESTRICTING, GROUPING and ORDERING, this statement is written as efficiently as it can be.

You could consider an index to improve the RESTRICTION.

fabiuz

I have just added an Index to "date" field and I think to have got a good improvement:

before rows involved in query were: 170873

now rows, with the same query, are: 1233

I think this could be the only field I can index... isn't it ?

Actually, the table have a lot of rows (I mean about 70.000), new rows are added dayily, but I need to get results for current month only, could it be a good idead make a new table where store old results and get a lighter main table where add and query dayily ?

Thanks ;-)

millesm

I'm not that familiar with MySQL, but in Oracle I would partition the table into Months, so that the search was automatically restricted to the latest records.

I am not sure if this is available in MySQL, but if it is not, you could write a job that removes old data and stores in a seperate table, giving you a much smaller data set to search.

fabiuz

I don't know if MySQL have this function, I will check... eventually I should make a cron job I suppose...

Do you think that make a lighter table be a good improvement using more than 70.000 records ?

Actually I am running another query on the same table where I have just added the index to date field, is it something like this:

SELECT DISTINCT a.ida, a.name, Sum(c.click) AS AddClick
FROM autors AS a, clicks_autor AS c
WHERE a.ida = c.ida
AND (TO_DAYS(CURDATE()) - TO_DAYS(c.date)) <= 15
GROUP BY a.ida, a.name
ORDER BY AddClick DESC
LIMIT 10;

this query is wasting a lot of resources, is it good SQL enought in your opinion ?

The strange thing is that it is not using the new index on date field :-? ... so it is processing all rows of the ckicks table :-(

Do you know the reason or the solution ?

...my SQL knowledge is not so good..

Thank you for any help about !

millesm

Smaller data volumes are always better for searching.
If you could create a cron job to copy out old data that would be good.

Regarding your query, you are performing functions on a column with an index. I believe that this will cause your index NOT to be used.

You should check the manual to see if performing functions on indexed columns is good or bad.