Wildcard usage in mysql

Posted on 2007-08-09
Last Modified: 2013-11-05
I'm finding some really strange results when I do searches on my mysql database (360k records).
For instance, if I search for JOHN%% limit 0,100 -> it returns quickly.
But if I search for JOHN%Y -> It takes forever.

Can someone tell me why some of my searches are dog slow, while others are quick?  I'm generating indexes for each column I am searching against, hoping that will help.

Running on: 4.1.22-log on Mac OSX Tiger Server
Question by:ecuguru
    LVL 48

    Assisted Solution

    Its because if you use JOHN%Y a whole table scan is performed, as no index can be used (string must start wirh JOHN and END with Y). If you search FOR JOHN% a index can be used, as you are only looking for a string starting with JOHN.
    LVL 17

    Accepted Solution

    You could try using the way below to keep using the indexes and execute the query you want :)
    ... yourColumn LIKE 'JOHN%'  AND yourColumn LIKE '%Y' ...

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now