+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Mysql hanging (/showthread.php?tid=27796)
Mysql hanging - El Forum - 02-20-2010
[eluser]megabyte[/eluser]
I have a search query using a join.
Code:
SELECT * FROM work_orders
LEFT JOIN
customers using (Cid)
WHERE
customers.Name LIKE 'f51%' OR
customers.Address LIKE 'f51%' OR
customers.Tel LIKE 'f51%' OR
work_orders.Site_Address LIKE 'f51%' or
customers.Name LIKE '%Wiltshire%' OR
customers.Address LIKE '%Wiltshire%' OR
customers.Tel LIKE '%Wiltshire%' or
work_orders.Site_Address LIKE '%Wiltshire%'
Group By customers.Cid
SELECT * FROM customers
LEFT JOIN
work_orders using (Cid)
WHERE
customers.Name LIKE 'f51%' OR
customers.Address LIKE 'f51%' OR
customers.Tel LIKE 'f51%' OR
work_orders.Site_Address LIKE 'f51%' or
customers.Name LIKE '%Wiltshire%' OR
customers.Address LIKE '%Wiltshire%' OR
customers.Tel LIKE '%Wiltshire%' or
work_orders.Site_Address LIKE '%Wiltshire%'
Group By customers.CidAlmost identical queries, just the join changes, however the first one executes in about 2 seconds and the second hangs forever and never finishes. I'm using phpmyadmin to test these queries.
Does anyone have a clue why this would happen?
Both tables have about 6,000 rows of data.
Mysql hanging - El Forum - 02-20-2010
[eluser]Kepler[/eluser]
You may want to try creating an index on work_orders.Cid
If that doesn't help, try grouping by work_orders.Cid in the second query.