SEO

Hello,

I wanted to know if there's anyone that could help me with this
problem. My query takes too much time to execute and I don't know how
to make it better.

SELECT SQL_CALC_FOUND_ROWS i.*, DATE_FORMAT(i.date, '%d-%m-%y') as
date, u.login
FROM img i
LEFT JOIN comments c ON c.imgid=i.id
LEFT JOIN users u ON u.id=i.userid
LEFT JOIN flags f ON i.id=f.imgid
WHERE i.deleted=0
AND c.date > f.lastview
AND c.userid!=530
AND f.userid=530
GROUP BY i.id ORDER BY i.id DESC LIMIT 0,12

The problem is that this query needs to examinate more that 1M rows
(by running EXPLAIN). There's 150k rows on table "comments", 4k on
users, 35k on img and 1,8 M on "flags". All table have indexes and PK.

Thanks a lot,


Jean-Baptiste

jean3b@gmail.com wrote:
> I wanted to know if there's anyone that could help me with this
> problem. My query takes too much time to execute and I don't know how
> to make it better.
> All table have indexes and PK.

What indexes and what PKs?
You have shown us the query, but not any details of what you have already
done to optimise it?

On 30 juil, 23:14, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
> jea...@gmail.com wrote:
> > I wanted to know if there's anyone that could help me with this
> > problem. My query takes too much time to execute and I don't know how
> > to make it better.
> > All table have indexes and PK.
>
> What indexes and what PKs?
> You have shown us the query, but not any details of what you have already
> done to optimise it?

The table are designed like this :
CREATE TABLE `comments` (
`id` int(13) NOT NULL auto_increment,
`imgid` int(13) unsigned NOT NULL default '0',
`userid` int(13) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`txt` text NOT NULL,
`edit_uid` int(13) unsigned default NULL,
`edit_date` datetime default NULL,
`deleted` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `imgid` (`imgid`),
KEY `userid` (`userid`),
KEY `date` (`date`),
KEY `deleted` (`deleted`)
) ENGINE=MyISAM AUTO_INCREMENT=162669 DEFAULT CHARSET=latin1;


#
# Table structure for table flags
#

CREATE TABLE `flags` (
`userid` int(13) unsigned NOT NULL default '0',
`imgid` int(13) unsigned NOT NULL default '0',
`lastview` datetime NOT NULL default '0000-00-00 00:00:00',
`views` int(10) unsigned NOT NULL default '1',
`favori` tinyint(1) unsigned NOT NULL default '0',
`suivi` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`userid`,`imgid`,`lastview`),
KEY `favoris` (`imgid`,`favori`),
KEY `suivi` (`suivi`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


#
# Table structure for table img
#

CREATE TABLE `img` (
`NoOrdre` int(13) unsigned NOT NULL default '0',
`id` int(13) unsigned NOT NULL auto_increment,
`url` varchar(250) NOT NULL default '',
`cat` int(3) unsigned NOT NULL default '1',
`hits` int(11) unsigned NOT NULL default '0',
`userid` int(13) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`actif` enum('0','1','2') NOT NULL default '1',
`deleted` tinyint(1) NOT NULL default '0',
`note` float NOT NULL default '-1',
PRIMARY KEY (`id`),
KEY `actif` (`actif`),
KEY `NoOrdre` (`NoOrdre`),
KEY `cat` (`cat`),
KEY `userid` (`userid`),
KEY `deleted_date` (`deleted`,`date`),
KEY `cat_deleted_id` (`cat`,`deleted`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=34945 DEFAULT CHARSET=latin1
PACK_KEYS=0;


#
# Table structure for table users
#

CREATE TABLE `users` (
`id` int(13) unsigned NOT NULL auto_increment,
`email` varchar(100) NOT NULL default '',
`login` varchar(100) NOT NULL default '',
`password` varchar(100) NOT NULL default '',
`dateinscrip` date NOT NULL default '0000-00-00',
`views` int(10) unsigned NOT NULL default '0',
`actif` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `email_index` (`email`),
UNIQUE KEY `login_index` (`login`),
KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3950 DEFAULT CHARSET=latin1;

And explain :
+----+-------------+-------+-------+-----------------------------
+---------+---------+-------------------------+-------
+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------
+---------+---------+-------------------------+-------
+-----------------------------+
| 1 | SIMPLE | i | index | PRIMARY,userid,deleted_date | PRIMARY | 4 |
NULL | 21493 | Using where; Using filesort |
| 1 | SIMPLE | f | ref | PRIMARY,favoris | PRIMARY | 8 |
const,imgalacondev.i.id | 1 | Using index |
| 1 | SIMPLE | u | ref | PRIMARY,id | id | 4 | imgalacondev.i.userid |
1 | |
| 1 | SIMPLE | c | ref | imgid,userid,date | imgid | 4 |
imgalacondev.f.imgid | 6 | Using where |
+----+-------------+-------+-------+-----------------------------
+---------+---------+-------------------------+-------
+-----------------------------+
4 rows in set (0.00 sec)

I create multiple Index and PK to optimize and nothing else. I'm lost
with JOIN optimization..

Thanks,

On 30 Jul, 23:04, Jean-Baptiste ANNE <jea...@gmail.com> wrote:
| I create multiple Index and PK to optimize and nothing else.

I am intrigued by this statement. The indexes that you have built seem
to have nothing to do with the query you posted. What led you to
decide to create those particular indexes?

Also I do not understand why, when id is the primary key for the users
table, you have it built as a non unique index too?????

Now, without knowing some statistics about the actual data, it is
difficult to come up with a perfect optimisation.

But, you could try the folowing:

On your img table, build a unique composite index of deleted,id. Since
this is the "prime" table in the query, this will stop it having to
read all 35k of the records to find which ones are not deleted.

The other thing that could speed it up might be to remove the
SQL_CALC_FOUND_ROWS if that is acceptable. then it would just stop
when the limit had beed reached.

On 1 août, 14:24, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 30 Jul, 23:04, Jean-Baptiste ANNE <jea...@gmail.com> wrote:
> | I create multiple Index and PK to optimize and nothing else.
>
> I am intrigued by this statement. The indexes that you have built seem
> to have nothing to do with the query you posted. What led you to
> decide to create those particular indexes?
Nothing in particular. I have multiples queries and I try to design
the best my table (but I'm a student, it's for my personal use this is
why it's not really as good as possible).
> Also I do not understand why, when id is the primary key for the users
> table, you have it built as a non unique index too?????
It's a mistake...
> Now, without knowing some statistics about the actual data, it is
> difficult to come up with a perfect optimisation.
What stats do you need ?
> But, you could try the folowing:
>
> On your img table, build a unique composite index of deleted,id. Since
> this is the "prime" table in the query, this will stop it having to
> read all 35k of the records to find which ones are not deleted.
I created this idx and I have to examine 26k rows on 'img'. (the same
before).
> The other thing that could speed it up might be to remove the
> SQL_CALC_FOUND_ROWS if that is acceptable. then it would just stop
> when the limit had beed reached.
SQL_CALC_FOUND_ROWS seems to take about 10ms (by benchmarking with a
quick query) which is acceptable and I prefer add this than querying
MySQL again with "SELECT COUNT(*) ....".

Thanks a lot,

On 1 Aug, 15:07, Jean-Baptiste ANNE <jea...@gmail.com> wrote:
>
> What stats do you need ?
Well, it is good to know how many rows from each table may match some
of the tests, so as to ensure that the choices are done in the most
efficient way.
How many rows in img have deleted = 0?
How many rows in there in total?
does deleted hold only 1 or 0?

> But, you could try the folowing:
>
> > On your img table, build a unique composite index of deleted,id. Since
> > this is the "prime" table in the query, this will stop it having to
> > read all 35k of the records to find which ones are not deleted.
>
> I created this idx and I have to examine 26k rows on 'img'. (the same
> before).
The explain you posted showed 21k rows. Where are you seeing the 26k?

> The other thing that could speed it up might be to remove the
> > SQL_CALC_FOUND_ROWS if that is acceptable. then it would just stop
> > when the limit had beed reached.
>
> SQL_CALC_FOUND_ROWS seems to take about 10ms (by benchmarking with a
> quick query)
I don't understand what you mean by "benchmarking with a quick query".
SQL_CALC_FOUND_ROWS must count all the possible results that would be
returned by the query if the LIMIT clause were not there.


which is acceptable and I prefer add this than querying
> MySQL again with "SELECT COUNT(*) ....".
>
> Thanks a lot,

On 1 août, 16:37, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 1 Aug, 15:07, Jean-Baptiste ANNE <jea...@gmail.com> wrote:
>
> > What stats do you need ?
>
> Well, it is good to know how many rows from each table may match some
> of the tests, so as to ensure that the choices are done in the most
> efficient way.
> How many rows in img have deleted = 0?
> How many rows in there in total?
> does deleted hold only 1 or 0?
There is 28098 rows with i.deleted=0 and it holds a userid. (I change
it to int(13)). If it contains a userid the image has been deleted.
> > But, you could try the folowing:
>
> > > On your img table, build a unique composite index of deleted,id. Since
> > > this is the "prime" table in the query, this will stop it having to
> > > read all 35k of the records to find which ones are not deleted.
>
> > I created this idx and I have to examine 26k rows on 'img'. (the same
> > before).
>
> The explain you posted showed 21k rows. Where are you seeing the 26k?
>
> > The other thing that could speed it up might be to remove the
> > > SQL_CALC_FOUND_ROWS if that is acceptable. then it would just stop
> > > when the limit had beed reached.
>
> > SQL_CALC_FOUND_ROWS seems to take about 10ms (by benchmarking with a
> > quick query)
>
> I don't understand what you mean by "benchmarking with a quick query".
> SQL_CALC_FOUND_ROWS must count all the possible results that would be
> returned by the query if the LIMIT clause were not there.
>
I don't understand what I say too. It's kinda stupid... Without
SQL_CALC_FOUND_ROWS the query is really faster but how can I get the
number of rows with a LIMIT ?

Jean-Baptiste ANNE wrote:
> On 1 août, 16:37, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>> On 1 Aug, 15:07, Jean-Baptiste ANNE <jea...@gmail.com> wrote:
>>
>>> What stats do you need ?
>>
>> Well, it is good to know how many rows from each table may match some
>> of the tests, so as to ensure that the choices are done in the most
>> efficient way.
>> How many rows in img have deleted = 0?
>> How many rows in there in total?
>> does deleted hold only 1 or 0?
> There is 28098 rows with i.deleted=0 and it holds a userid. (I change
> it to int(13)). If it contains a userid the image has been deleted.
>>> But, you could try the folowing:
>>
>>>> On your img table, build a unique composite index of deleted,id.
>>>> Since this is the "prime" table in the query, this will stop it
>>>> having to read all 35k of the records to find which ones are not
>>>> deleted.
>>
>>> I created this idx and I have to examine 26k rows on 'img'. (the
>>> same before).
>>
>> The explain you posted showed 21k rows. Where are you seeing the 26k?
>>
>>> The other thing that could speed it up might be to remove the
>>>> SQL_CALC_FOUND_ROWS if that is acceptable. then it would just stop
>>>> when the limit had beed reached.
>>
>>> SQL_CALC_FOUND_ROWS seems to take about 10ms (by benchmarking with a
>>> quick query)
>>
>> I don't understand what you mean by "benchmarking with a quick
>> query". SQL_CALC_FOUND_ROWS must count all the possible results that
>> would be returned by the query if the LIMIT clause were not there.
>>
> I don't understand what I say too. It's kinda stupid... Without
> SQL_CALC_FOUND_ROWS the query is really faster but how can I get the
> number of rows with a LIMIT ?
One question I forgot to ask you was what FOUND_ROWS() returned as the total
number of rows.