pascal_22 Posted June 11, 2012 Share Posted June 11, 2012 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 Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/ Share on other sites More sharing options...
fenway Posted June 16, 2012 Share Posted June 16, 2012 Show us the EXPLAIN. Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1354457 Share on other sites More sharing options...
pascal_22 Posted June 19, 2012 Author Share Posted June 19, 2012 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 Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1355156 Share on other sites More sharing options...
fenway Posted June 30, 2012 Share Posted June 30, 2012 Well, that query isn't very selective -- region probably return many matches. Try an index on Profils.DerniereVisit. Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1358160 Share on other sites More sharing options...
pascal_22 Posted July 5, 2012 Author Share Posted July 5, 2012 Hello Fenway, yeah, i already have an index on Profils.DerniereVisite This query isn't optimal? Thanks! Pascsal Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1359319 Share on other sites More sharing options...
mikosiko Posted July 5, 2012 Share Posted July 5, 2012 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. Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1359442 Share on other sites More sharing options...
pascal_22 Posted July 6, 2012 Author Share Posted July 6, 2012 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 Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1359540 Share on other sites More sharing options...
fenway Posted July 7, 2012 Share Posted July 7, 2012 When's the last time you ran ANALYZE TABLE? Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1359861 Share on other sites More sharing options...
pascal_22 Posted July 11, 2012 Author Share Posted July 11, 2012 never? oups.... should i do? What analyse table will do? Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1360820 Share on other sites More sharing options...
pascal_22 Posted July 11, 2012 Author Share Posted July 11, 2012 Hello, I just do an table analyse and the result was OK analyze status Table is already up to date Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1360821 Share on other sites More sharing options...
pascal_22 Posted July 11, 2012 Author Share Posted July 11, 2012 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 Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1360825 Share on other sites More sharing options...
pascal_22 Posted July 11, 2012 Author Share Posted July 11, 2012 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.... Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1360828 Share on other sites More sharing options...
fenway Posted July 12, 2012 Share Posted July 12, 2012 Try the EXPLAIN for the query withing ordering. Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1360954 Share on other sites More sharing options...
pascal_22 Posted July 16, 2012 Author Share Posted July 16, 2012 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 Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1361956 Share on other sites More sharing options...
fenway Posted July 17, 2012 Share Posted July 17, 2012 And without ordering? Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1362012 Share on other sites More sharing options...
pascal_22 Posted July 17, 2012 Author Share Posted July 17, 2012 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? Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1362187 Share on other sites More sharing options...
mikosiko Posted July 17, 2012 Share Posted July 17, 2012 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 Link to comment https://forums.phpfreaks.com/topic/264011-php-mysql-select-with-multiple-criteria-load-a-lot-of-row-with-limit-12/#findComment-1362235 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.