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 Quote 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. Quote 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 Quote 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. Quote 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 Quote 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. Quote 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 Quote 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? Quote 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? Quote 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 Quote 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 Quote 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.... Quote 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. Quote 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 Quote 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? Quote 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? Quote 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 Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.