PHP mySQL: Select with multiple criteria load a lot of row with LIMIT 12

[pascal_22]

Hello,

 

I'm trying to show on my homepage, the last 12 registered user.

So i use LIMIT 12 but when i use "Explain" in mysql.... it says they read over 2000 rows for these LIMT 12

 

I have 5 criteria in my where clause:

I have INDEX on each of them and an index of the combinaison of all.

My Query looks:

SELECT Usagers.*,Profils.*,Province.* from Usagers inner join Profils on Usagers.PkUsager=Profils.FkUsager inner join Province on Profils.FkProvince=Province.PkProvince where Profils.Actif=1 and Profils.FkUsager<>" . $_SESSION["PkUsager"] . " and FkRegion=" . $_SESSION["ipcountry_FkPays"] . " and FkSexe=".$_SESSION["FkSexeRechercher"] . " and ProfilAvecPhoto=1 order by Profils.DerniereVisite desc limit 12"

**NOTE i use Usagers.*,Profils.* and PRovince.* only reduce code. But iin my PHP page, i only chose field that i need.

 

What should i do to make it the fastest as we can?

Because i think it 's not optimized.

Thanks

 

Pascal

 

 

[fenway]

Show us the EXPLAIN.

[pascal_22]

Thank's and sorry for the time of response, i was out!!!

 

Here are the result of EXPLAIN:

 

id 	select_type 	table 	type 	        possible_keys 	                                                                           key 	                            key_len 	ref 	rows 	Extra 
1 	SIMPLE 	        Profils 	index_merge 	FkUsager,Actif,FkSexe,FkRegion,ProfilAvecPhoto,FkUsager_2 	FkRegion,ProfilAvecPhoto 	2,1 	NULL	2062 	Using intersect(FkRegion,ProfilAvecPhoto); Using where; Using filesort
1 	SIMPLE 	Province 	eq_ref 	PRIMARY 	PRIMARY 	8 	bd_celibataireduweb.Profils.FkProvince 	1 	Using where
1 	SIMPLE 	Usagers 	eq_ref 	PRIMARY 	PRIMARY 	8 	bd_celibataireduweb.Profils.FkUsager 	1 	

 

Thanks a lot!!!

 

Pascal

[fenway]

Well, that query isn't very selective -- region probably return many matches.  Try an index on Profils.DerniereVisit.

[pascal_22]

Hello Fenway,

 

yeah, i already have an index on Profils.DerniereVisite

 

 

This query isn't optimal?

 

Thanks!

Pascsal

[mikosiko]

try to run the query wrote in this way

"SELECT Usagers.*,
           Profils.*,
           Province.* 
      FROM Usagers inner join Profils on Usagers.PkUsager=Profils.FkUsager 
                                          AND Profils.FkUsager<>" . $_SESSION["PkUsager"] . " 
                                          AND Profils.Actif=1 
                                          AND ProfilAvecPhoto=1 
                   inner join Province on Profils.FkProvince=Province.PkProvince 
      WHERE FkRegion=" . $_SESSION["ipcountry_FkPays"] . " 
        AND FkSexe=".$_SESSION["FkSexeRechercher"] . " 
      ORDER BY Profils.DerniereVisite DESC 
      LIMIT 12"

 

post the EXPLAIN plan again and also the complete definition of your table Profils including all the indexes.

[pascal_22]

Hello and thanks for the code.

 

I really don't know what i did or what i put in my parameters but both: my code or yours, when i do explain, now i don't see 2000 rows but i see 548 rows both query(yours and mine).... i tried so much thing but an can 't get 2000 row again....

 

here are explain of your code:

+ Options   id      select_type     table    type    possible_keys   Key   key_len   ref   rows  Extra
1  SIMPLE   Profils   index_merge   FkUsager,Actif,FkSexe,FkRegion,ProfilAvecPhoto   FkRegion,ProfilAvecPhoto  2,1  NULL  548  Using intersect(FkRegion,ProfilAvecPhoto); Using where; Using filesort
1  SIMPLE Province eq_ref PRIMARY  PRIMARY 8  Profils.FkProvince 1 Using where 

1 SIMPLE Usagers eq_ref PRIMARYPRIMARY 8 Profils.FkUsager 1 

 

here are my index:

Action	Keyname	Type	Unique	Packed	Column	Cardinality	Collation	Null	Comment
Edit	 Drop	PRIMARY	BTREE	Yes	No	PkProfil	11447	A	No	
Edit	 Drop	FkUsager	BTREE	No	No	FkUsager	11447	A	No	
Edit	 Drop	Actif	BTREE	No	No	Actif	2	A	No	
Edit	 Drop	FkSexe	BTREE	No	No	FkSexe	2	A	No	
FkSexeRecherche	4	A	No
FkRegion	104	A	No
Ville	3815	A	No
age	11447	A	No
FkProvince	11447	A	No
Edit	 Drop	Approbation	BTREE	No	No	Approbation	2	A	No	
Edit	 Drop	FkRegion	BTREE	No	No	FkRegion	43	A	No	
Edit	 Drop	ProfilAvecPhoto	BTREE	No	No	ProfilAvecPhoto	2	A	No	
Edit	 Drop	NomUsagerSans	BTREE	No	No	NomUsagerSans	11447	A	No	
Edit	 Drop	DerniereVisite	BTREE	No	No	DerniereVisite	11447	A	No	

 

Hope this can help!! Thanks for your help!!

Have a great day!!

 

Pascal

[fenway]

When's the last time you ran ANALYZE TABLE?

[pascal_22]

never?

oups.... should i do?

What analyse table will do?

[pascal_22]

Hello, I just do an table analyse and the result was OK

 

analyze status Table is already up to date

[pascal_22]

hello,

here another exemple of big select,

 

I have a table Usagers who contain infos for my members, i have a field "Newsletters" with an INDEX on it and the value can be 0(for no newsletters) and 1(for agree to receive newsletter)

 

when i do a select * from Usagers where Newsletters=1

here are the result:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 
1 	SIMPLE 	Usagers 	ALL 	NewsLetters 	NULL	NULL	NULL	181901 	Using where

so all row where read.... WHY?

Is there a problem with my Mysql... imean, it did that on my development server and my server online

 

thanks for your help

[pascal_22]

i also did with an HINTS to force the INDEX

 

explain select * from Usagers USE INDEX (`NewsLetters`) where Newsletters=1

 

same thing....... all row were read....

[fenway]

Try the EXPLAIN for the query withing ordering.

[pascal_22]

hello!

Here the explain for:

explain select * from Usagers where Newsletters=1 order by PkUsager desc

id  select_type    table     type  possible_keys  key    key_len  ref    rows      Extra  
1    SIMPLE      Usagers    ALL  NewsLetters    NULL    NULL   NULL 181901  Using where; Using filesort

 

filesort?? it's not good i think?  no?

 

Pascal

[fenway]

And without ordering?

[pascal_22]

whitout ordering

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE      Usagers ALL  NewsLetters    NULL NULL     NULL 181901   Using where

if i change Newsletter=1 by Newsletter=0

id  select_type  table  type  possible_keys       key         key_len  ref       rows  Extra
1     SIMPLE    Usagers  ref  NewsLetters    NewsLetters     4      const     35572 

 

Why when i use 0 it works?

[mikosiko]

this 2 links should help you to find the answer for yourself (both include examples almost identical to your case)

 

http://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/

 

http://www.mysqlperformanceblog.com/2006/06/02/indexes-in-mysql/

 

is those don't clarify it for you google: index cardinality, and index selectivity