SQL optimization:

bogu

Ok, I got a problem and all of your ideas are real appreciated.

I have a table something like this:

CREATE TABLE `table` (
  `table_id` int(11) NOT NULL auto_increment,
  `join_id` int(11) NOT NULL default '0',
  `data` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`table_id`),
  KEY `date` (`data`)
) TYPE=MyISAM;

This table got lots of rows, something like 500.000 rows…
Mysql server vers. 4.1.10

When I’m executing this query:

SELECT DISTINCT(UNIX_TIMESTAMP(table.data)) as data, COUNT(table.table_id) as nr, COUNT(DISTINCT(table.join_id)) as nr1 FROM `table` WHERE table.join_id!=0 GROUP BY data ORDER BY data DESC LIMIT 0,10

, its take 9 sec to execute.

Does anyone have some idea of sql optimization?

Thanks for reading this, and any idea is real appreciated.

TF_TheMoon

Do you need the UNIX_TIMESTAMP in your query? Also remove the DISTINCT as you're doing a GROUP BY on the same field.

You could also create an index on the join_id which will help to speed it up

Sxooter

And what's with the rather odd use of NOT NULL DEFAULT '0000-00-00 00:00:00'???

Why bother being all correct defining the column as not null, then setting the default to 0000-00-00 00:00:00, an illegal time that only MySQL will allow for input. Considering that MySQL inserts the default when you tell it to insert NULL (another bit of insanity we'll discuss on some other date) there's no use setting a not null with a default in it.

And if you ever turn on the -ansi switch your inserts will start failing if they try to insert the default, cause then MySQL won't accept 0000-00-00 00:00:00

the zero timestamp is one of those things they never should have done, and it really should be avoided.

Oh, and what, exactly, are you trying to do? Often it's better to tell us that so we can offer a new query that's a faster performer, or spot design flaws in your schema that are holding you back.

bogu

TF@TheMoon wrote:
Do you need the UNIX_TIMESTAMP in your query?

Yes I need it, but is this function slowing my query?

TF@TheMoon wrote:
Also remove the DISTINCT as you're doing a GROUP BY on the same field.

I remove it, thanks for the tip.

TF@TheMoon wrote:
You could also create an index on the join_id which will help to speed it up

I already done this.

Sxooter wrote:
And what's with the rather odd use of NOT NULL DEFAULT '0000-00-00 00:00:00'?

This is the default settings when I create the table in phpmyadmin. 🙂 I changed the data field to null.

Sxooter wrote:
Oh, and what, exactly, are you trying to do?

As I was saying I have a table that have lots of rows (something like 500.000) and the select I explained above is very slow, can I make some optimization in the select query so I could speed up my script?
I have an ideea but is not exactly what I want, I could gather the result using a cron.

Sxooter wrote:
Often it's better to tell us that so we can offer a new query that's a faster performer

Ok, let me explain what I’m trying to do.
I have a table where I keep the site traffic. So I want to get the last 10 day’s with the number of hits and the number of unique hits, that’s all, but while that table grow so much, the select become very slow. I’m thinking if I could find a solution for this problem without using a cronjob to gather all this reports.

BTW, thank you both for your answers.