On Mon, 12 Oct 2009 13:47:19 -0500 Conor <conor.edward.da...@gmail.com> wrote:
> > Christian Démolis wrote: > > Hi, > > > > The idea of creating another column is good but it will multiplicate > > the size of my table by 2 for nothing. Is it possible to use MYSQL > > regular expression search with sql alcmehy? If yes, what is the > > command? > > > MySQL supports RLIKE/REGEXP operators; you can see how to use them in > SQLAlchemy in this thread: > http://groups.google.com/group/sqlalchemy/browse_thread/thread/7c6abe2ab9d5061 > > Be aware that regexp matching cannot utilize indexes, so you may have > performance problems. > > If you want to get fancy, you can try creating an index on the > expression REPLACE(REPLACE(<tel_column>, ' ', ''), '.', '') > which should create an index on the normalized phone numbers. In > theory, as long as your queries use the exact same function sequence > as the index, the index will be scanned instead of the full table. I > don't know what limitations MySQL has in this regard. Even if it does > work, the only real advantage it provides over using another column > is the normalized form is "hidden" in the index instead of the table. > > Hope it helps, > -Conor > Indeed this problem is easily solved with expression indexes (a.k.a. "functional" or "calculated" indexes). Unfortunately MySQL does not appear to support them. Their suggested workaround is to add a column to store the precomputed expression, with a trigger to keep it up to date, and index that column. This also means the optimization is not transparent: you have to explicitly use the precomputed column in your query. It seems expression indexes have been MySQL's todo list since at least 2007 :( You can of course still use RLIKE/REGEXP instead of adding this redundant column, but you'll get a full table scan every time. A classic space/time performance tradeoff :) -Kyle > > 2009/10/10 Andre Stechert <stech...@gmail.com > > <mailto:stech...@gmail.com>> > > > > > > This is not really a sqlalchemy question, but the quick answer > > is that you need to convert both your indexed data and your queries > > to the same normal form. In your example, you appear to be > > correctly stripping spaces and periods in your query. If you > > haven't done that in the database, then you should do it there, > > too. If you need to preserve the original formatting of the > > telephone number column, then create another column that contains > > the stripped phone numbers. You probably also want to put an index > > on that column. > > > > Lastly, a minor note on the sample code: you appear to be > > missing a "%" operator in your LIKE query. > > > > Cheers, > > Andre > > > > On Fri, Oct 9, 2009 at 5:46 AM, Christian Démolis > > <christiandemo...@gmail.com <mailto:christiandemo...@gmail.com>> > > wrote: > > > Hi everybody, > > > > > > I m stuck with a query about telephone number : I want to find in > > > my database all the contact who have a telephone number. The > > > difficulty is that some number in the database can have space or . > > > between numbers > > > example : 06.06.50.44.11 or 45 87 12 45 65 > > > > > > This my query with like but it s not what i want because i ignore > > > telephone number who have special chars > > > > > > NumeroApparu = "0645444444" > > > s = session.query(Contact.IdContact, Contact.Civilite, > > > Contact.Nom, Contact.Prenom, > > > > > > ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier).filter(ContactTel.Tel.like(NumeroApparu)) > > > > > > i saw class sqlalchemy.sql.expression.ColumnOperators¶ in the doc > > > but lack of explication... > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
