From: Reindl Harald Date: October 7 2011 4:20pm Subject: Re: MySQL Indexes List-Archive: http://lists.mysql.com/mysql/225961 Message-Id: <4E8F26E9.1050808@thelounge.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigD07FE3D9D06A50B8505738B5" --------------enigD07FE3D9D06A50B8505738B5 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable but could this not be called a bug? Am 07.10.2011 18:08, schrieb Michael Dykman: > When a query selects on field_a and field_b, that index can be used. I= f > querying on field_a alone, the index again is useful. Query on field_b= > alone however, that first index is of no use to you. >=20 > On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps wrote= : >=20 >> This thread has sparked my interest. What is the difference between an= >> index on (field_a, field_b) and an index on (field_b, field_a)? >> >> >> On 10/06/2011 07:43 PM, Nuno Tavares wrote: >> >>> Neil, whenever you see multiple fields you'd like to index, you shoul= d >>> consider, at least: >>> >>> * The frequency of each query; >>> * The occurrences of the same field in multiple queries; >>> * The cardinality of each field; >>> >>> There is a tool "Index Analyzer" that may give you some hints, and I >>> think it's maatkit that has a tool to run a "query log" to find good >>> candidates - I've seen it somewhere, I believe.... >>> >>> Just remember that idx_a(field_a,field_b) is not the same, and is not= >>> considered for use, the same way as idx_b(field_b,field_a). >>> >>> -NT >>> >>> >>> Em 07-10-2011 00:22, Michael Dykman escreveu: >>> >>>> Only one index at a time can be used per query, so neither strategy = is >>>> optimal. You need at look at the queries you intend to run against = the >>>> system and construct indexes which support them. >>>> >>>> - md >>>> >>>> On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins >>>> **wrote: >>>> >>>> Maybe that was a bad example. If the query was name =3D 'Red' what= index >>>>> should I create ? >>>>> >>>>> Should I create a index of all columns used in each query or have a= >>>>> index >>>>> on individual column ? >>>>> >>>>> >>>>> On 6 Oct 2011, at 17:28, Michael Dykman wrote: >>>>> >>>>> For the first query, the obvious index on score will give you optim= al >>>>> results. >>>>> >>>>> The second query is founded on this phrase: "Like '%Red%' " and no = index >>>>> will help you there. This is an anti-pattern, I am afraid. The on= ly >>>>> way >>>>> your database can satisfy that expression is to test each and every= >>>>> record >>>>> in the that database (the test itself being expensive as infix find= ing >>>>> is >>>>> iterative). Perhaps you should consider this approach instead: >>>>> >>>> natural-language.html >>>>>> >>>>> http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** >>>>> natural-language.html >>>>> >>>>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil<>>>> googlemail.com > >>>>> neil.tompkins@stripped> wrote: >>>>> >>>>> Hi, >>>>>> >>>>>> Can anyone help and offer some advice with regards MySQL indexes. >>>>>> Basically >>>>>> we have a number of different tables all of which have the obvious= ly >>>>>> primary >>>>>> keys. We then have some queries using JOIN statements that run sl= owly >>>>>> than >>>>>> we wanted. How many indexes are recommended per table ? For exam= ple >>>>>> should >>>>>> I have a index on all fields that will be used in a WHERE statemen= t ? >>>>>> Should the indexes be created with multiple fields ? A example = of >>>>>> two >>>>>> basic queries >>>>>> >>>>>> SELECT auto_id, name, score >>>>>> FROM test_table >>>>>> WHERE score> 10 >>>>>> ORDER BY score DESC >>>>>> >>>>>> >>>>>> SELECT auto_id, name, score >>>>>> FROM test_table >>>>>> WHERE score> 10 >>>>>> AND name Like '%Red%' >>>>>> ORDER BY score DESC >>>>>> >>>>>> How many indexes should be created for these two queries ? >>>>>> >>>>>> Thanks, >>>>>> Neil >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> - michael dykman >>>>> -mdykman@**gmail.com >>>>> >>>>> May the Source be with you. >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql?**unsub=3Dmdykman@gmai= l.com >> >> >=20 >=20 --=20 Mit besten Gr=FC=DFen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofm=FChlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm --------------enigD07FE3D9D06A50B8505738B5 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk6PJukACgkQhmBjz394AnnpLACglEd5wqnq48vO9uFppW82acI2 6BIAnjtT+4VD3d5RD1FDFPfQCRBB+bQs =BHR0 -----END PGP SIGNATURE----- --------------enigD07FE3D9D06A50B8505738B5--