Removing Join and Restriction clause in MySQL

Hi All,
How can I remove restriction clause from this query to make it run faster?  Thanks

Select tbl_search.JetID as LinkjetID, tbl_student.cordID as LinkcordID, tbl_student.JETId
 as 'JET Id', tbl_search.Point_Id as 'Point Id', ProDate as 'Pro Date',
Contractname as 'Contract', CardName as 'CardName', CardNumber
as 'CardNumber', CardName2 as 'CardName2', coopNumber as 'coopNumber', coopGroup as 'coopGroup',
LGCode as 'LGCode', Contract as 'Contract', POP as 'POP', Pocket as 'Pocket',
(case when (ifnull(tbl_record.Valor_Name,'') = 'e_valor') then 'e-delivery only' else
DATE(tbl_record.email_Date) end) as 'DateMailed', fitness as
 'fitness', tbl_record.Client_SLot as 'Client_SLot', tbl_student.Price_range as
 'Price_range', tbl_student.Price_after as 'Price_AFTER', tbl_student.hall as 'Hall'
from (tbl_student) left join tbl_record on tbl_record.Point_Id = tbl_student.Point_Id
 WHERE (CardNumber like '%55556666%') ORDER BY tbl_student.ProDate DESC LIMIT 0, 100
RotebergAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gheistCommented:
Drill it through explain (let us have a look at what explain says)

Generic query without joins might perform better than manual joins (and hint where you use joins wrong)
select .. form ..,..,.. where ..=... and ...
0
PortletPaulfreelancerCommented:
What is a "restriction clause"? ( do you mean the WHERE clause perhaps? )

One of the problems you have given us to solve is "which table does [cardnumber] come from?" because you have not used a table or alias prefix on that column - so as we have never seen your tables it is purely a guess. With that in mind I'd guess that you do not need a LEFT JOIN to tbl_record but you have also left out tbl_search from the query too.

SELECT
        /* join to this table is missing *
       tbl_search.JetID AS LinkjetID
     , tbl_search.Point_Id AS 'Point Id'

     , tbl_student.cordID AS LinkcordID
     , tbl_student.JETId AS 'JET Id'
     , tbl_student.Price_range AS 'Price_range'
     , tbl_student.Price_after AS 'Price_AFTER'
     , tbl_student.hall AS 'Hall'

      /* pure guess these come from the records table */
     , R.ProDate AS 'Pro Date'
     , R.Contractname AS 'Contract'
     , R.CardName AS 'CardName'
     , R.CardNumber AS 'CardNumber'
     , R.CardName2 AS 'CardName2'
     , R.coopNumber AS 'coopNumber'
     , R.coopGroup AS 'coopGroup'
     , R.LGCode AS 'LGCode'
     , R.Contract AS 'Contract'
     , R.POP AS 'POP'
     , R.Pocket AS 'Pocket'
     , CASE WHEN ifnull(R.Valor_Name, '') = 'e_valor' THEN 'e-delivery only' 
             ELSE DATE R.email_Date
       END AS 'DateMailed'
     , R.fitness AS 'fitness'
     , R.Client_SLot AS 'Client_SLot'

FROM tbl_student
INNER JOIN tbl_search ON ??? = ???
INNER JOIN tbl_record AS R ON tbl_record.Point_Id = tbl_student.Point_Id
WHERE R.CardNumber LIKE '%55556666%'
ORDER BY tbl_student.ProDate DESC 
LIMIT 0 , 100

Open in new window


A "double ended" wildcard such as LIKE '%55556666%' is always inefficient.
Can you do it with just a single wildcard? LIKE '55556666%' as this is more efficient.
Assuming you have an index on CardNumber a "begins with" search can make use of an index.
0
RotebergAuthor Commented:
Thank You geist & Paul Maxwell for your contribution I appreciate it, so how can I properly use the join can you guy help me with this?
here is a explain result geist asked for

ID= 1                                        
Select type= Simple
Table= tbl_student
Type= Index
Possible Keys=NULL
Key = IX_ProDate
Key_len=8
Ref=NULL
Rows=100
Extra= Using where
ID= 1                                        
Select type= Simple
Table= tbl_record.Point_Id
Type= eq_ref
Possible Keys=UI_PointId
Key = UI_PointId
Key_len=4
Ref= empire.tbl_student.Point_Id
Rows=1
Extra= Using where
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

gheistCommented:
Please use explain without 'extended', this one shows worst sin in very verbose format, other is more readable.

Particular output says it is not using index on
select .. from tbl_student where ui_pointid = ?? ;
0
RotebergAuthor Commented:
Thanks again gheist below is the query and explain result

Select tbl_student.JetID as LinkjetID, tbl_student.cordID as LinkcordID, tbl_student.JETId
 as 'JET Id', tbl_student.Point_Id as 'Point Id', ProDate as 'Pro Date',
Contractname as 'Contract', CardName as 'CardName', CardNumber
as 'CardNumber', CardName2 as 'CardName2', coopNumber as 'coopNumber', coopGroup as 'coopGroup',
LGCode as 'LGCode', Contract as 'Contract', POP as 'POP', Pocket as 'Pocket',
(case when (ifnull(tbl_record.Valor_Name,'') = 'e_valor') then 'e-delivery only' else
DATE(tbl_record.email_Date) end) as 'DateMailed', fitness as
 'fitness', tbl_record.Client_SLot as 'Client_SLot', tbl_student.Price_range as
 'Price_range', tbl_student.Price_after as 'Price_AFTER', tbl_student.hall as 'Hall'
from (tbl_student) left join tbl_record on tbl_record.Point_Id = tbl_student.Point_Id
 WHERE (CardNumber like '%55556666%') ORDER BY tbl_student.ProDate DESC LIMIT 0, 100

id  select_type      table                        type                  possible_keys      key             key_len      ref                            rows      Extra
1   SIMPLE      tbl_student                index                    (NULL)            IX_ProDate      8      (NULL)                            100      Using where
1   SIMPLE      tbl_record        eq_ref          UI_PointId            UI_PointId      4      empire.tbl_student.Point_Id      1
0
gheistCommented:
now it re-sorts ix_prodate on the fly.
i would look at it a bit skeptically.
make 1000 queries and time them. could happen that current version is good even optimizer disagrees.
0
PortletPaulfreelancerCommented:
Please answer this question....

Which table does cardnumber belong to?

I think you can change to an inner join which is more efficient, beyond that it is a very simple query so optimization potential is low.

In future, please do use table names or aliases on all column references in your queries.
0
RotebergAuthor Commented:
cardnumber belong to tbl_student
0
PortletPaulfreelancerCommented:
Thanks! The left join is valid as is.

Having % on both ends of the string means the query has to scan the table.

FROM tbl_student
      LEFT JOIN tbl_record ON tbl_record.Point_Id = tbl_student.Point_Id
WHERE tbl_student.CardNumber LIKE '%55556666%'
ORDER BY
      tbl_student.ProDate DESC
LIMIT 0, 100

please remember to add the table (or alias if used) as a prefix to all column references in your queries.
0
RotebergAuthor Commented:
Thanks Paul Maxwell I really appreciate it. but my question is why should i use Cardnumber column not Point_id column?
0
gheistCommented:
You are using cardnumber column, not us. Mentioned 10x - %text% causes full table scan
You can index on tail of field, or prefill initial %, but having % in the beginning will scan the field on every query, slow even in memory cache.
0
PortletPaulfreelancerCommented:
????

"How can I remove restriction clause from this query to make it run faster?"

Is the question I was trying to answer.

You are free to use any column (or set of columns)  in the WHERE clause.
You do not HAVE TO use cardnumber at all.
You can use the Point_id .
You could use both.

I cannot tell you why you "should" use any column because it depends on your functional requirements which I know nothing about.

I can simply say that if you are searching by cardnumber that:

best:
      WHERE tbl_student.CardNumber = '55556666'

better:
      WHERE tbl_student.CardNumber LIKE '55556666%'

least desirable:
      WHERE tbl_student.CardNumber LIKE '%55556666%'

assuming there is an index on that column.
0
gheistCommented:
And I was wrong about mysql and substring index. You can index only on leading letters in field.
0
PortletPaulfreelancerCommented:
>>"You can index only on leading letters in field."

yep, that's why LIKE '%55556666%' is a problem
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RotebergAuthor Commented:
Paul Maxwell & gheist thank you so much for your help, I really appreciate it.
0
gheistCommented:
PostgreSQL or any commercial DB will allow you to index on an expression, which includes substrings of column.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.